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.