Two_short_planks
asked on
Access 2010: How to convert numbers imported in european format #.##0,00 to UK format #,##0.00
Hi
I'm receiving CSV files with values formatted euro style so when I bring into access (with UK format numbers) I need to convert them. Currently my only solution is to import them as text and then use 4 steps for each field to swap the commas and decimal dots around and then value the result like this:
expr1: Replace([field1],',','a')
expr2: Replace([expr1],'.','b')
expr3: Replace([expr2],'a','.')
expr4: Replace([expr3],'b',',')
and then value it. I am no programmer (as you can tell) but there must be an easier way to do this - or even a built in function. Please could anyone suggest a more efficient solution as I have multiple fields to convert.
Many thanks
I'm receiving CSV files with values formatted euro style so when I bring into access (with UK format numbers) I need to convert them. Currently my only solution is to import them as text and then use 4 steps for each field to swap the commas and decimal dots around and then value the result like this:
expr1: Replace([field1],',','a')
expr2: Replace([expr1],'.','b')
expr3: Replace([expr2],'a','.')
expr4: Replace([expr3],'b',',')
and then value it. I am no programmer (as you can tell) but there must be an easier way to do this - or even a built in function. Please could anyone suggest a more efficient solution as I have multiple fields to convert.
Many thanks
als315
During import of csv file you can select delimiters (button advanced):
ASKER
Hi
Thanks for the quick response.
My understanding is that the field delimiter is simply for identifying the different fields i.e. the file I receive is semi-colon delimited so that is selected in that box.
I don't think this allows for the conversion of #.##0,00 to #,##0.00 does it?
I think that's a separate issue? Or am I missing the point?
Thanks again
Thanks for the quick response.
My understanding is that the field delimiter is simply for identifying the different fields i.e. the file I receive is semi-colon delimited so that is selected in that box.
I don't think this allows for the conversion of #.##0,00 to #,##0.00 does it?
I think that's a separate issue? Or am I missing the point?
Thanks again
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
You are correct, we can't set thousands delimiter in import specification, but you can import (or link) field as text and convert in query:
CDbl(Replace(Replace([Your
CDbl(Replace(Replace([Your
ASKER
HI Jerryb30
Thanks for the code. IT doesn't seem to work however. When I run it it compes up with compile error and this:
I invoke it in the query as follows:
newvalue: E2UK([field1])
Maybe I'm running it incorrectly?
@als315 - I tried nesting the replace but it didn't like it - also the nesting that you kindly suggest above has the problem that without an intermediate step it doesn't know which of the new commas should be replaced by dots without an intermediate step?
Thanks everyone so far
Thanks for the code. IT doesn't seem to work however. When I run it it compes up with compile error and this:
I invoke it in the query as follows:
newvalue: E2UK([field1])
Maybe I'm running it incorrectly?
@als315 - I tried nesting the replace but it didn't like it - also the nesting that you kindly suggest above has the problem that without an intermediate step it doesn't know which of the new commas should be replaced by dots without an intermediate step?
Thanks everyone so far
ASKER
Hi Jerryb30
Fixed it _ I just needed to add the "end function" and now it works perfectly
Thank you : )
Fixed it _ I just needed to add the "end function" and now it works perfectly
Thank you : )
ASKER
Which you had typed anyway...... doh!!
I need to learn to read!
I need to learn to read!