使用VBA从CSV /导入到Access的CSV导出 - schema.ini CharacterSet问题

Question

I am currently working on a thing where I want to export various tables from a MySQL Database to csv files and it should then be possible to import those csv files into an Access database with VBA.

I managed to produce a download of a zip file that contains csv files and a schema.ini. For importing the csv files, I use roughly (loop is omitted for the sake of brevity) this code:

Sub ImportTableOne()
    Dim db As DAO.Database
    Set db = CurrentDb()
    frompart = "[Text;FMT=CSVDelimited;HDR=Yes;DATABASE=" & CurrentProject.Path & "\csvfiles\" & ";].[fileone.csv];"
    db.Execute _
    "SELECT * INTO tableone FROM" & frompart, dbFailOnError
    db.TableDefs.Refresh
    RefreshDatabaseWindow
End Sub

My problem is that I can either have CharacterSet=ANSI in my schema.ini which of course produces a result where for example í turns into í-. Changing things in the schema.ini to CharacterSet=UTF-8 will produce a run-time error 3000: reserved error (-5402).

I already tried to get the csv file to behave more UTF-8ish in php by adding a UTF-8 bom right after fopen() like so:

fputs($csvfile, chr(0xEF) . chr(0xBB) . chr(0xBF) );

When I open the csv files in excel or notepad++, the charset seems to be ok. When having the UTF-8 bom in php and trying to import, the bom just ends up being part of the first column heading like so: columnheadingone. That might not be very surprising for all I know about how the schema.ini works.

I would appreciate any hints as to how to resolve this.

Answer 1

The "Text ISAM" driver and its associated "schema.ini" file do not support UTF-8 files because they pre-date the Unicode era. The "Selecting a Character Set" section of the MSDN document

Schema.ini File (Text File Driver)

says

You can select from two character sets: ANSI and OEM.

So, you will probably need to devise another way to import your data. If you want to use UTF-8 then you may be interested in a related question here.