ado recordset from txt file not delimiting

[baitmaster]

Good afternoon all

I'm using the following code to create an ADO recordset from a tab-delimited text file:

Sub LoadFile()
Dim connCSV As New ADODB.Connection
Dim rsTest As New ADODB.Recordset

Dim path As String: path = ThisWorkbook.path & "\" 


connCSV.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & path & ";Extended Properties=""text;HDR=yes;FMT=Delimited"""

rsTest.Open "Select * From ReportDataSmall.txt", connCSV, adOpenStatic, adLockReadOnly, adCmdText

Sheet1.Cells(1, 1).CopyFromRecordset rsTest

End Sub

I'm expecting to see the data appear on sheet 1, delimited across multiple cells. Instead the data is concatenating into column 1 only. This is a test only, as I'm trying to create recordsets on which I can perform various SQL commands purely from the text file. Can anyone suggest why I'm not getting multiple columns in my recordset?

Thanks

 

[John_w]

That should work if you create a schema.ini file in the same folder containing:

[ReportDataSmall.txt]
Format=TabDelimited
ColNameHeader=True

 

[baitmaster]

Thanks John

This has partially worked but it's not ideal. I don't know until run-time, what the name of the text file will be - it could and will vary. Also, some of my column headers have disappeared (I tried setting colNameHeader = false). There's no obvious pattern for why some have gone, there are 3 there then 1 missing, 1 there then another 2 missing, etc. I'd also prefer to keep everything inside the one file as I'm not the end user

My requirement is to point to a text file once (.txt, no ability to change this), then be able to turn that into an ADO recordset that I can analyse on demand, filtering on various categories. I can do that by opening the text file with Excel, saving it and referring to that Excel object, but I don't want to take that approach as it's messy, I'd like to pull the data directly from the .txt

If I understand why headers are going missing, I guess I could check for the schema.ini and create / delete it on demand, that would probably solve the other issues..?

 

[baitmaster]

Update:
I've been working on an alternative approach, i.e. opening the .txt file via Excel, creating an Excel object that I know I can work with, and creating an ADO recordset from that. Still have same problem with missing column headers. I've now realised that the columns that have no headers, are the ones with numerical data - i.e. numbers or dates. All others are fine

When I create the Excel object the headers are there. When I create the ado recordset and then paste the recordset back to Excel, those headers have gone - but the data itself is fine. Obviously I can't work with the recordset if the headers are missing. Why would these disappear?!

 

[Fazza]

They may be there

Using range.copyfromrecordset pastes only the data; it does not paste the headers

To get them, loop through the recordset.fields collection and grab the names

Suggest you google for an example. I'll have a quick look too

regards

 

[Fazza]

first hit from google

http://stackoverflow.com/questions/4190515/pulling-column-names-into-excel-from-sql-query

 

[baitmaster]

Thanks to you both, I now have what I need for a working solution