Query Table from a Text File

You can also use a QueryTable to extract data from a text file. The advantage of using a QueryTable as opposed to the Workbooks.OpenText method is that the text file data can be loaded directly into the workbook you specify, as opposed to being opened in a new workbook.

This example uses the same Sales.csv source file used in the ADO text file example in Chapter 20. The code to load data from this text file using a QueryTable is as follows:

Sub QueryTableFromTextFile()

Dim qryTable As QueryTable Dim rngDestination As Range Dim strConnection As String

' Define the connection string and destination range. strConnection = "TEXT;C:\Files\Sales.csv" Set rngDestination = Sheet6.Range("A1")

' Create the QueryTable.

Set qryTable = Sheet6.QueryTables.Add(strConnection, rngDestination)

' Populate the QueryTable. qryTable.TextFileStartRow = 1 qryTable.TextFileParseType = xlDelimited qryTable.TextFileCommaDelimiter = True qryTable.TextFileTextQualifier = xlTextQualifierNone qryTable.TextFileColumnDataTypes = Array(2, 2, 2, 2, 1)

qryTable.Refresh False End Sub

You inform the QueryTable that it will be extracting data from a text file by specifying TEXT as the first argument in the connection string. The second argument of the connection string is the full path and filename of the text file.

The QueryTable object has a series of text file-specific properties that allow you to control how the text file data is loaded. The following list describes the five of these properties that are most commonly used:

□ TextFileStartRow—This property tells the query table which row of the text file to start with when it loads the data. A text file may have one or more initial rows that are not part of the data set. You can use this property to skip these initial rows by specifying some number greater than 1. A value of 1 tells the QueryTable to import the entire text file.

□ TextFileParseType — This property tells the QueryTable whether the columns of data in the text file are separated by some delimiting character (xlDelimited) or are fixed width (xlFixedWidth). The value of this property will determine which additional properties you specify. Your text file is comma-delimited, so the options for a delimited text file are discussed next. If your text file has fixed-width columns, you would also set the TextFileFixedColumnWidths property to an array of column width values, one for each column in your text file.

□ TextFileCommaDelimiter — When loading a delimited text file, you need to tell the QueryTable what delimiter to look for. You're loading a comma-delimited text file, so you set the TextFileCommaDelimiter property to True. The following additional properties are available to specify other delimiters: TextFileTabDelimiter, TextFileSemicolonDelimiter, TextFileSpaceDelimiter, and TextFileOtherDelimiter.

□ TextFileTextQualifier—You will often encounter text files where values in text data type fields are surrounded by single or double quotes. This property is used to inform the QueryTable if this is the case, and if so, which of these characters it should treat as a qualifier and ignore when loading the data. The options are xlTextQualifierSingleQuote, xlTextQualifierDoubleQuote, and xlTextQualifierNone. You use the last option because your text file does not use any text field qualifiers.

□ TextFileColumnDataTypes —This property is used to tell the QueryTable how to interpret each column of data in the text file. The property takes an array of integer values, one for each column in the text file, that specify what type of data is contained in those columns. For uncomplicated text and numeric data, you can normally pass the value 1 for every column. This tells the QueryTable to automatically determine what type of data is being loaded. If the text file contains data that may be misinterpreted, you can use this property to tell the QueryTable what type of data is contained in each column. A value of 2 means the column contains text data. A value of 9 tells the QueryTable to skip the column entirely. The additional seven allowable numeric values are used to handle various arrangements of date data types. See the VBA help for more details on these.

+1 0


Post a comment