Microsoft XLODBC query- unable to add values for multiple columns in excel due to (, ) separator used for decimal numbers

Question

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 29-Nov-22 3:06am

Member 9956700

Updated 4-Dec-22 7:02am

Add a Solution

2 solutions

Add a Solution

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

Maciej Los · Answer 1 · 2022-12-04T07:02:00

Solution 2

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

Posted 4-Dec-22 7:02am

Maciej Los

Richard MacCutchan · Answer 2 · 2022-11-29T04:54:00

Solution 1

See VBA Macros - Region and Language[^] for how to modify locale settings.

Posted 29-Nov-22 4:54am

Richard MacCutchan

Microsoft XLODBC query- unable to add values for multiple columns in excel due to (, ) separator used for decimal numbers

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Microsoft XLODBC query- unable to add values for multiple columns in excel due to (, ) separator used for decimal numbers

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us