ado recordset from txt file not delimiting

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
Good afternoon all

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

Code:
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
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
6,619
That should work if you create a schema.ini file in the same folder containing:
Code:
[ReportDataSmall.txt]
Format=TabDelimited
ColNameHeader=True
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,368

ADVERTISEMENT

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
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,039
Thanks to you both, I now have what I need for a working solution :)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,822
Messages
5,598,307
Members
414,224
Latest member
Crazy_FC

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top