Unable to read data from CSV using ADO due to driver thinking. I work with integers / number and showing zeros instead of text

Question

I am trying to use ADO to read in a series of text files in a worksheet. I ran into problems when most of the data in a particular column is integers. It will give null values ​​(empty cells) when it reaches the row.

According to Microsoft support ( Ado mixed data tyes ) this is a common thing and the solution is to install IMEX = 1. I tried this, but it did not work.

I searched for other topics that searched for an answer and stumbled upon this answer ( another thread ) where the author says to change TypeGuessRowsto "get Jet to determine if the situation is of mixed types, and trick Jet into detecting a particular data type." However, this did not work 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  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 an example text file. Code reads everything except "P"

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

Answer 1

Basically, do not rely on registry entries as described here on MSDN .

Schema.ini , . Schema.ini , , - , , , ...

, txt , "" : , [test.txt], txt : Schema.ini

[test.txt]
Format=CSVDelimited

Col1=Column1 Text
Col2=Column2 Text

, strPath ( )

strPath = ThisWorkbook.Path & "\Excel_Barcode_Files\"

* , - , test.txt

, Schema.ini, , , Schema.ini

, SSCCE, , :

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

, P: