• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1201
  • Last Modified:

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.
0
iamnamja
Asked:
iamnamja
2 Solutions
 
PatHartmanCommented:
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
 
Jim P.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

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now