Querying Text/CSV files. Method #2: ADO Recordset

The module ADOQueryText.bas has the functions to read a delimited text file into a worksheet. It uses an ADODB recordset to perform the query. The pros and cons of ADO are:

1. It can handle embedded newlines in data, unlike a Querytable. These are common in CSV files from sources (eg surveys) where users enter free text with multiple lines, and the text is returned in one field.
2. It cannot open Unicode file names, so to be safe you must copy the original file to a temporary copy with an ANSI filename.
3. It requires a schema.ini file to recognise when the fields are delimited by tabs. To avoid this, you could use a function ReplaceBytes() in the QueryTables module from the previous blog post to change all tab delimiters with commas.

To avoid some of these complications, the final post in this series will be on how to use Power Query in VBA to query a text / csv file.

The functions are:

ADOLoadWSFromDelimitedTextFile(ByVal OriginalFullname As String, ByRef wsData As Worksheet, Optional ByRef Delimiter As String, Optional ByRef CodePage As Long)

              Queries a structured text file into a table in a sheet, returns delimiter and codepage by reference. It uses the two following functions, writing a schema.ini file if the data source is tab-delimited and checks whether there are enough rows on the sheet for the records.

ADORecordsetFromTextfile(ByVal FullName As String, ByRef rs As ADODB.Recordset,  ByVal FMT As String, ByVal CodePage As Long, Optional ByVal strSelect As String)

              Queries a structured text file into an ADO recordset, returning the format and codepage by reference.  strSelect is the list of fieldnames or expressions in SQL syntax, the default is “*”, all fields.

CopyRecordsetToSheet(rs As ADODB.Recordset, Optional ByRef wsTarget As Worksheet, Optional ByVal RecordSource As String)

              Writes a recordset to a worksheet, adding the sheet and writing headers if required.