Take the 2-minute tour ×

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.

share|improve this question

migrated from stackoverflow.com Jul 11 '11 at 19:47

This question came from our site for professional and enthusiast programmers.

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 Jul 11 '11 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 Jul 11 '11 at 21:11
add comment

2 Answers

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.

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer

