tags:

views:

6190

answers:

4

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

A: 

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.

Alterlife
Thanks, but I need the CRs to make it into the cell...
Maximilian Tyrtania
Oops, only now I understand what you meant. Anyway, csv files work for me...
Maximilian Tyrtania
+1  A: 

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.

Jason Lepack
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
Really? Hmm... back to the drawing board. I will find something for you.
Jason Lepack
I just looked at some AppleScript... I want to not have to do that any time soon.
Jason Lepack
+2  A: 

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

Maximilian Tyrtania
Glad you found a solution!
Jason Lepack
A: 

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

Michel de Ruiter