Excel data import from text/CSV is incorrectly truncating columns

Question

Bug report - Excel version Office 365 ProPlus.

Hi Excel Team.  I'm experiencing issues with Data Import From Text/CSV where the import Wizard is incorrectly truncating columns and not importing all the data. To replicate: 

1. In Excel select the "Data" menu from the ribbon 
2. Click "From Text/CSV" 
3. Browse and select the file to import (use a file with say 20 items per row, separated by your chosen delimiter.  This should import as 20 columns)
4. Select File Origin: "1252: Western European (Windows)" 
5. Choose a delimiter relevant to your data and click Load
6. You'll notice that columns are truncated and not all data is imported 

EXAMPLE 1 - Using backslash as a custom delimiter, when I import a file and select the backslash character "" as a custom delimiter, the imported data is incorrectly truncated to 6 columns.

EXAMPLE 2 - Using Tab as a delimiter, when I import a file and select "Tab" as the delimiter, the situation is improved, but this time the imported data is incorrectly truncated at 14 columns 

In both cases, columns are truncated and data is lost. Please investigate and resolve this bug.  (Text import used to work ok in older versions of Excel and is still invoked when selecting Data > Text to Columns).  Use Data > Text to Columns as a current workaround until the Import wizard is fixed.

Answer 1

Hi VeryHandyAndy,

Thanks for your detailed information about the issue.

I do a test in my environment and it seems that the issue may go away if you select Comma and Tab when using the feature. I suggest you try it to see the result.

 

Best Regards,

Cliff

Answer 2

Hi Cliff, thanks for the response but that looks like the classic text import wizard from earlier versions of Excel which worked fine.  Here's what I see:

Answer 3

Hi VeryHandyAndy,

Thanks for your updates.

To make the columns be imported successfully, you may need to replace the slash “\” with “,” and save the text file as a .csv file as a workaround. When importing it , select Comma.

 

Meanwhile, I also suggest you click on File>Feedback>Send a Frown to report the issue in From Text/CSV to our related team so that they will be aware of the issue for further investigation.

Best Regards,

Cliff

Answer 4

Hi Cliff

Thanks again for your response.  File format and delimiter change don't resolve the underlying issue.

My test file should import across 18 columns.  Your suggestion to replace '' delimiter with commas (and save the file as a .csv) improved the situation to truncate at 14 columns (being identical to the situation in "Example 2" as I originally reported).  

So the situation appears to be:

 - Custom delimiters truncate at 6 columns

 - Pre-defined delimiters (comma, tab, space etc) truncate at 14 columns

I've taken your advise and logged the issue with the Excel team via Excel "Feedback" > Frown.

Regards

Andy