0

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.

Community
  • 1
  • 1
MGP
  • 2,480
  • 1
  • 18
  • 31

1 Answers1

2

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.

Community
  • 1
  • 1
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
  • Thanks a lot, for the detailed answer, I'll have a look into it, and will get back to you! – MGP Jan 16 '20 at 10:45
  • I have updated my question... I'm not quite sure, what I'm doing wrong – MGP Jan 16 '20 at 11:49
  • @MG92 Actually if you change your original question (an a way the question asked changes) then you should ask a new one. The error you describe now is completely different from your original quesition. And my answer was for your original question. – Pᴇʜ Jan 16 '20 at 14:12