Saving Excel to CSV does not retain number formating

Question

This is a query related to Excel in Office 365 (64bit)

I have an excel spreadsheet with text fields and number fields.

I have converted the number fields to numeric with 2 decimal  places

I then save this file as a .csv

When I import the .csv file again using the Data Import function, the numeric columns all appear as text columns. When you convert them to numeric with decimal, the "numbers" with decimal places show as an error, and only the numbers with no decimal places are preserved as numbers. The sequence is shown in the screen shots below. Somehow Excel is not recognising the decimal point of the numeric fields and is converting these numbers to text

Answer 1

Do your system settings specify comma as decimal separator?

Answer 2

I'm working with Excel 2016, so things look slightly different.

try using the "From TEXT/CSV".  That should prompt the "text import wizard". There you can confirm the comma separator, and you can define data types for the columns.

I did notice on my test data, when I formatted the text including comma separators (I did NOT enter that comma, it was placed by formatting when I saved the file), that value alone was treated as text during the import.

Answer 3

The system settings specify . as decimal separator and not comma

Answer 4

Many thanks. I will try this