CODEWITHSUNDEEP
excelvbams-access
MGP
MGP

Reputation: 2551

DoCmd.TransferText where delimiter is semicolon and decimal is comma

I'm trying to import a csv file with:

Dim appAccess As Access.Application
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase (databasePath)
appAccess.DoCmd.TransferText transferType:=acImportDelim, tableName:=dbTableName, Filename:=strPath, hasFieldNames:=True

I'm using a German machine, where the standard delimiter is ; and the standard decimal-separator is ,.

If I use those separators, I get an error (the data is not separated correctly).

If I change the separator in the csv file to ,and the decimal-separator to ., the data is loaded in the database, but the . is ignored and numeric values therefore aren't imported correctly.

I don't have the option, to create an import scheme in Access manually. Is there a way, to do this with VBA?

I created a Schema.ini file, which looks like this:

[tempfile.csv]
Format=Delimited(;)
ColNameHeader=True
DecimalSymbol=","

I saved it in the same folder where the csv file is located.

I still get a Runtime-Error, saying field1;field2;... is not a header in the target table. So I'm guessing, the method didn't use ; as a delimiter.

Upvotes: 0

Views: 5384

Answers (1)

Pᴇʜ
Pᴇʜ

Reputation: 57733

If you have a look at the documentation of the DoCmd.TransferText method there exists a parameter SpecificationName which says:

A string expression that's the name of an import or export specification you've created and saved in the current database. For a fixed-width text file, you must either specify an argument or use a schema.ini file, which must be stored in the same folder as the imported, linked, or exported text file.

To create a schema file, you can use the text import/export wizard to create the file. For delimited text files and Microsoft Word mail merge data files, you can leave this argument blank to select the default import/export specifications.

So if you are not able to generate that schema.ini file using the wizard you can generate it yourself in the same folder as your files to import. For a documentation how to build that file see Schema.ini File (Text File Driver).

It should look something like the following I think:

[YourImportFileName.csv]
Format=Delimited(;)
DecimalSymbol=","

Note that you have to generate one ini file for each CSV file you want to import because the first line is always the name of the import file. So generate the schema.ini, import, delete the ini and start over generating the next ini for the next file.

If you want to generate that ini file with VBA on the fly, have a look at How to create and write to a txt file using VBA.

Upvotes: 2

Related Questions