"." and "," formate confusion using CSV and EU/US integer decimal number

[Eddy]

Hi everybody. I cant solve the following Problem.   

Ojective: I want to read file name and GPS coordinates from all JPG pictures in a directory to a single XL file. I need the GPS Coordinates in decimal with a "," separator (EU format). I am using Win7.
I use this command:
  exiftool -csv -filename -GPSLatitude -n -GPSLongitude -n . > Pictures_GPS_Data_Output.csv

The output is a CSV file with all pictures listed in lines like this:
  ./TestBild2.JPG,TestBild2.JPG,50.9285180555556,14.2449213888889
  ./TestBild3.JPG,TestBild3.JPG,50.9176113888889,14.0565663888889

Now the problem is, if I open it in XL and set the column separator to "," XL converts the lines to
./TestBild2.JPG   TestBild2.JPG   509.285.180.555.556   142.449.213.888.889
./TestBild3.JPG   TestBild3.JPG   509.176.113.888.889   140.565.663.888.889

Because the integer separator of the numbers is "." in the US but here in the EU its "," the GPS coordinates are wrong.
Changing all "." into "," before opening in XL, screws up the the file names which are also separated with "." (e.g. Picture.jpg)

Cann anybody help me?  Thanks a lot.   

[Phil Harvey]

I read your post 3 times and am still confused.

1.  By "XL", do you mean Microsoft Excel?

2. Why does XL read "509.285.180.555.556" when the input is "50.9285180555556"?  This seems insane to me.  Maybe check your XL local settings.

This sounds like more of an XL problem than an ExifTool problem to me.  Perhaps asking in an XL forum makes more sense?

- Phil

[Alan Clifford]

2. Why does XL read "509.285.180.555.556" when the input is "50.9285180555556"?  This seems insane to me.  Maybe check your XL local settings.

- Phil

That does make sense if the dot is used as a thousands separator.  The spreadsheet (XL?) is just reformatting the number in the cell.

[Eddy]

Hi Phil,
Thanks for your help.

1. By "XL", do you mean Microsoft Excel?
YES

2. Why does XL read "509.285.180.555.556" when the input is "50.9285180555556"?

Because an European fraction numbers are shown as 12,456 and not like 12.456 (US).
An European Software (also Excel) reads the number 12.456 (US) as 12456 (EU).
The otional dots in the EU numbers every 3 digits are only there because of better readability, like the optional "," in the US numbers.

I checked the Excel forum for this problem. The problem is, that you can't just change "." with ";" because the file names would be read as "Picture,JPG" instead "of Picture.JPG".

The only solution I see is, to give the decimal GPS coordinates or any faction number output with an colon instead of a dot with an option in ExifTool.

Best regards,
Eddy

[Phil Harvey]

Surely you must be able to change your locale settings in Excel to use a "." as a decimal separator.

An alternative would be

exiftool -p "$filename,""${gpslaitude#;tr/./,/}"",""${gpslongitude#;tr/./,/}""" .

... and hope that no file names contain a comma.

(I think that's the proper way to escape quotes in the Windows cmd shell.)

- Phil

[Alan Clifford]

Unfortunately, I don't have excel but I've been trying with open office.

When I open the csv file, it asks me for the language.  Selecting German results in the the numbers being imported as text. 

So I select English.  This imports the numbers as numbers and formats the cells with 12.345 format.  I can them reformat the cells as German and they become formatted in 12,345 format.

[StarGeek]

I don't have Excel so I can't double check, but this page seems to show a way to change the comma/decimal problem.