trouble importing csv files to mysql from vbs and access

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?

From stackoverflow

• 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.

  Jason Stanthorp : There is no schema.ini, just the vbscript and the database.

  Jason Stanthorp : Actually a schema.ini file is generated, and ColNameHeader=True is set.

  onedaywhen : Well, trying setting it to false :)

  Jason Stanthorp : That made no difference :(

  onedaywhen : Works for me (tested example added to my answer).

  Jason Stanthorp : Please check my revised question

  onedaywhen : Check you've a) actually saved the schema.ini file, b) saved it in the same folder as the Database in the SQL, c) actually named it schema.ini (your edit says "scheme.ini"), d) for me right now Year(Date) & Month(Date) & Day(Date) & ".csv" returns 2009428.csv (you seem to have CSV2009427.csv i.e. yesterday's date).