Thread: 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

Good morning Brelin

Does my response to this post help?
http://www.excelforum.com/excel-programming/652722-exporting-to-a-flat-txt-file.html

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

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

Hi Brelin

Yes. Try changing this line :

Write #1, ExpData;

to this :

Write #1, ExpData

and marvel at the difference a semicolon can make ...

HTH

DominicB

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

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?

If IsEmpty(Selection.Cells(NR, NC)) Then ExpData = ""

HTH

DominicB

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?

If IsEmpty(Selection.Cells(NR, NC)) Then ExpData = ""

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

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.

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

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 :

Sub test()
Open "d:\work\testfile.txt" For Output As #1
For NR = 1 To Selection.Rows.Count
For NC = 1 To Selection.Columns.Count
ExpData = Selection.Cells(NR, NC).Value
If IsNumeric(ExpData) Then ExpData = Val(ExpData)
If IsEmpty(Selection.Cells(NR, NC)) Then ExpData = ""
If NC <> NumCols Then
If Not ExpData = "Skip" Then Print #1, ExpData
End If
Next NC
Next NR
Close #1
End Sub

Let me know how you go with that.

HTH

DominicB

Absolutely spot on mate it works a treat !

Thanks for all the help you've provided

Brelin

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.