Large text file

[toby131]

I have a text file of individual records with a similar format to what is
shown below. I want to import this into excel with one record per row and
each line of the record in a seperate column. I have a few thousand entries
so am trying to find an automated method. Is there a way to do this?


Record 1
Column 1 Header: xxxxxxxx
Column 2 Header: xxxxx
Column 3 Header: xxxxxxx
Column 4 Header: xxxxxx


Record 2
Column 1 Header: xxxxxxxx
Column 2 Header: xxxxx
Column 3 Header: xxxxxxx
Column 4 Header: xxxxxx

 

[PhilosophersSage]

You cannot do this in excel; however, you can make the *.txt file a *.CSV in
word

1) First replace all multiple paragraph marks between records with a text
string that does not exist in your document. (i.e. replace "^p^p^p" with
"zxc")
2) Replace headings with comma. (i.e. replace "^p Column 1 Header" with ","
and the same for rest of columns)
File will look like R1,C1,C2,C3,C4zxcR2,C1,C2,C3,C4 and so on...
3) Replace text string with paragraph (i.e. replace "zxc" with "^p")
4) Import into excel as a CSV file.

 

[toby131]

I do not have an option to save the file as a *.csv, is there another program
I can use to save in that format or another format I can save in?

 

[Steve Yandl]

Toby131,

I'd write a VBA routine and utilize the "Scripting.FileSystemObject". It
would help to see a few dozen lines of the actual text files and to know if
the format is retained through the whole body of text (4 fields for each
record, seperation by two blank lines etc.)

Steve Yandl

 

[PhilosophersSage]

You can save as a *.txt and just import as *.csv (I have not tried) it is the
formatting that matters. If that does not work just rename *.txt to *.CSV