1

I have a txt file fromated like this:

"SORKOD";"BE0010";"BE0020";"BE0030"
"DEPO_000001";"";"5720004850313864";"266653"
"DEPO_000002";"";"5720004850356083";"278173"
"DEPO_000003";"";"5720004850346091";"286179"

Because it is a large database to open, I wanted to import a filtered table. Used the following code, but it imports only the first column, and don't know why.

Sub GetMyTXTData()
Dim xlcon As ADODB.Connection
Dim xlrs As ADODB.Recordset


Set xlcon = New ADODB.Connection
Set xlrs = New ADODB.Recordset
Dim currentDataFilePath As String
Dim currentDataFileName As String
Dim nextRow As Integer

currentDataFilePath = "K:\TB\"
currentDataFileName = "698"

xlcon.Provider = "Microsoft.ACE.OLEDB.12.0"
xlcon.ConnectionString = "Data Source=" & currentDataFilePath & ";" & "Extended Properties=""text;HDR=NO;FMT=Delimited(,);""" 
xlcon.Open
xlrs.Open "SELECT * FROM [" & currentDataFileName & ".txt] WHERE F1='DEPO_000001'", xlcon
xlrs.MoveFirst

nextRow = 4
Sheets(1).Rows(nextRow & ":" & Sheets(1).Rows.Count).Delete

Sheets(1).Cells(nextRow, 1).CopyFromRecordset xlrs
xlrs.Close
xlcon.Close
Set xlrs = Nothing
Set xlcon = Nothing
End Sub
byte me
  • 770
  • 6
  • 13
inorbert
  • 11
  • 2

1 Answers1

0

You need a schema.ini file in the same folder as the text file. The information defines the "custom" settings which aren't being recognized in the connection string.

The content of the file to reflect the settings in the connection string:

[CSVData.txt]
 ColNameHeader=False
 Format=Delimited(;)

And the connection string is then

xlcon.ConnectionString = "Data Source=" & currentDataFilePath & ";" & "Extended Properties=""text;"""

I tested and it works; the original code gave me the same results reported in the question.

Links I found all refer to languages other than VBA, but the information was correct. For example: Microsoft.ACE.OLEDB.12.0 CSV ConnectionString, Unable get value of all columns from CSV using OLEDB

Cindy Meister
  • 25,071
  • 21
  • 34
  • 43