0

I'm working with climatic data from a bunch of counties around the country. The data is originally on the .csv format, and when I open it, I get a different result than my co-workers. Apparently my Excel isn't recognizing properly where the decimal separator should be put and is making an odd conversion (he transforms 123,456789 (<-hundreds) into 123,456,789 (<- millions). Like I said before, my co-workers didn't have this problem, which leads me to think it's not a file related, but software-configuration related. I tried changing the format of the cell, the decimal separators (under excel options) and the tabulation options without success. I'll attach two links: the first shows the data as it should be displayed and the other one is how i'm seeing it.

How I should be seeing it
How I should be seeing it

How excel is importing my file:
How excel is importing my file

nixda
  • 27,268
Eric Lino
  • 111
  • Are your system settings set to recognise the comma for thousands? – Solar Mike Sep 04 '17 at 19:40
  • Check your system settings under Region and language » Formats » Advanced and compare them to your co-worker – nixda Sep 04 '17 at 19:44
  • Excel also has this setting set somewhere, which you can use to override the windows settings. – LPChip Sep 04 '17 at 19:59
  • Export the data as xlsx file not as CSV, then it will not be sensitive to locale on input, or specify the correct settings while you import it (it is too late afterwards) – eckes Sep 04 '17 at 20:43
  • @SolarMike No, it is supposeadly set to recognize comma for decimals, which makes it even more weird. – Eric Lino Sep 06 '17 at 20:57
  • @nixda They're set the same. – Eric Lino Sep 06 '17 at 21:00
  • @LPChip I'm aware of that, It's on the same place nixda specified. Doesn't solve the problem, just changes commas to dots. – Eric Lino Sep 06 '17 at 21:00
  • @eckes I don't have the .xls format. The database automatically provides data as .csv files. – Eric Lino Sep 06 '17 at 21:01
  • @EricLino no. nixda speaks about system settings, which is for your entire windows. Somewhere burried in the options in Excel, there's another place where you can set this. – LPChip Sep 06 '17 at 21:13
  • You set the settings for the format while clicking on the actual colum in the import dialog. If you doubleclick the file or use text to column it works differently. – eckes Sep 07 '17 at 00:10
  • @EricLino In your excel under Excel Options » Advanced » Use system separators: What is it set? (path to this option can vary between Excel versions) – nixda Sep 07 '17 at 00:57
  • 1
    Somewhere, you have set the comma as the thousands separator, or you have a conflict between the thousands and decimal separators. It could be in Excel Options Advanced Editing; it could be in the text import wizard stage 3 Advanced; it could be in your Windows Regional Settings (where it is called the digit grouping symbol) for Numbers, or the WRS for Currency. They ALL should agree. – Ron Rosenfeld Sep 09 '17 at 01:00

0 Answers0