Large text file
 

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

Large text file
 

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" wrote:

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

Large text file
 

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?

"PhilosophersSage" wrote:

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" wrote:

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

Large text file
 

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



"toby131" wrote in message
...
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?

"PhilosophersSage" wrote:

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" wrote:

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

Large text file
 

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

"toby131" wrote:

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?

"PhilosophersSage" wrote:

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" wrote:

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