Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a column in Excel where values are inserted as decimals, and decimals are presented by the (,) separator. Whenever I am trying to sum the values of the column, I am getting the wrong answer because decimal values are not stored with a (.) separator. I need to know how I can sum the decimal values of multiple rows by using the (,) separator.

Exapmle

23,15

23,15

Sum = 46.30 expected 


What I have tried:

SELECT Data.P, Data.co,Data.comp,SUM(Switch(Data.Sens='D',1, Data.Sens='C', -1, TRUE, 1)* Data.Montant) AS Montant FROM data.txt 
Posted
Updated 4-Dec-22 7:02am

See VBA Macros - Region and Language[^] for how to modify locale settings.
 
Share this answer
 
I'd suggest to use schema.ini file. See: Schema.ini File (Text File Driver) - Open Database Connectivity (ODBC) | Microsoft Learn[^]

There's several options you can set. For example:
[data.txt]
DecimalSymbol=.
Format=Delimited(;)
ColNameHeader=False
MaxScanRows=0
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900