Access VBA, importing csv file via TransferText with commata as decimal separator and semicolon as delimiter

0
votes

I'm having some problems importing double numbers from csv files. The files have a semicolon delimiter and comma as decimal separator.

I can't set up import specs since the order of the fields in the csv often changes and it would be a desaster if the data goes into the wrong field.

Also the csv files will have to written to a temporary table first. Don't hate me for it, but since I have to process data and set some information fields for later data processing this is by far the easiest, fastest and safest way to achieve it.

Here is the problem itself:

When using TransferText it will import, but of course interpret the comma as delimiter. Not good ...

When replacing comma by full stop and semicolon by comma it works. But it will ignore full stops, so 1.2 becomes 12, 1.333 becomes 1333. The field will be of type double.

I've tests numerous things. Besides TransferText I've tried:

DoCmd.RunSQL ("INSERT INTO Tabelle1 SELECT cdbl(a1) as aa FROM[TEXT;FMT=Delimited;HDR=YES;CharacterSet=437;DATABASE=C:\SPOT].[test.csv]")

But nothing seems to work, even when I create a new table with field type DOUBLE before using TransferText ... decimals are still ignored.

So, I would be happy if you could tell me either how to use TransferText with or without replacing semicolon and comma in a first step or how to use the INSERT INTO stuff.

Thank you very much!

1
vbacsvimport

1 Answers

0
votes

Ok, I think I got it!

The problem where the regional settings and that my Access uses comma as decimal separator. I was also not able to create a Import Spec via manual import, since it needs to have defined which fields will have to be imported.

What I did now was this:

Open the table MSysIMEXSpecsthat contains the import specs via query: select * from MSysIMEXSpecs

Then add a new row and set SpecName = "Whatever", DecimalPoint= "," and 'FieldSeparator` = ";" and whatever other settings have to be made.

Since there is this workaround, isn't there a way to do this easier?