Nobody can answer! Problem with commas and dots

[Poppe]

Hi

I have done vast research but i seem to get no working answer to my problem.

I have a code that sends data from excel sheet to database. It works
perfectly but the problem is values with commas and dots.

I have values like 1400,50 (EUROPEAN CHARSET)

Database reads values only like 1400.50 (US CHARSET)

I send the data with this code:

query = "INSERT INTO company (salary) SELECT " & CDbl(ActiveSheet.Cells(8, 5))

This makes the query look like:

"INSERT INTO company (salary) SELECT 1400,50"

I have set european regional setting in my control panel, SQL tries to read
the value as two values: 1400 and 50 (1400,50) - resulting into an error.

Salary is defined as double in the database.

I cannot simply make a script that changes all commas into dots, as there
can be values like 150.400,60 - i can never know where the comma is.

I can't ask all users to change their regional settings to US while using
the script, there are many users in many countries.

I cannot make the script change the whole system setting to US while the
script is run, as it would be too dangerous.

How can i make the visual basic understand that the value to be sent to the
SQL is 1400.50 and not 1400,50 ?

HELP!!!

 

[Per Jessen]

Hi

This should do it:

MyVal = Replace(ActiveSheet.Cells(8, 5), ",", ".")
query = "INSERT INTO company (salary) SELECT " & MyVal

Regards,
Per

 

[Peter T]

as there

can be values like 150.400,60

I don't think you would return the thousands separator when you read the
Value property (only if you read the cell's Text property)

for the decimal separator maybe this will help

Application.International(xlDecimalSeparator)

Typically the separator will be the same in Excel and for the system, but
not necessarily. For your needs I guess the system is more relevant, if
same as Excel I don't think you need to change your code at all. That is,
unless your db is to be used by users with different settings. For the
system setting there are API methods but it might be OK to simply return

Format$(0, ".")

Regards,
Peter T

 

[NickH]

Hi Poppe

The part of your INSERT statement - CDbl(ActiveSheet.Cells(8, 5)) - will
yield the locale format used by Excel, which has the comma as decimal setting.

If you substitute with - Format(ActiveSheet.Cells(8, 5),"0.00") - this
should give you the correct structure for US number formats in the INSERT
statement.

 

[Poppe]

Thanks for your replies. I tried all of above, but nothing works.

Results:

------------------

Per Jessen: Your code works only with values with a single comma. If i have
larger values like 100,400.60 - it does not. That would result in wrong
values

------------------

Peter T:

Application.International(xlDecimalSeparator) = The output result is ","

If i force the separator as dot, the execute command will still send the
value with comma:

With Application
.DecimalSeparator = "."
.UseSystemSeparators = False
End With

"INSERT INTO company (salary) SELECT " & ActiveSheet.Cells(8, 5))

Result = "INSERT INTO company (salary) SELECT 1400,50"

------------------

NickH:

"INSERT INTO company (salary) SELECT " & Format(ActiveSheet.Cells(8,
5),"0.00")

Result = "INSERT INTO company (salary) SELECT 1400,50"

It still uses commas.

------------------

It's really a tough problem. I also think it should read them in "raw" mode
where there is no comma, but for some reason it does. My regional setting in
control panel is Finnish.

What to do? This really seems like a question with no answer..

 

[Poppe]

I found something that might give a clue..

In excel 2007 help there is an example under "Format Function"

 

[NickH]

Hi Poppe

I always thought the Format string worked independently of the locale -
every day is a learning day

Try the following:

Dim strPointNum As String, strCommaNum As String, p As Integer
Dim strSQL As String
strcommaNum = Format(ActiveSheet.Cells(8, 5), "0.00")
p = InStr(1, strCommaNum, ",", vbTextCompare)
strPointNum = Left(strCommaNum, p - 1) & "." & Right(strCommaNum, 2)
strSQL = "INSERT INTO company (salary) SELECT " & strPointNum & ";"

This assumes the formatted number has two decimal places. If you need a
different number, you must adjust the code accordingly.

I tried this going from my number format (UK) to yours (Finland) and it
worked - substituting my "." with your ",". I have swapped the comma and
decimal stres around, so it should work as-is for you.

Nick

 

[Poppe]

Thanks for your reply.

I understand your idea, and i thought something similar. But the problem is
that you can never know where the comma exactly is. There can be different
kinds of values.

With this code:

Value 502.200,55 results into value 502.200.55 (should be 502,200.55)

Value 502.200,5 results into value 502.200.,5 (should be 502,200.5)


The Replace() function can change commas to dots, but situation is the same.
I have for example 2000 rows of excel chart with mixed values, changing the
code for all different values is impossible.

I can use code that forces whole windows system wide regional settings to
change while running the code, but that is a dangerous option and not good
coding.

 

