0

When I have a line break like this (created by my program, or as in this example by Excel itself):

enter image description here

the Excel import wizard misinterprets it like this:

enter image description here

How can I avoid this failure?

The advice in Importing CSV with line breaks in Excel 2007 did not help.

Workarounds by post-import editing are unacceptable.

EDIT: Dragging the .TXT into Excel gives a different failure:

enter image description here

EDIT: Untrue. Expanding the row reveals success:

enter image description here

EDIT: EDIT: Replacing Tab with comma does not give success from File|Open or drag.

ANSWER summary: The given representation works - provided the .TXT file import is done by dragging not File|Open.

Community
  • 1
  • 1
ChrisJJ
  • 2,191
  • 1
  • 25
  • 38

1 Answers1

1

I created your file locally (thanks for hex dump!) and when I Import or File->Open the file I get the same results. However, if I just drag the file into Excel I actually get the expected line breaks. I then created a similar file directly in Excel and saved it as a tab delimited file and repeated the same process with the same results. So the fact that Excel won't correctly import a file that it creates leads me to believe that this isn't possible.

The next thing that I tried was replacing your tab with a comma and this actually worked for the File->Open scenario but not for the Import.

My guess is that Excel assumes that if you are "importing" text then it assumes it isn't in a correct and known format already. So I would suggest just sticking to CSV best practices.

Chris Haas
  • 53,986
  • 12
  • 141
  • 274
  • "if I just drag the file into Excel I actually get the expected line breaks". Here not. "replacing your tab with a comma and this actually worked for the File->Open scenario but not for the Import." Here neither worked. Thanks for the thought, but perhaps yours is a different version of Excel? – ChrisJJ May 01 '13 at 22:11
  • In both scenarios above, if I expand the row I see the full data. Excel doesn't vertically auto-expand during text import. Although I am running Excel 2010 I wouldn't imagine that that part of the engine would change too much. – Chris Haas May 02 '13 at 13:06
  • Here, expanding the row works for the drag case. Thanks for the correction, and answer. Here, expanding the row does not work for the File|Open case, so I guess Excel 2010 is indeed different. However, if you post a screenshot of our success with File|Open of comma-seperated .TXT, it would be useful for the future. Thanks. – ChrisJJ May 02 '13 at 18:57