Querying Text/CSV files in VBA, method #3: Power Query

The module PowerQueryText.bas contains the code to query delimited files and return tables.

The advantages of PowerQuery are:

1. It can handle comma and tab separated values and does not need a schema.ini as for ADO. You do however need to specify the separator character, see below for the details.
2. Tab-delimited files do not have to have the extension “.tab” as they do for ADO, but can be named with a “.tsv” extension if that’s what you have.
3. It can handle line breaks within quotes, or not, depending on the option you set. I always include line breaks within quotes.
4. Its default encoding is 65001 which can handle all text encoding I have tested it with.  The UTF-16 (UCS-2) encoding is 1200.

In the ADO module, I determined the field separator by reading the file contents as bytes, and use a function CountColumnsFromBytes to infer the number of columns and their separator. In Powerquery, there is a function Binary.InferContentType that will offer statistics on possible delimiters, and your code can choose the most likely one.

At the moment, I have to send the output to a worksheet. If anyone finds a way to return the table data as a memory structure in VBA such as a recordset, let me know.

The functions contained in PowerQueryText.bas are:

testPQcsv    – a simple test stub that prompts for a file name and queries it. It calls the following functions.

InferContentType – needed to infer the encoding and field delimiter (separator) character.

TableProfile – returns statistics about a file to help determine the number of rows and columns.

PQMCsvFormula – a generic function to return the M expression for a delimited file.

PQDelimitedText – runs a query and outputs the result.

WBQueryToSheet – sends the data from a query to a sheet table range.

WBQueryToListTable – sends the data from a query to an Excel ListTable.

That is the end of this series on reading delimited text files in Excel VBA. I hope you find it useful, and let me know of any bug fixes needed, or suggest any improvements.