Best way to delimit text file

[ern]

What is the best structure for delimiting text (.txt) files in
preparation for importation to an Excel workbook ?

An example might be:

Groups of Trees that have a heigth and width

Tree1;20;50;Tree2;30;47.8;Tree3.........

Would this be an acceptable way or is there another standard ?

 

[Bruce001]

If you can import it into Excel, you can save it with a
FileFormat:=xlText. My code looks like this:

FDATE = Format(Now, "YYYYMMDD")
fName = Worksheets("Nav").Range("A8") + FDATE + ".txt"
UF = "T:\Estimator\" + fName

Worksheets("Inputs").Activate
ActiveSheet.Select
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=UF, FileFormat:=xlText


Then when I open it with Excel as a text file, it puts everything in
the proper rows and columns.

I'm sure some of the more experienced users can come up with something
better.

 

[Guest]

The semicolon is fine but can you create your text file with groups of
related data on individual lines? That would work great.

Tree1;20;50
Tree2;30;47.8
Tree3;etc.

 

[ern]

If you can import it into Excel, you can save it with a
FileFormat:=xlText. My code looks like this:

FDATE = Format(Now, "YYYYMMDD")
fName = Worksheets("Nav").Range("A8") + FDATE + ".txt"
UF = "T:\Estimator\" + fName

Worksheets("Inputs").Activate
ActiveSheet.Select
ActiveSheet.Copy
ActiveWorkbook.SaveAs Filename:=UF, FileFormat:=xlText

I'm new to this, so I don't know what this code is or what it means.
Could someone maybe point me to a good "getting started" point for this
topic ? Much appreciated !!!

 

[AnExpertNovice]

I have a simple standard. Tab Delimited with a Header row. (the header row
solves issues when importing into Access.)

I deviate from this only when the user insists upon another format and only
when they convince me that I can't convince them.

To manually create a tab delimited file:
File | Save As
Then select "Text (Tab delimited) (*.txt)" as the "Files of type" option.

 

[ern]

I have a simple standard. Tab Delimited with a Header row. (the header row
solves issues when importing into Access.)

I deviate from this only when the user insists upon another format and only
when they convince me that I can't convince them.

To manually create a tab delimited file:
File | Save As
Then select "Text (Tab delimited) (*.txt)" as the "Files of type" option.

Awesome. Thanks. Is it too much to ask to show a few lines of what a
tab-delimited text file would look like ? I will be writing to this
text file from a program, so I won't be able to manually create the
file as you suggested. I could however insert a '\t' between Excel
entries in the text file. thanks again.

 

[Harald Staff]

option.

Awesome. Thanks. Is it too much to ask to show a few lines of what a
tab-delimited text file would look like ? I will be writing to this
text file from a program, so I won't be able to manually create the
file as you suggested.

I can't watch this any longer. Yes, it's far too much to ask. Chew your own
food. Do as described once, open the file in notepad or similar and have a
long good look at what a tab-delimited text file would look like.

Best wishes Harald

 

[ern]

I can't watch this any longer. Yes, it's far too much to ask. Chew your own
food. Do as described once, open the file in notepad or similar and have a
long good look at what a tab-delimited text file would look like.

Best wishes Harald

thank you very much harald : P you
would be proud of me i chewed my
own bagel this morning

 

[AnExpertNovice]

I would try an experiment so you can see for yourself benefits and draw
backs.

Create an Excel workbook and copy the following text into the stated cells.
I would not copy the "A1:" portion of each line.

A1: Col 1
A2: This isn't a lot of fun, but it needs to be done.
A3: 1
A4: 4

B1: Col 2
B2: 2
B3: Plus, a famous quote says; "Seeing is Believing"
B4: 4

You end up with 2 columns and 4 rows with a header. You also have a mix of
text and numbers.

Now, do a File | Save As and save to the following files and the following
formats.
File ............ Type
Tab.txt....... Text (Tab delimited)
Csv.csv...... CSV (Comma delimited)
Csv.txt...... CSV (Comma delimited) (alternatively rename the csv.csv file
to csv.txt)
and any other formats you want to try.

Open each with notepad to see what they look like. With the CSV. notice all
the extra double quotes used. This doesn't look pretty if you now want to
print the file from the text file. Without the extra quotes the file would
not be imported into Excel, Access, etc. properly.

Open each with Excel. The .csv tells Excel what to use as a delimiter and
so it works nicely. However, try opening csv.txt with Excel and it doesn't
even give you a chance to parse the data. You get the extra quotes. Now,
try to parse it manually. Good luck!

What I have found in my limited experience is that Tab Separated is a more
universally accepted method of creating delimited files. Albeit by a very
slim margin. One application only accepts Tab Separated. (Which I think is
a weakness of the application, per a TIMTOWTDI ideology.)

 

[Tom Ogilvy]

However, try opening csv.txt with Excel and it doesn't

even give you a chance to parse the data.

There should be no difference in the way csv.txt and tab.txt are handled.
Both should take you through the Text Import Wizard. I have never
experienced what you are describing.

If you do get eveything in one column, then you can use Data=>Text to
Columns