Why does Excel parse numbers with more than two decimal places as whole numbers?

0
votes

Excel uses Windows Regional Settings to get the List Separator and Decimal Separator for csv files. I am attempting to localize some csv reports in our application for French and German users. I am using semi-colons as the csv delimiter and commas as decimal separators for the French and German versions of each csv.

I've set my local Windows Regional settings to use semi colons and commas as decimal separators. When I open the following test file in Excel, Excel parses numbers with 2 or less decimal characters correctly ... based on my Regional settings. However, numbers with 3 or more decimal places are parsed as whole numbers. So, the string 12,3000 will be parsed as 123 000 (One hundred twenty three thousand).

test.csv:

"Decimal Separator";"In Quotes";"Number"
"Period";"false";4.283333
"Period";"true";"4.283333"
"Period";"false";0.283333
"Period";"true";"0.283333"
"Comma";"false";4,283333
"Comma";"true";"4,283333"
"Comma";"false";0,283333
"Comma";"true";"0,283333"
"Period";"false";4.333
"Period";"true";"4.333"
"Period";"false";0.333
"Period";"true";"0.333"
"Comma";"false";4,333
"Comma";"true";"4,333"
"Comma";"false";0,333
"Comma";"true";"0,333"
"Period";"false";4.28
"Period";"true";"4.28"
"Period";"false";0.28
"Period";"true";"0.28"
"Comma";"false";4,28
"Comma";"true";"4,28"
"Comma";"false";0,28
"Comma";"true";"0,28"
"Period";"false";4.4
"Period";"true";"4.4"
"Period";"false";0.4
"Period";"true";"0.4"
"Comma";"false";4,4
"Comma";"true";"4,4"
"Comma";"false";0,4
"Comma";"true";"0,4"
"Period";"false";4
"Period";"true";"4"
"Period";"false";0
"Period";"true";"0"
"Comma";"false";4
"Comma";"true";"4"
"Comma";"false";0
"Comma";"true";"0"
"Period";"false";45623455454.283333
"Period";"true";"45623455454.283333"
"Period";"false";45623455450.283333
"Period";"true";"45623455450.283333"
"Comma";"false";45623455454,283333
"Comma";"true";"45623455454,283333"
"Comma";"false";45623455450,283333
"Comma";"true";"45623455450,283333"
"Period";"false";45623455454.28
"Period";"true";"45623455454.28"
"Period";"false";45623455450.28
"Period";"true";"45623455450.28"
"Comma";"false";45623455454,28
"Comma";"true";"45623455454,28"
"Comma";"false";45623455450,28
"Comma";"true";"45623455450,28"

Does anyone have any insight on this

2
excelcsvlocalizationdecimal
Have you looked into the additional regional options provided with the Workbooks.OpenText method? These can be explicitly specified without regard to the individual machine's regional settings. – user4039065
I need an answer that works for end Users without asking them to jump through hoops. – Jason Williams
fwiw, I would consider asking users to modify their regional settings in order to import a CSV along the lines of 'jumping through hoops'. I was suggesting that you take the time to write a proper import routine that did not rely on ' the individual machine's regional settings'-. – user4039065

2 Answers

1
votes

You might have this issue if your Regional settings are set to use a comma as the "Digit grouping symbol". French uses a space character for Digit Grouping and Germany uses a period.

Windows --> Control Panel --> Region and Language --> Numbers --> Additional Settings --> Digit Grouping Symbol --> Set to use a space character

1
votes

I'm thinking that you have the digit grouping symbol also as a comma. That's usually represented by a period (or a space) in Europe, so three thousand and twenty-two hundredths would be represented 3.000,22.

I changed the decimal character to a comma, and the digit grouping character to a period, and imported 12,3000, and Excel interpreted it as twelve and three tenths, as it should.

When I changed both to comma, the same import gave me one hundred twenty-three thousand, as you saw.