vba - Unable to read data from a CSV using ADO due to the driver thinking I am working with integers/number and showing nulls instead of text

Question

I am trying to use the ADO to read in a series of text files into a worksheet. I am running into problems when the majority of the data in a specific column are integers. It will give null values (blank cells) when it reaches a String.

According to microsoft support (Ado mixed data tyes) this is a common thing and the solution is to set the IMEX = 1. I tried this however it didn't work.

I have been searching others threads looking for the answer and came across this answer (other thread) where the author says to change TypeGuessRows to "get the Jet to detect whether a mixed types situation exists and trick the Jet into detecting a certain data type." However, this hasn't worked either.

Below is my VBA code. Any help would be appreciated

Sub query_text_file(WorkingSheet As String, Col As String, Row As Integer, fileName As String, firstOrLast As Integer)

Dim strPath As String
Dim ws As Worksheet

strToolWkbk = fileName
strPath = ThisWorkbook.Path & "Excel_Barcode_Files"
Set ws = Worksheets(WorkingSheet)

'Need to reference the:
'   Microsoft ActiveX Data Objects 2.5 Library
Dim s_rst As ADODB.Recordset
Dim s_cnn As ADODB.Connection 's for sub connection
Dim intRow As Integer

Const adOpenStatic = 3
Const adLockOptimistic = 3
Const adCmdText = &H1

Set s_cnn = New ADODB.Connection

s_cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";" _
& "Extended Properties=""text;HDR=Yes;IMEX=1;TypeGuessRows=12;FMT=Delimited"";"

s_cnn.Open
Set s_rst = New ADODB.Recordset


strSQL = "SELECT * FROM " & strToolWkbk


s_rst.Open strSQL, _
    s_cnn, adOpenStatic, adLockOptimistic, adCmdText

intRow = Row

s_rst.MoveFirst


Do Until s_rst.EOF
    ws.Range(Col & intRow) = s_rst(0)
    ws.Range(Chr(Asc(Col) + 1) & intRow) = s_rst(1)
    intRow = intRow + 1
    s_rst.MoveNext
Loop

s_rst.Close
s_cnn.Close

Set s_rst = Nothing
Set s_cnn = Nothing

End Sub

Here is a sample text file. The code reads in everything except the "P"

test test
P,0
1,1
5,2
6,3

Answer 1

Basically, don't rely on the registry entries as explained here on MSDN.

You need to create a Schema.ini file and put it in the same folder as all your text files. In the Schema.ini you specify the type for all columns you may have in your text files - it's just a much safer option to do that explicitly rather than have the driver work out the correct types for columns...

Say you have some txt files on your desktop, open Notepad and copy paste the below - make sure you adjust the [test.txt] part to match the name of your actual txt file and save it as: Schema.ini

[test.txt]
Format=CSVDelimited

Col1=Column1 Text
Col2=Column2 Text

Make sure you add another slash at the end of the parth in the strPath (also indicated in the article)

strPath = ThisWorkbook.Path & "Excel_Barcode_Files"

*Keep in mind that I am working in a different location to yours - I am using my Desktop for this example and my text file is named test.txt

Now, that you have a Schema.ini you can modify the connection string and take out some parameters which are not required because they exists in the Schema.ini

So bascially an SSCCE based on the above assumptions would be:

Sub Main()

    Cells.ClearContents

    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset

    Dim thePath As String
    thePath = "C:Users" & Environ("USERNAME") & "Desktop"


    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & thePath & ";" _
                        & "Extended Properties=""text;HDR=No;"""

    cn.Open

    Dim sql As String
    sql = "SELECT * FROM test.txt"

    ' populate the recordset
    rs.Open sql, cn, adOpenStatic, adLockOptimistic, &H1

    ' copy the recordset starting at Range("A1") - assuming there are no headers - see HDR = No;
    Range("A1").CopyFromRecordset rs

    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing

End Sub

Now after running this you should see all the values including the missing P: