CSV decimal dot in Excel

Question

I have a CSV file with the following values:

3271.96;274;272;1;1;0;1;0.071690;0;0;0;0;0;0;1.753130;1.75;0;1.75;

But when I open the file with Excel I get this:

3271.96 274 272 1   1   0   1   0.071690    0   0   0   0   0   0   1.753.130   1.75    0   1.75

Why is "1.753130" converted into "1.753.130"? (1.753130 is a decimal number) how can I "force" Excel to understand that these are decimal numbers?

I create the CSV file with a web application, so is difficult just modify my Excel configuration because many people visit my website and download the CSV file to their machines.

Answer 1

For users seeking to this question with newer Excel versions like Excel 365... As written at Professor Excel you could activate/restore "From Text (Legacy)" in the settings.

My prefered solution

File - Options - Data

Then you will be able to get the old import wizard... legacy but in my opinion more intuitiv.

Other possibilities

At that linked Professor Excel website there are also shown other possibilities. With Excels new import dialog, if you have several columns with numbers all in a different locale to your computers locale settings, then it will be much more effort to do the import. With the old wizard you are set within a minute. With the new import dialog I haven't found yet a method to be as fast as with the legacy import method.

Answer 2

As of now (Sep, 2020), I managed to do this in a slightly different way. I'm using Excel from a Office 365 subscription.

With your Excel sheet open, go to:

• Data (tab) > From Text/CSV (Get & Transform Data section)

Select your file (.txt or .csv), then you'll have 3 options:

• File Origin: probably you won't have to change this
• Delimiter: choose whatever your delimiter is (probably comma)
• Data Type Detection: change this to "Do not detect data types"

Answer 3

here is the answer I used:

• go to Data tab on excel sheet.

• click on from Text button.

• then select text or csv file.

• then the import wizard will come out. select comma separated or space separated option.

• then select delimiter. (this is better if you don't want it to have problem while importing decimals)

• then in the next window there will be Advanced option for General column type. Click the advanced button and choose how to separate decimals and thousands.

Change the decimal separator to a "." and remove the thousand separator with a space.

Answer 4

1. rename the csv to .txt
2. open excel
3. go to file-->open and point to your txt file
4. go through the steps of importing it
5. make sure to use ; as the delimitter

Answer 5

I had the same problem but solely this solution didn't work out for me.

Before that I had to go to Office icon -> Excel Options -> Advanced and set the thousand delimitter from "." to "" (nothing).

Answer 6

There is a more straight forward method to import data from text/csv into Excel (2017):

• Open a blank book in Excel and click in import data from text/csv.
• Select the file.
• The assistant will show a preview of the data, but if you are importing from a csv with decimal / scientific numbers all will be recognized as text.
• Before importing, click on edit, you will see an Excel spreadsheet with a preview of your data.
• If you click on the advanced editor button, a new window with the query Excel does will appear.

You will see something like:

let
    Origin = Csv.Document(File.Contents("C:\Users\JoseEnriqueP\Downloads\evaluation_output.txt"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Updated type" = Table.TransformColumnTypes(Origin,{{"Column1", Int64.Type}, {"Column2", type text}, {"Column3", type text}, {"Column4", type text}, {"Column5", type text}, {"Column6", type text}})
in
    #"Updated type"

Then, you can write down directly the types for each column: - Text: type text - Integers: Int64.Type - Decimals: Double.Type

The import code would be as follows:

let
    Origin = Csv.Document(File.Contents("C:\Users\JoseEnriqueP\Downloads\evaluation_output.txt"),[Delimiter=",", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None]),
    #"Updated type" = Table.TransformColumnTypes(Origin,{{"Column1", Int64.Type}, {"Column2", Int64.Type}, {"Column3", Int64.Type}, {"Column4", type text}, {"Column5", Double.Type}, {"Column6", Double.Type}})
in
    #"Updated type"

By doing this, you will get directly your data into Excel.

Answer 7

If you have a newer version of Excel(e.g. Office 365) and you don't need to correct the file's encoding, here is what worked for me:

1. open the .csv file by double clicking it in your file explorer
2. select the column(s) containing decimal numbers
3. use Find and Replace to change all dots (.) to a comma (,) sign

This assumes that no other data transformations are needed(which would likely require going through the import wizard), and that the file's encoding is correctly recognized by Excel.

If encoding is also an issue, do the following before the steps above:

1. edit the file in Notepad++
2. open the Encoding menu tab
3. choose a desired value to convert the file's encoding

Some of the other answers work also, but for sheer simplicity, you can't beat the Find and Replace method. No matter what you do, here is the most important step: Live long and prosper!

Answer 8

Something that worked for me in 2012 version of Excel is that when you import data, you have the option to open a 'Transform Data' box. In this box on the right side panel, you can see a list of 'Applied Steps'. These are the steps which excel applies on the source file. You can remove the steps from this list which are causing problems. I had a problem with excel ignoring the decimal point while importing from my text file but this resolved the issue.