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.
Hi Patrick. The link to the .bas file doesn’t work for me.
Thanks Ian! Fixed.
Pingback: Power Query infer data types with Table.Profile | Patrick O'Beirne @ sysmod