importing a csv into access from excel via ado
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:
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;"
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
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
Tags for this Thread
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1