Managed Extensions: Using the .NET ODBC Classes to Read Text Data
Taking Control of the Process with the schema.ini FileOnce you've started working with text files via a DSN-less connection, you might run into situations that will have you asking things like "How do I specify how the file is delimited (e.g., tab vs. comma)?" or "Where can I specify the character set?" These settings and more can be specified via a very simple file named schema.ini that resides in the same directory as the data file. The schema.ini file is documented on the Microsoft Web site so I won't attempt to cover every possible parameter that can be specified. However, I will cover the most popular question I see on the Internet: how to specify if the data includes (as its first row) the column names of the data.
By default, the text driver assumes that the data contains a column heading row. Therefore, if your data does not contain this row and you do not define a schema.ini file, you will find that the first row of data being ignored. For example, if your data looked like the following, the reader code above would display only the second and third records (leaving out your favorite author!):
Tom Archer,Archer Consulting Group Bradley Jones,Jupitermedia Bill Gates,MicrosoftIn order to specify that the data does not include a column row and that you don't wish to name the columns, your schema.ini file would look like the following:
[data.txt] ColNameHeader=FALSEIn terms of specifying the column names for your data, you have two choice:
- You can include—as the first row in the text file—the column names and then specify the ColNameHeader attribute in the schema.ini file. (You can also omit the schema.ini file, as the text driver defaults the ColNameHeader value to TRUE).
Name,Company Tom Archer,Archer Consulting Group Bradley Jones,Jupitermedia Bill Gates,Microsoftschema.ini file
- If the data doesn't include a column row, you can manually set the column names such that the two files look as follows:
Tom Archer,Archer Consulting Group Bradley Jones,Jupitermedia Bill Gates,Microsoftschema.ini file
[data.txt] ColNameHeader=FALSE Col1=Name Char Width 255 Col2=Company Char Width 255
Download the Code
To download the accompanying source code for this article, click here.
About the Author
The founder of the Archer Consulting Group (ACG), Tom Archer has been the project lead on three award-winning applications and is a best-selling author of 10 programming books as well as countless magazine and online articles.
Page 2 of 2