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

Answer 1

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.

Answer 2

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.

Answer 3

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

Thanks again to those who responded.

Max

Answer 4

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. :-(