Hi,
I have a macro in an excel spreadsheet that imports a csv file into an access table via ado. The csv is a download from sap and has 44 fields.
One of the fields (Col34) in the csv is a free text. One day someone entered in sap - "Shipping 45 " OD - special loading" and this became a value in this csv field.
When the code was executed in the excel spreadsheet, everything after the double quote got truncated for this record.
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
Sub mcrUplSAP()
On Error GoTo mcrUplSAP_err
strSAPFName = Dir("" & strSAPFLoc & "*.csv")
If strSAPFName = "" Then Exit Sub
strTextSQL = "select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24," _
& "F25,F26,F27,F28,F29,F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,F40,F41,F42,F43 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
If cn.State <> adStateOpen Then Exit Sub
strSQL = "INSERT INTO [SAPinfo] ([Order],[Item],[Sch Line],[Ship-to], " _
& "[Ship-to Name],[Sold-to],[Sold-to Name],[Cross Dock],[Cross Dock Location],[Route],[Dest City], " _
& "[State],[Route Desc],[Transit],[Allowed Total Wt],[UoM], " _
& "[CSR],[Shipping Point],[Load Date],[Delv Date],[Line Haul Carrier], " _
& "[Line Contract No],[SchLine Qty],[UoM1],[Product Line], " _
& "[SKU],[Bill of Lading],[RailCar ID],[Delv#],[Delivery Qty (MT)], [PGI Date]," _
& "[Shipment],[O A No],[Rail Load Instructions],[Mode],[DC],[Width (CM)],[Width (IN)],[Dia (CM)],[Dia (IN)],[PO#], " _
& "[Ordered Qty (LB)],[Delivery Qty (LB)]) IN '" & dbPath & "'" _
& " " & strTextSQL & ""
'Delete all existing records in SAPinfo table
rs.Open "Delete * from [SAPinfo] IN '" & dbPath & "'", cn
'Insert new records from a downloaded file
cn.Execute strSQL, lngRecAff
I have a macro in an excel spreadsheet that imports a csv file into an access table via ado. The csv is a download from sap and has 44 fields.
One of the fields (Col34) in the csv is a free text. One day someone entered in sap - "Shipping 45 " OD - special loading" and this became a value in this csv field.
When the code was executed in the excel spreadsheet, everything after the double quote got truncated for this record.
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
Sub mcrUplSAP()
On Error GoTo mcrUplSAP_err
strSAPFName = Dir("" & strSAPFLoc & "*.csv")
If strSAPFName = "" Then Exit Sub
strTextSQL = "select F1,F2,F3,F4,F5,F6,F7,F8,F9,F10,F11,F12,F13,F14,F15,F16,F17,F18,F19,F20,F21,F22,F23,F24," _
& "F25,F26,F27,F28,F29,F30,F31,F32,F33,F34,F35,F36,F37,F38,F39,F40,F41,F42,F43 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
If cn.State <> adStateOpen Then Exit Sub
strSQL = "INSERT INTO [SAPinfo] ([Order],[Item],[Sch Line],[Ship-to], " _
& "[Ship-to Name],[Sold-to],[Sold-to Name],[Cross Dock],[Cross Dock Location],[Route],[Dest City], " _
& "[State],[Route Desc],[Transit],[Allowed Total Wt],[UoM], " _
& "[CSR],[Shipping Point],[Load Date],[Delv Date],[Line Haul Carrier], " _
& "[Line Contract No],[SchLine Qty],[UoM1],[Product Line], " _
& "[SKU],[Bill of Lading],[RailCar ID],[Delv#],[Delivery Qty (MT)], [PGI Date]," _
& "[Shipment],[O A No],[Rail Load Instructions],[Mode],[DC],[Width (CM)],[Width (IN)],[Dia (CM)],[Dia (IN)],[PO#], " _
& "[Ordered Qty (LB)],[Delivery Qty (LB)]) IN '" & dbPath & "'" _
& " " & strTextSQL & ""
'Delete all existing records in SAPinfo table
rs.Open "Delete * from [SAPinfo] IN '" & dbPath & "'", cn
'Insert new records from a downloaded file
cn.Execute strSQL, lngRecAff
