0

I am trying to find a way to properly import a text file into excel that contains a carriage return using the Get External Data option.

The file has a text qualifier and is pipe delimited. Here is some example data.

"1"|"test data" 
"2"|"test data 
with a line break"

It should import 2 rows with a line break in row 2 column 2. However, it ends importing it as 3 rows.

I tried playing with the File origin settings to see if the different encodings made a difference but they do not.

Improve this question
2
  • 1
    It's a shame this got migrated from stackoverflow, because I think the only answer to this is a VBA program.
    – Jean-François Corbett
    Commented Jul 11, 2011 at 20:29
  • Just because the only answer is programming in VB, doesn't mean that its a programming problem. The question was asked in such a way that a non-programming solution would be more acceptable if one exists.
    – Justin Dearing
    Commented Jul 11, 2011 at 21:11

2 Answers 2

Reset to default
1

Have you tried Ron's Editor? I used to 'try' and use Excel to edit my CSV files and now I only use this application.

Once opened correctly the file can be export to Excel with everything formatted as you intended.

To be clear I am close the the development of Ron's Editor having contributed a lot of ideas and testing to it, so I am not entirely unbiased. However I honestly feel it is a great alternative to Excel for CSV editing.

Improve this answer
0

You can give XLReport a try. We offer a free trial - www.xlrepprt.com. It can easily import real time data from almost any data source, and of course text files into Excel.

Improve this answer

You must log in to answer this question.