Reputation: 2342

CSV decimal dot in Excel

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.

Upvotes: 48

Answers (8)

MarkusEgle

Reputation: 3065

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

My prefered solution

File - Options - Data

Then you can use the old import wizard... legacy but in my opinion more intuitiv.

Other possibilities

The linked website of Professor Excel also shows other possibilities. With Excel's new import dialog, if you have multiple columns of numbers that are all in a different locale than your computer, then the import is much more time-consuming. With the old wizard you are done within a minute. With the new import dialog, I have not yet found a method that is as fast as the old import method.

Upvotes: 49

Gaurav Malik

Reputation: 11

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.

Upvotes: 0

Vlad-Emil Petrea

Reputation: 11

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:

open the .csv file by double clicking it in your file explorer
select the column(s) containing decimal numbers
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:

edit the file in Notepad++
open the Encoding menu tab
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!

Upvotes: 1

rnahumaf

Reputation: 501

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"

Upvotes: 26

smoothumut

Reputation: 3491

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.

Upvotes: 15

Jose Enrique Pons Frias

Reputation: 176

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.

Upvotes: 3