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;
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
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 toTrue
.Identify each column using the format
Col
N
(whereN
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 Col
N
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
Col
N
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
, orText
.DateTime
values must be in one of the following formats:dd-mmm-yy
,mm-dd-yy
,mmm-dd-yy
,yyyy-mm-dd
, oryyyy-mmm-dd
, wheremm
is the month number andmmm
are the characters specifying the month.Width
nThe 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
.
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.