Import Text File Via ADO - Null Values

[twoplustwo]

Hey guys, hope you're all well.

I am importing a large text file via ADO. A given column contains both numeric and text values - these are identifiers for a product type. When I read the file to the recordset the first value sets the variant array field type (in this case double). The text value(s) are subsequently set to 0.

Anybody have any suggestions on how to fix? I originally parsed the whole file to an array but this took a while...

I don't actually need to loop through Field(4), I just wanted to view the results.

Set cnConnection = CreateObject("ADODB.CONNECTION")
cnConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                 "Data Source=" & sInputFolder & ";" & _
                 "Extended Properties=""text;HDR=Yes;FMT=Delimited"""
 
Set rsRecordset = CreateObject("ADODB.RECORDSET")
With rsRecordset
   .Open "SELECT * FROM " & sFileName, cnConnection, 3, 1, 1
   lnRowCount = .RecordCount
   varrInput() = .GetRows(lnRowCount)
   .MoveFirst
   If Not .BOF Then
      Do While Not .EOF
         Debug.Print .Fields(4)
         .MoveNext
      Loop
   End If
End With

 

[twoplustwo]

I did try this:

sSql = "UPDATE " & sFileName & " SET " & sFileName & ".Field5 = ""'"" & [Field5]"

To add an apostrophe to the values in Field 5 to no avail. Error "No value given for one or more parameters." was raised.

sFileName has the .csv suffix - might this be causing a problem?

Thanks.