Recipe 1.19 Connecting to a Text File

Problem

You want to use ADO.NET to access data stored in a text file.

Solution

Use the OLE DB Jet provider to access data in a text file.

The sample code creates an OleDbDataAdapter that uses the Jet OLE DB provider to load the contents of the text file Categories.txt, shown in Example 1-13, into a DataTable and displays the contents in a data grid on the form.

Example 1-13. File: Categories.txt

"CategoryID","CategoryName","Description"
1,"Beverages","Soft drinks, coffees, teas, beers, and ales"
2,"Condiments","Sweet and savory sauces, relishes, spreads, and seasonings"
3,"Confections","Desserts, candies, and sweet breads"
4,"Dairy Products","Cheeses"
5,"Grains/Cereals","Breads, crackers, pasta, and cereal"
6,"Meat/Poultry","Prepared meats"
7,"Produce","Dried fruit and bean curd"
8,"Seafood","Seaweed and fish"

The C# code is shown in Example 1-14.

Example 1-14. File: ConnectTextFileForm.cs

// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;

//  . . . 

// Create the data adapter to retrieve all rows from text file.
OleDbDataAdapter da =
    new OleDbDataAdapter("SELECT * FROM [Categories.txt]",
    ConfigurationSettings.AppSettings["TextFile_0119_ConnectString"]);

// Create and fill the table.
DataTable dt = new DataTable("Categories");
da.Fill(dt);

// Bind the default view of the table to the grid.
categoriesDataGrid.DataSource = dt.DefaultView;

Discussion

The Jet OLE DB provider can read records from and insert records into a text file data source. The Jet database engine can access other database file formats through Indexed Sequential Access Method (ISAM) drivers specified in the Extended Properties attribute of the connection. Text files are supported with the text source database type as shown in the following example:

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\MyTextFileDirectory;
    Extended Properties="text;HDR=yes;FMT=Delimited";

The Extended Properties attribute can, in addition to the ISAM version property, specify whether or not tables include headers as field names in the first row of a range using an HDR attribute.

It is not possible to define all characteristics of a text file through the connection string. You can access files that use non-standard text delimiters and fixed-width text files by creating a schema.ini file in the same directory as the text file. As an example, a possible schema.ini file for the Categories.txt file used in this solution is:

[Categories.txt]
Format=CSVDelimited
ColNameHeader=True
MaxScanRows=0
Character=OEM
Col1=CategoryID Long Width 4 
Col2=CategoryName Text Width 15
Col3=Description Text Width 100

The schema.ini file provides the following schema information about the data in the text file:

• Filename

• File format

• Field names, widths, and data types

• Character set

• Special data type conversions

The first entry in the schema.ini file is the text filename enclosed in square brackets. For example:

[Categories.txt]

The Format option specifies the text file format. Table 1-8 describes the different options.

Table 1-8. Schema.ini format options

Format	Description
CSV Delimited	Fields are delimited with commas: Format=CSVDelimited This is the default value.
Custom Delimited	Fields are delimited with a custom character. You can use any single character except the double quotation mark (") as a delimiter: Format=Delimited(customCharacter)
Fixed Length	Fields are fixed length: Format=FixedLength If the ColumnNameHeader option is True, the first line containing the column names must be comma-delimited.
Tab Delimited	Fields are delimited with tabs: Format=TabDelimited

You can specify the fields in the text file in two ways:

• Include the field names in the first row of the text file and set the ColNameHeader option to True.

• Identify each column using the format ColN (where N is the one-based column number) and specify the name, width, and data type for each column.

The MaxScanRows option indicates how many rows should be scanned to automatically determine column type. A value of 0 indicates that all rows should be scanned.

The ColN entries specify the name, width, and data type for each column. This entry is required for fixed-length formats and optional for character-delimited formats. The syntax of the ColN entry is:

ColN=columnName dataType [Width n]

The parameters in the entry are:

columnName

The name of the column. If the column name contains spaces, it must be enclosed in double quotation marks.

dataType

The data type of the column. This value can be Bit, Byte, Currency, DateTime, Double, Long, Memo, Short, Single, or Text.

DateTime values must be in one of the following formats: dd-mmm-yy, mm-dd-yy, mmm-dd-yy, yyyy-mm-dd, or yyyy-mmm-dd, where mm is the month number and mmm are the characters specifying the month.

Width n

The literal value Width followed by the integer value specifying the column width.

The Character option specifies the character set; you can set it to either ANSI or OEM.