Jul 03 2020 08:58 AM
Jul 03 2020 08:58 AM
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.
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).
Jul 03 2020 09:23 AM
@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.
Jul 03 2020 09:41 AM
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).
Jul 03 2020 09:45 AM
@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
Jul 03 2020 12:03 PM
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.