I'm trying to import a CSV file into Excel and this is the basic step I do:
- 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:
- 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.)
- 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!