[Peter T]

Value 502.200,55

Where are you getting an apparent value like that from. AFAIK, although it
might be displayed like that in a cell, VB/A would read it as 502200d55
where d is the system's decimal separator (not Excel's separator although
typically it's the same).

Regards,
Peter T

 

[NickH]

Yes, I agree. The underlying value in the cell is just a number without
punctuation.

 

[NickH]

Did you try the code I suggested?

 

[NickH]

Hi Poppe

I have done some more experimentation on this and my code works for both
cases of your sample data. In my case, I am doing the opposite from you;
i.e. taking numbers from UK locale (123,456.78) to European locale
(123.456,78) format.

I thought that your original data cells might be formatted as text, rather
than numbers. However, running my code on string format numbers still works.
I used "502,200.55" and "502,200.5" as string literals and they came out as
"502200,55" and "502200,50" respectively. The statement
'Format(ActiveSheet.Cells(8, 5),"0.0")' returns "502200.55" and "502200.50"
(in your system, you would have "," instead of "."). In other words, it has
stripped out the thousands separators, leaving just the decimal point. From
this point on, it is simply string manipulation - so providing you don't go
back to a number between deconstruction and the SQL statement, it should work.

Not sure where to go next on this.

 

[Poppe]

NickH & PeterT:

Thanks for your replies.

My code attempts:

-------

Dim CC As Double
Dim Query As String

CC = ActiveSheet.Cells(8, 5)
CC = CDbl(ActiveSheet.Cells(8, 5).Value)

Query = "INSERT INTO salary (amount) SELECT " & CC
Query = "INSERT INTO salary (amount) SELECT " & ActiveSheet.Cells(8, 5).Value

connection.Execute "INSERT INTO salary (amount) SELECT " &
Format(ActiveSheet.Cells(8, 5), "0.00")

-------

All of the above result the value being sent as "5020,5".

I see the values by adding all of the variables into Watch.

Also trying to run the code result in error "Insert value list does not
match column list. Wrong number of values for insert" as it tries to insert
5020 and 5 as two values.

Excel doesn't send it in RAW mode like it should (5020d5), it always sends
comma.
Formatting the Cell into text/value/general - has no effect.

How to get the underlying value 5020d5 ? This is a mystery to me.

NickH i'll try your code suggestion next. I have to post this answer now as
it is getting too long.

 

[Poppe]

When i try this, i get different results.

The value in the cell is 502,200.55. Trying to format the cell in
general/value/text has no effect. Result is always the same.

The code in full:

-----------

Dim strPointNum As String, strCommaNum As String, p As Integer
Dim strSQL As String
strCommaNum = Format(ActiveSheet.Cells(8, 5), "0.00")
p = InStr(1, strCommaNum, ",", vbTextCompare)
strPointNum = Left(strCommaNum, p - 1) & "." & Right(strCommaNum, 2)
strSQL = "INSERT INTO company (salary) SELECT " & strPointNum & ";"

-----------

After running all lines, the Watch tells me the variables get values:

p = 4
strCommaNum = "502,200.55"
strPointNum = "502.55"
strSQL = "INSERT INTO company (salary) SELECT 502.55;"

-----------

I don't know how you get value 502200.55. I have nothing else in my code.

BR,
Poppe

 

[NickH]

Good morning

Evidently your system behaves differently from mine, but that doesn't matter
as you have now given me enough to work on.

I have created a conversion function which you can use, with a test harness
to try it out. It works at string level, so the original cell number format
is immaterial.

I included a variety of test cases in the array of numbers - you can add to
these as much as you like.

Copy the following into a module in VBA and run 'DropCommasTest'. You can
step through the code to see what it is doing, from which you should be able
to adjust if you need to fine-tune anything.

The last few lines in the ConvertNumber function may not be needed. I just
added them to make sure there is always a decimal point with two places of
precision. If you start with the format statement in the string set-up, you
won't need those additional lines.

Let me know how this works.

Nick

Sub DropCommasTest()
Dim varNumbers As Variant, i As Integer, p As Integer, q As Integer
Dim strMessage As String
varNumbers = Array("502,200.55", "502200.5", "502,503,504", "123456.78", "1")
For i = 0 To UBound(varNumbers)
strMessage = "Original number is " & varNumbers(i) & vbCr
strMessage = strMessage & "New number is " & ConvertNumber(varNumbers(i))
MsgBox strMessage, vbInformation, "Converted number"
Next
End Sub

