trouble importing csv files to mysql from vbs and access

Question

I am using the following code, based on from previous posts and answers by Remou and Anthony Jones.

Dim db: db = "C:\Dokumente und Einstellungen\hom\Anwendungsdaten\BayWotch4\baywotch.db5"
Dim exportDir: exportDir = "C:\Dokumente und Einstellungen\hom\Desktop"
Dim exportFile: exportFile=NewFileName(exportDir)


Dim cn: Set cn = CreateObject("ADODB.Connection")

cn.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
    "Data Source =" & db

cn.Execute "SELECT * INTO [text;HDR=No;Database=" & exportDir & _
   ";CharacterSet=65001]." & exportFile & " FROM tblAuction"

'Export file

'Support functions

Function NewFileName(ExportPath)
Dim fs 
Dim NewFileTemp

Set fs = CreateObject("Scripting.FileSystemObject")

NewFileTemp = "CSV" & Year(Date) _
    & Month(Date) & Day(Date) & ".csv"

NewFileName = NewFileTemp
End Function

The problem I am having, is when I export the file, the csv file contains headers, despite HDR being set to No. It will have the names of my columns in quotes before the actual data, which causes problems when attempting to import.

My second problem is that special characters do not seem to be escaped.

I am loading the data into mysql with:

LOAD DATA LOCAL INFILE 'file' INTO TABLE MYTABLE FIELDS TERMINATED BY ';' ENCLOSED BY '"' ESCAPED BY '\\'

I have also tried without the ESCAPED BY clause.

The problem is that one of the fields contains html data, which means quotes, slashes etc. This causes the data to be imported incorrectly, with date fields being inserted into the usernames fields and such. How can I escape to stop this from happening, or import correctly?

I am using a scheme.ini like the following:

[CSV2009427.csv]
ColNameHeader=No
CharacterSet=65001
Format=Delimited(;)
Col1=article_no Char Width 19

And column headers are still exported. Is it not possible to do this in a way without requiring schema.ini? I.e. being able to use the script in a portable way, where a schema.ini may not alway exist?

Answer 1

The problem I am having, is when I export the file, the csv file contains headers, despite HDR being set to No.

I think you need to need to include ColNameHeader=False in the Schema.ini File.

Example:

C:\schema.ini

[blah.csv]
ColNameHeader=False
CharacterSet=1252
Format=CSVDelimited
Col1=pence_amount Integer

SQL code:

SELECT * INTO [text;Database=C:\].blah#csv FROM Coins;

Note the schema.ini file is saved in the same directory as specified by Database in the connection string in the SQL.

Result: no headers.