import text file containing line breaks into excel

Question

I have a plain text file looking like this:

"some  
 text  
 containing  
 line  
 breaks"

I'm trying to talk excel 2004 (Mac, v.11.5) into opening this file correctly. I'd expect to see only one cell (A1) containing all of the above (without the quotes)...

But alas, I can't make it happen, because Excel seems to insist on using the CR's as row delimiters, even if I set the text qualifier to double quote. I was sort of hoping that Excel would understand that those line breaks are part of the value - they are embedded in double quotes which should qualify them as part of the value. So my Excel sheet has 5 rows, which is not what I want.

I also tried this Applescript to no avail:

tell application "Microsoft Excel"
    activate
    open text file filename ¬
    "Users:maximiliantyrtania:Desktop:linebreaks" data type delimited ¬
    text qualifier text qualifier double quote ¬
    field info {{1, text format}} ¬
    origin Macintosh with tab
end tell

If I could tell Excel to use a row delimiter other than CR (or LF), well, I'd be a happy camper, but excel seems to allow the change of the field delimiter only, not the row delimiter.

Any pointers?

Thanks,

Max Excel's open

score 5 · Accepted Answer · edited Mar 28 '12 at 07:30

5

Looks like I just found the solution myself. I need to save the initial file as ".csv". Excel honors the line breaks properly with CSV files. Opening those via applescript works as well.

Thanks again to those who responded.

Max

edited Mar 28 '12 at 07:30

Jean-François Corbett

37,420
30
139
188

answered Dec 04 '08 at 13:31

Maximilian Tyrtania

437
1
4
14

I know this is a necro-post but there is potentially an issue with this that is as far as I can tell a fundamental shortcoming of Excel: opening a csv file in this way will cause cells that contain only digits but begin with a 0 to truncate the leading 0. The only way to have excel respect the text as-written (as far as I'm aware) is to do a Data->From File import, which does not allow line breaks. – Darren Ringer Nov 12 '14 at 21:47

Michel de Ruiter · Answer 2 · 2010-06-03T10:01:47.203

1

If your file has columns separated by list separators (comma's, but semicolons for some non-English region settings), rename it to .csv and open it in Excel. If your file has columns separated by TABs, rename it to .tab and open it in Excel. Importing (instead of opening) a csv or tab file does not seem to understand line feeds in between text delimiters. :-(

edited Jun 03 '10 at 10:01

answered Jun 03 '10 at 09:56

Michel de Ruiter

7,131
5
49
74

score 1 · Answer 3 · answered Dec 04 '08 at 12:45

1

The other option is to create a macro to handle the opening. Open the file for input, and then read the text into the worksheet, parsing as you need, using a Range object.

answered Dec 04 '08 at 12:45

LeppyR64

5,251
2
30
35

Thanks, good idea actually, but I need it to work with Excel 2008 Mac as well, and that doesn't support vba anymore... Max – Maximilian Tyrtania Dec 04 '08 at 12:59
Really? Hmm... back to the drawing board. I will find something for you. – LeppyR64 Dec 04 '08 at 14:51
I just looked at some AppleScript... I want to not have to do that any time soon. – LeppyR64 Dec 04 '08 at 14:57

score 0 · Answer 4 · answered Dec 04 '08 at 12:34

Is it just one file? If so, don\'t import it. Just copy paste the content of your text file into the first cell (hit f2, then paste).

If you absolutely must script this, Excel actually uses only one of those two chars (cr, lf) as the row delimiter, but I'm not sure which. Try first stripping out the lf's with an external util (leave the cr's) and then import it... if that does't work, strip out the cr's (leave the lf's) and thenimport it.

import text file containing line breaks into excel

4 Answers4

Linked