Import pipe-delimited text data that contains multi-line fields

Hi,

I have a pipe-delimited text file that has multi-line fields.

test.txt
I need to import this file to MS Access or Excel (this was exported out from Oracle).
The desired results are :

results.xlsx
However, there's a line space ('cr lf') after every line, and when I import it to Excel, they get separated to different lines even though they should all be in one cell if they are between pipes.
How can I import this to Excel as 3 column, multi-line?

Thank you.
iamnamjaAsked:
Who is Participating?
 
Jim P.Connect With a Mentor Commented:
I have to agree with Pat -- the data is a mess.

Unless there is some kind of indicator of what the start of each individual record is trying to build a good import routine will be very hard.

If there is a consistent number of rows for each one you could possibly pull it into a staging table with just an autonumber and a data column. Then go through and pull row "1" into a variable, then row 2,3, and 4 into another variable, 5,6, and 7 into the next and then write to another table. Rinse and repeat as needed.

You may also consider if you want the column B and C to look like that or break it out to more columns.
0
 
PatHartmanConnect With a Mentor Commented:
You would have to write code.  No standard import can handle such a mess.

I don't see any of identifying where one "record" stops and another record begins unless there is always a blank line between records.  Your code would have to read each record and determine whether it was part of the previous column or should start a new column or a new record.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.