+ Reply to Thread
Results 1 to 12 of 12

Exporting one column to text file

  1. #1
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38

    Exporting one column to text file

    Hello everyone.

    I've created a spreadsheet to help me create a text file which I can use to inport values into another device. Using CONCATENATE etc I end up with a range of cells (1 column / Many rows) that I can then MANUALLY highlight, choose copy, open the text file, paste in the contents and then save it. This works fine but as you can imagine I'd like to automate the proceedure by having excel create and save the text file for me.

    From what I've seen on these forums most people are wanting CSV type files but not me. I've already got the cell contents pieced together it just needs writing to a text file, no delimiters, no quote marks, just exactly what's in the one cell on one line, it's just one column remember (H2:H1456).

    I'm not bothered about appending data either, a hard coded overwrite of the text file is fine by me.

    The actual file type I ultimately need is an .abk file, but like I say this is just a basic text file that's been renamed. Could Excel create this or must it have the .txt file extension?

    If anyone could help with the code I would be most grateful as most of my VBA is learn from re-engineering macros, but I can't seem to do this one myself.

    Many thanks

    Brelin

  2. #2
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Good morning Brelin

    Does my response to this post help?
    http://www.excelforum.com/showthread.php?t=652722

    Quote Originally Posted by Brelin
    Could Excel create this or must it have the .txt file extension?
    If you use this method I've shown you can use any extension you please (or even omit one entirely!).

    HTH

    DominicB
    Please familiarise yourself with the rules before posting. You can find them here.

  3. #3
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38
    Thanks for the responce.

    Yes, I'm getting a text file out but it does not use a new line in the text file for each row, can it be changed to do a new line per row?

    Thanks

    Brelin

  4. #4
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Hi Brelin

    Yes. Try changing this line :
    Please Login or Register  to view this content.
    to this :
    Please Login or Register  to view this content.
    and marvel at the difference a semicolon can make ...

    HTH

    DominicB

  5. #5
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38
    That's working spot on now many thanks !

    Out of curiosity I'd like to ask one more question if I may. Some of the rows within the source spreadsheet column do not contain any data and of course are still treated as new lines in the text file. Is it possible to have it skip writing a line to file if it contains a particular character or word? I'm thinking that if I tweak my forumulas so that the word "Skip" or something appears in cells then that row should be skipped. The thing is not all blank lines can be missed hence my suggestion of using a 'skip' word. Don't get me wrong the other device can pick up the data no matter how many blank lines it's got in it but cosmetically speaking the text file is easier to review with unrequired lines removed.

    Thanks again Dom.

    Brelin

  6. #6
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Hi Brelin

    What words do you want to skip? If you remove this line then zeros will replace the blank lines. Is this acceptable or must they be skipped completely?
    Please Login or Register  to view this content.
    HTH

    DominicB

  7. #7
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38
    Quote Originally Posted by dominicb
    Hi Brelin

    What words do you want to skip? If you remove this line then zeros will replace the blank lines. Is this acceptable or must they be skipped completely?
    Please Login or Register  to view this content.
    HTH

    DominicB
    That line if fine from what I can tell, if the cell is blank it puts nothing ( "" ) in the text file ...this is fine for the blank lines that I want (they help visually separating out groups of data). The 'skip' word I meant was that if the contents of the cell read "Skip" I would sort this myself within the formulas)then it would not write that line to the text file it would simply move onto the next line and repeat. Does that explain it better?

    Thanks

    Brelin

  8. #8
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38
    I've pasted a copy of the current exported file here hopefully that will give you a better idea of what I'm after.

    --------------------------------------------------------------------

    # Canon AddressBook version: 1
    # CharSet: WCP1252
    # dn: fixed

    dn: 1
    cn: Sophia Kite
    cnread: Sophia Kite
    cnshort: Sophia K
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    dn: 2
    cn: Abby Morton
    cnread: Abby Morton
    cnshort: Abby M
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    dn: 3
    cn: Claire Wilson
    cnread: Claire Wilson
    cnshort: Claire W
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email













































    dn: 10
    cn: Reception Sheffield
    cnread: Reception Sheffield
    cnshort: Reception
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email











    dn: 13
    cn: Steve Siddall
    cnread: Steve Siddall
    cnshort: Steve S

    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    --------------------------------------------------------------------

    The large gap in the spacing is what I'm trying to remove without removing the single blank lines between each group of info are they are good visual separator aids. You see in my spreadsheet some rows are populated and others aren't.

  9. #9
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38
    Here is what the source spreadsheet would look like:

    --------------------------------------------------------------------

    # Canon AddressBook version: 1
    # CharSet: WCP1252
    # dn: fixed

    dn: 1
    cn: Sophia Kite
    cnread: Sophia Kite
    cnshort: Sophia K
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    dn: 2
    cn: Abby Morton
    cnread: Abby Morton
    cnshort: Abby M
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    dn: 3
    cn: Claire Wilson
    cnread: Claire Wilson
    cnshort: Claire W
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)

    dn: 10
    cn: Reception Sheffield
    cnread: Reception Sheffield
    cnshort: Reception
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)
    SKIP (Export skips this line)

    dn: 13
    cn: Steve Siddall
    cnread: Steve Siddall
    cnshort: Steve S
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    --------------------------------------------------------------------

    The resultant text file would then read something like this:

    --------------------------------------------------------------------

    # Canon AddressBook version: 1
    # CharSet: WCP1252
    # dn: fixed

    dn: 1
    cn: Sophia Kite
    cnread: Sophia Kite
    cnshort: Sophia K
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    dn: 2
    cn: Abby Morton
    cnread: Abby Morton
    cnshort: Abby M
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    dn: 3
    cn: Claire Wilson
    cnread: Claire Wilson
    cnshort: Claire W
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email


    dn: 10
    cn: Reception Sheffield
    cnread: Reception Sheffield
    cnshort: Reception
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email


    dn: 13
    cn: Steve Siddall
    cnread: Steve Siddall
    cnshort: Steve S
    accesscode: 0
    protocol: smtp
    objectclass: top
    objectclass: extensibleobject
    objectclass: email

    --------------------------------------------------------------------

    Hopefully that helps you get my meaning.

    Thanks

    Brelin

  10. #10
    Forum Moderator dominicb's Avatar
    Join Date
    01-25-2005
    Location
    Lancashire, England
    MS-Off Ver
    MS Office 2000, 2003, 2007 & 2016 365
    Posts
    4,867

    Smile

    Hi Brelin

    OK, think I've got that. Try this reworking here - it will only export cells that don't say "Skip". I've been able to shorten it a bit as well, because you want a carriage return after every cell, there are a couple of lines that have become redundant, so we might as well remove them :
    Please Login or Register  to view this content.
    Let me know how you go with that.

    HTH

    DominicB

  11. #11
    Registered User
    Join Date
    01-31-2008
    MS-Off Ver
    2010
    Posts
    38
    Absolutely spot on mate it works a treat !

    Thanks for all the help you've provided

    Brelin

  12. #12
    Registered User
    Join Date
    08-21-2008
    Location
    Canada
    Posts
    3
    Alternative way to clean data would:
    - save worksheet as csv file
    – download TextMaster from www.textmaster.ca
    – run SQL like queries (delete and / or update) using any field or group of fields in where clause to remove unwanted lines.
    TextMaster support is very good and they can help you with details.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1