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

by

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:

enter image description here

More Related Contents:

  1. How do I get values from a hidden worksheet using VBA [closed]
  2. How to copy cells that contain certain text to another worksheet [closed]
  3. VBA Macro for copying and pasting columns on same excel sheet
  4. Levenshtein Distance in VBA [closed]
  5. VBA Runtime Error 1004 “Application-defined or Object-defined error” when Selecting Range
  6. How do I base64 encode a string efficiently using Excel VBA?
  7. Assign event handlers to controls on user form created dynamically in VBA
  8. Programmatic Access To Visual Basic Project Is Not Trusted
  9. Can I make this macro more efficient or faster?
  10. Controlling IE11 “Do you want to Open/Save” dialogue window buttons in VBA
  11. How to get the background color from a Conditional Formatting in Excel using VBA
  12. VBA Excel Populate ListBox with multiple columns
  13. VBA Integer Overflow at 70,000
  14. How to count up text of a different font colour in excel
  15. How to “flatten” or “collapse” a 2D Excel table into 1D?
  16. VBA Macro to compare all cells of two Excel files
  17. What does ReDim Preserve do?
  18. VBA – How the colon `:` works in VBA code with condition
  19. How to create a separate CSV file from VBA?
  20. VBA: How to change the value of another cell via a function?
  21. Sending formatted Lotus Notes rich text email from Excel VBA
  22. How can I generate GUIDs in Excel?
  23. Range.Find not making a difference between January and November (February and December) in VBA Excel
  24. Remove Duplicates from range of cells in excel vba
  25. Excel Select vs Activate
  26. Fast compare method of 2 columns [closed]
  27. How can an Excel Add-In respond to events in any worksheet?
  28. Fastest way to check if two ranges are equal in excel vba [duplicate]
  29. How to call function from another specific workbook in VBA?
  30. Excel VBA Run-time error 1004 when inserting or value formula into cell

Leave a Comment