Text Files are supported with the text source database type as shown in the following
connection string:
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+System.Environment.CurrentDirectory + "\\;Extended Properties=\"text;HDR=yes;FMT=Delimited\"";
Notice that only the folder where the text file resides is specified. The filename
of the text file is specified in the SQL commands that access data in the file,
similar to referencing a table name in a query.
The Extended Properties attribute can also specify whether tables include headers
for field names in the first row using the HDR attribute.
It is not possible to define all the characteristics of a text file in the connection
string, however. You can access files that use nonstandard delimiters and fixed-width
lines by creating a Schema.ini text file that must reside in the same folder
as the text file database. A sample schema for the "quotes.txt" file
of famous quotations that is included in the downloadable sample is shown below:
[quotes.txt]
Format=Delimited(|)
ColNameHeader=True
MaxScanRows=0
Character=OEM
The above is saved as "Schema.ini" next to the quotes.txt text file database.
The Schema.ini file provides the 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 file name surrounded by square
brackets.
The format specifier can be one of the following:
Format=CSVDelimited - Fields are delimited with commas. This is the default value.
Format=Delimited(Custom Character Here) - You can use any single character except the double quotation mark as a delimiter.
In my sample file, I use the Pipe (|) symbol.
Format=FixedLength - If the ColumnName header option is true, the first line with the column names
must be comma-delimited.
Format=TabDelimited - Fields are delimited with Tabs.
You can specify your fields in the text file in one of two ways:
1. Include the field names in the first row of the text file and set the ColNameHeader
option to True.
2. Identify each column using the format ColN (where N is the 1-based column number)
and specify the name, width, and data type of each column.
The MaxScanRows option indicates how many rows should be scanned to automatically determine the
datatype of a column. A value of 0 indicates all rows should be scanned.
The ColN entries specify the name, width and datatype for each column. This entry is required
for fixed-length formats and optional for character-delimited formats.
The syntax of the ColN entry is:
Col1=ID Short Width 4
Col2=FirstName Text Width 100
The datatype can be any of Bit, Byte, Currency, DateTime, Double, Long, Memo, Short, Single or
Text.
The Character option specifies the character set and can be set to either ANSI or OEM.
Once this is all set up, you can issue select, update, delete and insert statments
exactly the way you would with a "regular" database.
The code from my sample:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
namespace TextFileADONET
{
class Program
{
static void Main(string[] args)
{
string connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +System.Environment.CurrentDirectory + "\\;Extended Properties=\"text;HDR=yes;FMT=Delimited\"";
OleDbDataAdapter da = new OleDbDataAdapter("select * from [quotes.txt]", connectionString);
DataTable dt = new DataTable();
da.Fill(dt);
foreach(DataRow row in dt.Rows)
Console.WriteLine((string)row["ID"] + ": " + (string) row["LastName"] + (string) row["quotation"]);
Console.WriteLine("---SELECT * FROM [quotes.txt] WHERE LastName='Einstein'---------");
OleDbDataAdapter da2 = new OleDbDataAdapter("select TOP 1 * from [quotes.txt] WHERE LastName='Einstein'", connectionString);
DataTable dt2 = new DataTable();
da2.Fill(dt2);
foreach (DataRow row in dt2.Rows)
Console.WriteLine((string)row["ID"] + ": " + (string)row["LastName"] + " " + (string)row["quotation"]);
Console.WriteLine("Any key to quit.");
Console.ReadLine();
}
}
}
You can download the sample code here.