Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
Hi
This should do it: MyVal = Replace(ActiveSheet.Cells(8, 5), ",", ".") query = "INSERT INTO company (salary) SELECT " & MyVal Regards, Per "Poppe" skrev i meddelelsen ... 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!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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 "Poppe" wrote in message ... 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!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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. -- Nick "Poppe" wrote: 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!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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.. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
I found something that might give a clue..
In excel 2007 help there is an example under "Format Function" ------------ Dim MyStr MyStr = Format(5459.4, "##,##0.00") ' Returns "5,459.40". ------------ But when i run this code i do not get "5,459.40" i get "5 459,40" So what is going on? Not even the excel help file example works! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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 -- Nick "Poppe" wrote: 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.. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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 "Poppe" wrote in message ... 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. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
Yes, I agree. The underlying value in the cell is just a number without
punctuation. -- Nick "Peter T" wrote: 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 "Poppe" wrote in message ... 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. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
Did you try the code I suggested?
-- Nick "Poppe" wrote: 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. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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. -- Nick "Poppe" wrote: 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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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" wrote: 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. -- Nick |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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 "Poppe" wrote: 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" wrote: 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. -- Nick |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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" wrote in message ... 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 |
#18
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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 |
#19
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
Did you see my reply to your Friday's post...
Regards, Peter T "Poppe" wrote in message ... 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 |
#20
Posted to microsoft.public.excel.programming
|
|||
|
|||
Nobody can answer! Problem with commas and dots
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Combobox changes the decimal commas to dots - How to prevent it? | Excel Discussion (Misc queries) | |||
Change dots to commas!! | Excel Discussion (Misc queries) | |||
how do u set up multiple choice answer buttons/dots? | Excel Worksheet Functions | |||
Replacing dots with commas using macro | Excel Programming | |||
Change Dots into Commas as Decimal Divider | Excel Discussion (Misc queries) |