Private Function ConvertNumber(ByRef pNum As Variant) As String
Dim p As Integer, q As Integer
Dim strSourceNum As String, strNewNum As String
strSourceNum = pNum
Rem start with an empty string for output
strNewNum = vbNullString
q = 1
Rem loop through the string to remove commas
Do
Rem q is pointing to the next starting point in the source string
p = InStr(q, strSourceNum, ",", vbTextCompare)
If p = 0 Then Exit Do ' no (more) commas
strNewNum = strNewNum & Mid(strSourceNum, q, p - q)
q = p + 1
Loop
Rem check for remaining text to pick up
If q <= Len(strSourceNum) Then
strNewNum = strNewNum & Right(strSourceNum, Len(strSourceNum) - q + 1)
End If
Rem return the converted number
ConvertNumber = strNewNum
Rem the following ensures two decimal places ...
Rem may not be necessary in which case drop everything between here and End
Function
Rem check for presence of decimal point if needed
p = InStr(1, strNewNum, ".", vbTextCompare)
If p = 0 Then
ConvertNumber = strNewNum & ".00" ' no DP present
ElseIf (Len(strNewNum) - p) < 2 Then
ConvertNumber = strNewNum & "0" ' DP present, but only 1 place
Else
ConvertNumber = strNewNum
End If
End Function
--
Nick

When i try this, i get different results.

The value in the cell is 502,200.55. Trying to format the cell in
general/value/text has no effect. Result is always the same.

The code in full:

-----------

Dim strPointNum As String, strCommaNum As String, p As Integer
Dim strSQL As String
strCommaNum = Format(ActiveSheet.Cells(8, 5), "0.00")
p = InStr(1, strCommaNum, ",", vbTextCompare)
strPointNum = Left(strCommaNum, p - 1) & "." & Right(strCommaNum, 2)
strSQL = "INSERT INTO company (salary) SELECT " & strPointNum & ";"

-----------

After running all lines, the Watch tells me the variables get values:

p = 4
strCommaNum = "502,200.55"
strPointNum = "502.55"
strSQL = "INSERT INTO company (salary) SELECT 502.55;"

-----------

I don't know how you get value 502200.55. I have nothing else in my code.

BR,
Poppe

 

[Poppe]

Hi

I'll return on monday and let you know what happened.

Also how to get the underlying value of a cell (4000d5), instead of what it
displays (4000,5), would be very interesting. I know the database doesn't
have commas either, the software converts the raw values into proper format
for display. Getting visual basic/excel to give this raw format seems to
behind a brick wall.

BR,
Poppe

 

[Peter T]

In your system a comma is the d separator, which means whenever you see a
decimal value in all programs you'll see a comma (unless formatted to
display otherwise). It's best to think of the 'd' as a symbol, like a +/-
sign rather than a format character such as a thousands separator.

When you use the Format function the dot in the format string represents the
symbol and acts an instruction how to format either side of the decimal,
which for you will continue to display as a comma. However if you want to
change the comma to a dot you need to escape it, try this

dbl = 12345.67
MsgBox dbl ' 12345,67
s = Format(dbl, "0\.00")
MsgBox s ' 12345.67

but .....

MsgBox Val(s) ' 12345,67

If you write your formatted dot value to a double value it'll revert to the
d-comma separator.

In your d-comma system, as I mentioned before, I don't think you need to do
anything, probably best to leave it alone! However the only problem is when
the file is exchanged between users of different decimal separator systems,
and then only if the value is stored as a string. In that scenario you will
need to ensure the separator is consistent with what the system expects, and
parse/convert if/as necessary.

Regards,
Peter T

 

[Poppe]

Hello

It seems my fridays reply did not post here at all.

Anyways, i tried your code and we are close to the answer.

This code basically removes commas. I was thinking about the situation, and
concluded that what we need to accomplish is to convert all commas into dots,
and all dots into commas. This would result in perfectly working code, and it
wouldn't matter where the commas/dots are, as it changes every one of them.

For example:

100.000.000,50 -> 100,000,000.50

222,222,222.50 -> 222.222.222,50

I was trying to make the code work in this manner:

----------

1) Convert value to string.
2) Go the string through char by char.

If comma is detected -> replace it with dot.
If dot is detected -> replace it with comma.

3) Keep track of changes, and in the end save changed value

----------

I'm having some trouble getting the code work, as it has to remember what
character has been checked already.

BR,
Poppe

 

[Peter T]

Did you see my reply to your Friday's post...

Regards,
Peter T

 

[Poppe]

Hi

Thanks for your reply.

I was now able to find a permanent solution how to send the underlying data
to database. There is no longer need to manipulate data with strings, as it
sends variables like Double as real Double values.

The solution is accomplished by using ADO Command object events. Previously
i was only using ADO Connection events.

The data is now sent as it should, in "raw" mode and database accepts them
perfectly.

Thanks for everybody their help. I was able to learn more from your help.

BR,
Poppe