I have a macro in an excel spreadsheet that imports a csv file into an access table via ado. The csv file has 4 fields - F1, F2, F3 and F4

One of the fields (F2) in the csv is a free text. Sometime this field gets text with special characters, specifically double qoutes, i.e. Shipping 45 " OD - special loading.

When the code was executed in the excel spreadsheet, everything after the double quote got truncated for this record.

So, in this expample F1 would have a value in the Access, F2 would be Shipping 45 and the rest would be truncated, F3 and F4 would be empty.

How do I deal with this problem - dealing with special characters from a csv file

Thanks for your help in advance.

Here is the code:

'csv file
strTextSQL = "select F1,F2,F3,F4 from [TEXT;HDR=YES;DATABASE=" & strSAPFLoc & ";].[" & strSAPFName & "]"

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

conString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"DATA SOURCE= " & dbPath & ";" & _
"Jet OLEDB:Engine Type=4;"

cn.Open conString

strSQL = "INSERT INTO [SAPinfo] ([Order],[Item],[Sch Line],[Rail Load Instructions]) IN '" & dbPath & "'" _
& " " & strTextSQL & ""

'Insert new records from a downloaded file
cn.Execute strSQL, lngRecAff