Import of CSV unable to swap comma for decimal for a number in import

Question

I am using Office 365 ProPlus - I am running into an issue with the new functionality for data import.  I have a .csv file that was originally created by someone using the German version of Excel.  This means that a decimal point (US/English system) is actually a comma.  In early versions of the import tool I was able to say "comma" is a decimal and "decimal" is a comma on the import.  This is no longer an option, the import tool automatically interprets the value as this example - What I want is 41.2345, or "forty-one point  2345" where the period is a decimal.  When I import the data, it is now 412345, which is the "incorrect" value for my data set.  I can't find any way to modify this interpretation in the latest version of Excel.

Answer 1

Hi Nicole,

If you only want to swap comma for decimal for a number in import, you can simply go to File > Options, uncheck Use system separators check box then you can swap comma for decimal.

However, if you want to go back to the legacy Text Import Wizard, you can follow the steps below.

1. Go to File > Options. Check From Text (Legacy) check box in Data tab then click OK.

2. Go back to the workbook, under Data tab, click Get Data dropdown menu, hover the mouse on Legacy Wizard and choose From Text (Legacy).

Then you can follow the steps in the reply above.

Regards,

Tisky

Answer 2

Hi Nicole,

I am using Office 365 ProPlus - I am running into an issue with the new functionality for data import.  I have a .csv file that was originally created by someone using the German version of Excel.  This means that a decimal point (US/English system) is actually a comma.  In early versions of the import tool I was able to say "comma" is a decimal and "decimal" is a comma on the import.  This is no longer an option, the import tool automatically interprets the value as this example - What I want is 41.2345, or "forty-one point  2345" where the period is a decimal.  When I import the data, it is now 412345, which is the "incorrect" value for my data set.  I can't find any way to modify this interpretation in the latest version of Excel.  

Change the extension of the file from csv to txt: an import wizard will guide the loading of the file and, in step 3, will offer the ability to define the decimal separator:

===

Regards,

Norman

Answer 3

Hi Nicole,

You can follow Norman’s suggestion to swap comma for decimal.

1. Create a new Excel workbook.
2. Click From Text under Data tab.

3. Choose the text file you have. Then Excel will launch a Text Import Wizard. In Step 3, like Norman said, click Advanced, then you can define the decimal separator.

Regards,

Tisky

Answer 4

Hi Tisky -

This was what was available in the previous version.  When I Go to "From Text/CSV", navigate to my file and open it, this is what I get.  The Value column below should have decimal point after the first 2 digits of the number, as it is in the source.  

If I click Edit - I see no option to doing this translation.  

Even though the column type says decimal, when I click OK to load, the number contains no decimal.  

For reference - what I ended up having to do was run the CSV through our ETL tool and export it into a new Excel file as I couldn't figure out anywhere how to do this in the Edit screen.

Answer 5

Hi Nicole,

Have you tried the steps above?

Regards,

Tisky