Dealing with different number formats in import spec

[diannosd]

Hello, i'm using Access 2010

I am now managing a database which is being used or importing text files.

The problem or question I have is, Can a spec be programmed dynamically to deal with different number formats and always change to English.

For example, if I import a German text file using my stored spec, I would like it to recognise / convert 1.000,99 to 1,000.99

[TheSmileyCoder]

I don't think the import Specification itself can be set up in such a way. But it would be possible to import it as text, to a temporary table, then run a code to check if the third character from the right is a , or a . and react accordingly, and then finally transfer the records from the temp table into your main table.

[diannosd]

Interesting and simple, thank you. Using your method, I woudl then append the temp table to the main table of cause, is there a function available for converting German to English for example?

[twinnyfo]

you could use the Mid() function, to find the value of the third to last character in teh string:

Dim sChar as String
sChar = Mid(UnknownMonetaryValue, Len(UnknownMonetaryValue)-2, 1)
If sChar = "," Then
    'Value is German
ElseIf sChar = "." Then
    'Value is UD
Else
    'Value is Unknown
End If

[zmbd]

The problem is that OP didn't specify if the numer was currency, fixed point, or some other floating point; thus, left-2 may not work for the text input.


-z

[zmbd]

the position of the division mark

A partial thought....

Logically, the position of the division mark between the integer and factional parts of the number will follow the grouping mark ( for English: commas before periods etc...)

Using English notation we need to handle the following:
[a] # - any integer less than one-thousand
[b] #,000 - any integer greater than 999
[c] #.0# - any numerical less than one-thousand
[d] #,000.0# - any numerical greater than 999

Using the instr function
[a] determine if there are any comma/periods and if not then treat as any standard integer
[b] This is tricky if the # is not equal to zero. If the first number is zero, and there are no other numbers between the leading zero and the dividing mark, then we can treat the number as a fractional and convert as needed. However if the first number is non-zero then we may have a situation as in [c]
[c] same as [b]
[d] if the comma is before the period then we have an English style etc.

Cases [b] and [c] have me stumped for the moment

-z

[twinnyfo]

diannosd,

Do you have a sample of the text files you will be importing? This would assist the entire forum in coming up with some ideas for importing....