CSV decimal dot in Excel + Tableau Public - Stack Overflow

admin管理员组

文章数量:1417711

I'm trying to import a CSV file into Excel and this is the basic step I do:

1. Data --> From Text/CSV

The Data Type Detection is set to "Based on first 200 rows" and the delimeter is the "comma". All the columns are loaded automatically perfectly except for a few and this is where the problem arises.

I'm working with a dataset and there are two columns (longitude and latitude) that must absolutely be recognized as numeric (even better decimal) otherwise when I import the file into Tableau Pubic I encounter problems since it loads geographical graphs only if the data is numeric/decimal

I checked the original CSV file and everything is written correctly. The latitude and longitude columns are written as follows:

,41.89544677734375,12.491181373596191,

But in the file pre-loading screen, the values ​​of these two columns are as follows:

• 4189544677734375
• 12491181373596191

(Note that the "." is not recognized as a delimiter.)

What I need is for these decimal numbers to be written in full, with the "." as delimiter and which have "decimal number" as data type.

From this point on I did a general search and found many possible solutions which I tried, but in vain. Below I will list all the things I have done, but which still haven't solved the problem:

1. I clicked on "Transform Data", selected the latitude column and checked its data type: automatically set as "whole number". So I tried changing it to "decimal number", but the values ​​remain the same.

(I noticed that if I set it to "text" the values ​​inside the columns are correct, but I repeat: they are not correct when I upload the file to Tableau Public because the latitude and longitude values ​​must necessarily be recognized as numbers, otherwise the program does not load coordinates well. The same thing happens if I set the Data Type Detection to "Do not detect data type" instead of "Based on first 200 rows": all columns are set as "text" and this is not good.)

2. I found an interesting article by Professor Excel (link here) and tried to do both things he said:

• Restore the “Text Import Wizard” (old way to import data)
• Import text and CSV files with the “Get & Transform” tools (Using locale)

Regarding the first point, I also went to the "Advanced Text Import Settings" and set the "decimal separator" as "." and "thousands separator" with a "," but nothing has changed. (It should be noted that I had also already tried to change them by going to Excel's advanced settings)

As regards the second point, however, I tried to change the "data type" of the column using "Using locale". On "data type" I set "decimal number" and as for "Locale" I tried practically all the countries, but always in vain: sometimes the numbers transformed well, but instead of the "." they had the ",". For example: instead of obtaining the data like this "41.89544677734375" it is written like this "41,89544677734375". This is the link (here) to all the "Region formats" I used: I tried them all, but I still can't get the result I want.

I really need a hand since I'm working on a very important project and I've already been "wasting time" for 5 days on this stupid issue: I've learned a lot of things in every way about how to import files, but at a certain point I would like to find a solution and I really hope that you can give me a hand.

Thank you so much for your help, really appreciated!

本文标签： CSV decimal dot in ExcelTableau PublicStack Overflow