Import data from a text file (ADO)

The procedure below can be used to get an ADO recordset from a text file and fill in the result in a worksheet.

Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range)
' example: GetTextFileData "SELECT * FROM filename.txt", _
              "C:\FolderName", Range("A3")
' example: GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
              "C:\FolderName", Range("A3")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, f As Integer
    If rngTargetCell Is Nothing Then Exit Sub
    Set cn = New ADODB.Connection
    On Error Resume Next
    cn.Open "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
        "Dbq=" & strFolder & ";" & _
        "Extensions=asc,csv,tab,txt;"
    On Error GoTo 0
    If cn.State <> adStateOpen Then Exit Sub
    Set rs = New ADODB.Recordset
    On Error Resume Next
    rs.Open strSQL, cn, adOpenForwardOnly, adLockReadOnly, adCmdText
    On Error GoTo 0
    If rs.State <> adStateOpen Then
        cn.Close
        Set cn = Nothing
        Exit Sub
    End If
    ' the field headings
    For f = 0 To rs.Fields.Count - 1
        rngTargetCell.Offset(0, f).Formula = rs.Fields(f).Name
    Next f
    rngTargetCell.Offset(1, 0).CopyFromRecordset rs ' works in Excel 2000 or later
    'RS2WS rs, rngTargetCell ' works in Excel 97 or earlier
    rs.Close
    Set rs = Nothing
    cn.Close
    Set cn = Nothing
End Sub

The procedure can be used like this:

Sub TestGetTextFileData()
    Application.ScreenUpdating = False
    Workbooks.Add
    GetTextFileData "SELECT * FROM filename.txt", "C:\FolderName", Range("A3")
'    GetTextFileData "SELECT * FROM filename.txt WHERE fieldname = 'criteria'", _
        "C:\FolderName", Range("A3")
    Columns("A:IV").AutoFit
    ActiveWorkbook.Saved = True
End Sub

Replace filename.txt with the name of the text file you want to get data from.
Replace C:\FolderName with the name of the folder where the text file is saved.

The first row in the text file will be used as column headings/field names.
Each column with datwa must be separated with the list separator character that is used in the regional settings in the Control Panel. I Norway this usually is semicolon (;), in other countries this can be a comma (,).

You'll find the procedure RS2WS by clicking on this link.

The macro example assumes that your VBA project has added a reference to the ADO object library.
You can do this from within the VBE by selecting the menu Tools, References and selecting Microsoft ActiveX Data Objects x.x Object Library.

Document last updated 2002-04-02 12:46:39 Printerfriendly version

User comments:

Ole P. from Trondheim wrote (2006-09-05 15:52:26 CET):

Re: Import from multiple text files?
Try something like this:

SELECT a.Table1, b.Table1 FROM TextFile1.txt a, TextFile2.txt b Where a.Table2 = b.Table2

Erlend from Norway wrote (2006-09-05 14:20:50 CET):

Import from multiple text files?
Can anyone give an example of how to import from multiple text files-

That is I would like to do something like this (Both tables contain a field Table1):

SELECT TextFile1.Table1,TextFile2.Table1 FROM TextFile1.txt, TextFile2.txt Where TextFile1.Table2 = TextFile2.Table2

But this creates an error..

Ole P. from Norway wrote (2005-12-06 11:38:30 CET):

Re: How about finding just one row of a text file?
Your example refers to an Access database, not a text file.

You can populate the necessary query string like this:

Sub Test()
Dim r As Long, lrn As Long
Dim strItems As String, strItem As String, strSQL As String
' find last used cell
lrn = Range("A" & .Rows.Count).End(xlUp).Row
' populate query filter
For r = 15 To lrn
strItem = Trim(Range("B" & r).Text)
If Len(strItem) > 0 Then
strItems = strItems & "'" & strItem & "', "
End If
Next r
If Len(strItems) > 0 Then
strItems = Left$(strItems, Len(strItems) - 2)
strSQL = "select * from TableName where FieldName in (" & strItems & ")"
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;DBQ=C:\Folder\FileName.mdb;DefaultDir=C:\Folder;DriverId=25;FIL=MS Access;MaxBufferSize=20" _
), Array("48;PageTimeout=5;")), Destination:=Range("D1"))
.CommandText = strSQL
.Name = "QueryName"
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = True
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = False
.RefreshPeriod = 0
.PreserveColumnInfo = False
.Refresh BackgroundQuery:=False
End With
End If
End Sub

Andy from Detroit, MI wrote (2005-12-06 01:24:53 CET):

Re:Re: How about finding just one row of a text file?
Ok, I've tried that when I recorded a macro, however, I'm not able to substitute the values for an object string. For example:
[snip]

Another concern with the loop is that it would repeatedly open a connection -- would definetely be slow. An more efficient way would be to gather all of the PNs -- sort of like making a listbox -- open the connection, gather all of the recordsets, then loop thru the spreadsheet. I think I can figure this out eventually, but I'm stuck on the variables 'xPN' & 'thisPN', as well as constructing an array of multiples, to use your example ('0001324', '0001322', '0005555', '0007777', '0009999').

Ole P. from Norway wrote (2005-12-05 10:06:07 CET):

Re: How about finding just one row of a text file?
If you just want to return one row from your query you have to add a distinct filter-criteria, e.g. like this:
"SELECT * FROM filename.txt WHERE PartNumber = '0001324'"

If you want to query multiple part numbers and return multiple rows in one single query, it can be done like this:
"SELECT * FROM filename.txt WHERE PartNumber IN ('0001324', '0001322', '0005555', '0007777', '0009999')"

Andy from Detroit, MI wrote (2005-12-05 01:37:55 CET):

How about finding just one row of a text file?
I'm trying to figure out the following:

1) Take the Lookup Value in Cell B2, i.e. " 0001324 "

2) Find the match in a text file with PSV laid out thusly over 1.8 million lines-- PartNumber may, or may not, be in ascending order :
PartNumber|ItemName|SoS|Unit|Price|Core
9999999|Fluid, Brake,Silicon|E|GAL|$56.00|N
0000001|Gadget,Widget|A|DZ|$99.99|N
0001324|Starter,..Pre-Lubed|A|EA|$1500.00|Y*
0001322|Starter,Standard|B|EA|$3570.00|Y

[snip]

TIA

Andy

Ole P. from Norway wrote (2004-12-17 20:44:50 CET):

Re: Couldn't get ADO to read very large text file (200,000+ records)
This new example shows how you can read data from a large text file (1 000 000 records) using ADO and put the contents into separate worksheets (e.g. 100 worksheets with 10 000 records each).

Peter Mroz from Princeton, NJ wrote (2004-12-17 17:37:54 CET):

Couldn't get ADO to read very large text file (200,000+ records)
Thanks for the above information - I used it as a starting point for a program that reads a tab-delimited file in and splits it into separate worksheets. The program worked great for smaller files (42,000 records), but was not able to open a 200,000 record file. I know that Excel has a 65536-row limit, but what I was trying to do was determine the number of distinct values of a particular column (EVAL CRITERIA), and then split the file up into separate worksheets based on that column.

Here's the code I wrote, which shows how to issue a SELECT COUNT(*) from a tab delimited text file. This macro works for smaller files, but not for a 200,000+ record file.

[snip, snip, snip, removed large code example]

zstringer999@yahoo.com