The module PowerQueryText.bas contains the code to query delimited files and return tables.
The advantages of PowerQuery are:
- 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.
- 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.
- It can handle line breaks within quotes, or not, depending on the option you set. I always include line breaks within quotes.
- 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.