Excel Save as Unicode Text (with line breaks) and then trying to open the Unicode Text file in Excel

Copper Contributor

I don't understand how the Text Import Wizard handle line feeds within a column. Trying to create files that are accepted by Excel but it seems a bit harder than expected.

 

A simple test that, in my mind, should work but doesn't.

 

  1. Open Excel and create a blank workbook.
  2. Add the text 'HeaderAA' in A1 and 'HeaderBB' in B1.
  3. Add the text 'Hello' in A2.
  4. Add the text 'Row 1', press Alt+Enter to add a line feed within the cell and then add the text 'Row 2' as a second row in B2.
  5. Select File\Save As and change Save as type to 'Unicode Text (*.txt)' .
  6. Close Excel.
  7. Open Excel and select Open.
  8. Change file type to 'All files' or 'Text files'.
  9. In Text Import Wizard
  10.  - Original data type set to 'Delimited',
  11.  - File origin set to 'Windows (ANSI)' or '65001: Unicode (UTF-8)' or '1252: Western European (Windows)' (tested a few more with the same result).
  12.  - Delimiters set to 'Tab'.
  13.  - Leaving Text qualifier as is (")
  14. Press Finish
  15. The result is always wrong with the text 'Row 2"' in A3 instead of B2 with the text 'Row 1'

BUT if I instead (in the file explorer) right click on the text file, selects 'Open with' and selects Excel, the file is opened in Excel AND the text 'Row 2' is shown as it should in B2!!

 

Excel can't even import a file created in Excel....why is it like this? 

What format do Excel require in the Text Import Wizard?

 

 

Using Excel version 2005 build 12827.20470 (Microsoft 365 for Enterprise).

 

4 Replies

@G_u_s_t_a_v The Image you have attached is perfect

 

Actually when you save the excel file to .txt format and you open that txt file in notepad there you will see that the data of one cell has been splitted to different rows so whenever you try to import the same file in excel it will always import the file into different rows.

 

 

Secondly if you have a text file to be imported to excel with a specific format then please let me know I ca help you with that.

@DevendraJain 

That is not entierly true. When you save the text file where there are line feeds in a cell, Excel will (normally) add the text qualifier ("). In a cell the line break also differ slightly, only LF is used. A 'real' row break is both CR+LF. You can see this in the picture that is attached to the thread where you have the screendumps from Excel before export, after import, and from Notepad++ (to see the characters you don't see in Notepad and which file encoding that is used).

@G_u_s_t_a_v Sorry for misleading Information above 

 

I dint know about this 

 

But I would surely love to learn from you how to see the screendumps

@G_u_s_t_a_v 

 

Yes, that's a bit confusing and it happens exactly as you described.

 

When I tried to import the same text file through Power Query, the file origin was set to 1200:Unicode by default and this option is not available when you open the text file with text import wizard and interestingly when imported through Power Query the data is loaded as it was originally there i.e. A2 contains Hello and B2 contains Row1 Row2 when loaded onto the Sheet, though shows Row1Row2 in a single line, separates in individual lines as soon as Wrap Text is enabled.