Deborah's Developer MindScape : Data, text file

Reading Fixed Length Files

Deborah Kurata — Tue, 25 Aug 2009 20:34:15 GMT

There may be times that you need to read fixed length files into your application. For example, you obtain output from a legacy system or other application in a fixed length text file format, and you need to read and use that data in your application.

NOTE: For more information on fixed length files, see this link.

.NET provides several techniques for reading text files. This post focuses on how to read a fixed length text file into a DataTable.

You may find it very useful to read your text file into a DataTable, whether or not you plan to use a database. Reading a text file into a DataTable not only saves you a significant amount of string manipulation coding, it also makes it easy to access the imported data from within your application.

For example, you can use binding to bind the resulting DataTable to a grid or other controls. You can use Linq to DataTables like in this example to manipulate the resulting data. All of the features of the DataTable are then available to you.

BIG NOTE: Many developers have ignored this technique because one look at the code and the developer assumed it is somehow associated with a database, it is NOT. This is referring to in-memory DataTable objects.

For this example, the text file appears as follows:

000001 Baggins             Bilbo     20090811
000002 Baggins             Frodo     20090801
000003 Gamgee              Samwise   20090820
000004 Cotton              Rosie     20090821

Notice several things about this file:

The columns are a fixed width.
There is no header row that provides the column names. You could add column headers here if desired.

The first step in reading the file is to define a schema.ini file that defines the column widths. The file must follow these specifications:

The file must be called schema.ini.
The file must exist in the same directory as the text file.
The file must be in ANSI format. (See the note at the bottom of this post for information on saving a file to ANSI format.)

The contents of the schema.ini file for the example above is shown below:

[testFixed.txt]
ColNameHeader=False
Format=FixedLength
DateTimeFormat=yyyymmdd
Col1=CustomerId Text Width 6
Col2=LastName Text Width 22
Col3=FirstName Text Width 10
Col4=LastUpdateDate DateTime Width 8

The first line of the file is always the name of the associated text file enclosed in square brackets ([ ]).

The next set of lines define basic attributes of the text file:

ColNameHeader: In this case, there is no column header in the text file, so this property is set to false. The system will assume that the first line of the text file is the header unless you specify otherwise.
Format: In this case, the format is FixedLength. The system will assume comma delimited unless you specify otherwise.
DateTimeFormat: If you have a date in your file, you can specify the format here.

The last set of lines defines each column in the text file. The format of these lines are as follows:

Colx=ColumnName ColumnType Width ColumnWidth

See this link for more information on the contents of the schema.ini file.

You can then read the file using the following code.

In C#:

string fileName = "testFixed.txt";
string dirName = Path.GetDirectoryName(Application.ExecutablePath);
DataTable dt;

using (OleDbConnection cn =
    new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;" +
            "Data Source=" + dirName + ";" +
            "Extended Properties=\"Text;\""))
{
    // Open the connection
    cn.Open();

    // Set up the adapter
    using (OleDbDataAdapter adapter =
        new OleDbDataAdapter("SELECT * FROM " + fileName, cn))
    {
        dt = new DataTable("Customer");
        adapter.Fill(dt);
    }
}

In VB:

Dim fileName As String = "testCSV.txt"
Dim dirName As String = _
Path.GetDirectoryName(Application.ExecutablePath)
Dim dt As DataTable

Using cn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" & _
            "Data Source=" & dirName & ";" & _
            "Extended Properties=""Text;""")
    ' Open the connection
    cn.Open()

    ' Set up the adapter
    Using adapter As New OleDbDataAdapter( _
            "SELECT * FROM " & fileName, cn)
        dt = New DataTable("Customer")
        adapter.Fill(dt)
    End Using
End Using

This code starts by declaring variables to hold the text file name, directory containing the file and the resulting DataTable.

This technique only works with a standard set of file name extensions (see the NOTE at the end of this post). The file can reside in any directory. In this example, the file resides in the same directory where the application is executed. But this is not a requirement.

The first using statement in the example code sets up the connection string for connecting to the directory. It sets the Provider property to use the Microsoft.Jet.OleDb provider. The Data Source property defines the directory containing the text file. The Extended Properties define that the file will be Text ("Text"). The Extended Properties must be within quotes, so double-quotes (VB) or slash quote (C#) are used to escape the quotes.

If a schema.ini file exists in the directory defined as the data source and has a bracketed entry with the text file name, that .ini file is used to determine any other extended properties. So no other extended properties are defined in the connection string itself.

The code then opens the connection, thereby opening the file and the associated schema.ini file. Since this code is in a using statement, the files are automatically closed at the end of the using block.

The second using statement sets of the DataAdapter by defining a Select statement and the open connection. The Select statement selects all of the information from a specific file as defined by the fileName variable.

The code then creates the DataTable, giving the table a name. In this example, the table name is "Customer".

Finally, it uses the Fill method of the TableAdapter to read the data from the text file into the DataTable.

Using the technique detailed here, you can view the resulting DataTable. The column headings were defined by the header in the text file. If you don't have a header, the columns will be giving a default name.

Note how the date in the above screen shot appears as a standard date column.

You can then access the data in the table as you access any other DataTable. For example:

In C#:

foreach (DataRow dr in dt.Rows)
{
    Debug.Print("{0}: {1}, {2} LastUpdated: {3}",
                dr["CustomerId"],
                dr["LastName"],
                dr["FirstName"],
                dr["LastUpdateDate"]);

}

In VB:

For Each dr As DataRow In dt.Rows
    Debug.Print("{0}: {1}, {2} LastUpdated: {3}", _
                dr("CustomerId"), _
                dr("LastName"), _
                dr("FirstName"), _
                dr("LastUpdateDate"))
Next

NOTE:

By default, this technique only works with .txt, .csv, .tab, and .asc file extensions. If your file name has a different extension, you can either change the extension in your code before reading the file, or you can update the Extensions key in following registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

NOTE:

By default, this technique assumes you are working with ANSI text files. If that is not the case, you can update the CharacterSet key in the same registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

Though this is not recommended.

VERY IMPORTANT NOTE:

If you test this sample code by creating a text file with Visual Studio, the resulting text file will be in UTF-8 format. You need to save the file into ANSI format. The easiest way I found to do this is detailed below.

Adding a Text File to your Project:

Right-click on your project in Visual Studio.
Select Add | New Item from the context menu.
Pick Text File from the available templates and click Add.
Type in the data for the test file or paste in the text from the example at the top of this post.
Save the file within Visual Studio. This creates a UTF-8 formatted file.
If you plan to use the directory of the executing application, set the Copy to Output Directory to Copy always in the properties window for the file.

Converting the resulting UTF-8 file to ANSI format:

Right-click on the file and select Open With …
Select Notepad.
Select File | Save As.
Set the Encoding to ANSI and click Save.

Enjoy!

Reading Comma Delimited Files

Deborah Kurata — Tue, 25 Aug 2009 19:14:24 GMT

There may be times that you need to read comma separated value (CSV) files into your application. For example, you obtain output from a legacy system or other application in a comma delimited text file format, and you need to read and use that data in your application.

NOTE: For more information on delimited files, see this link.

.NET provides several techniques for reading text files. This post focuses on how to read a comma delimited text file into a DataTable.

For this example, the text file appears as follows:

CustomerId, LastName, FirstName, LastUpdateDate
1, Baggins, Bilbo, 20090811
2, Baggins, Frodo, 20090801
3, Gamgee, Samwise, 20090820
4, Cotton, Rosie, 20090821

Notice several things about this file:

It is a comma separated value (CSV) file.
The first line provides the column names. This is optional.

You can read this text file into a DataTable using OleDb as follows.

In C#:

string fileName = "testCSV.txt";
string dirName = Path.GetDirectoryName(Application.ExecutablePath);
DataTable dt;

using (OleDbConnection cn =
    new OleDbConnection(@"Provider=Microsoft.Jet.OleDb.4.0;" +
            "Data Source=" + dirName + ";" +
            "Extended Properties=\"Text;HDR=Yes;FMT=Delimited\""))
{
    // Open the connection
    cn.Open();

In VB:

Dim fileName As String = "testCSV.txt"
Dim dirName As String = _
Path.GetDirectoryName(Application.ExecutablePath)
Dim dt As DataTable

Using cn As New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;" & _
            "Data Source=" & dirName & ";" & _
            "Extended Properties=""Text;HDR=Yes;FMT=Delimited""")
    ' Open the connection
    cn.Open()

    ' Set up the adapter
    Using adapter As New OleDbDataAdapter( _
            "SELECT * FROM " & fileName, cn)
        dt = New DataTable("Customer")
        adapter.Fill(dt)
    End Using
End Using

This code starts by declaring variables to hold the text file name, directory containing the file and the resulting DataTable.

The first using statement in the example code sets up the connection to the directory. It sets the Provider property to use the Microsoft.Jet.OleDb provider. The Data Source property defines the directory containing the text file. The Extended Properties define that the file will be Text ("Text"), it has a header (HDR=Yes), and it is in a delimited file format (FRM=Delimited). The Extended Properties must be within quotes, so double-quotes (VB) or slash quote (C#) are used to escape the included quotes.

The code then opens the connection, thereby opening the file. Since this code is in a using statement, the file is automatically closed at the end of the using block.

The code then creates the DataTable, giving the table a name. In this example, the table name is "Customer".

Finally, it uses the Fill method of the TableAdapter to read the data from the text file into the DataTable.

You can then access the data in the table as you access any other DataTable. For example:

In C#:

}

In VB:

NOTE:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

NOTE:

By default, this technique assumes you are working with ANSI text files. If that is not the case, you can update the CharacterSet key in the same registry setting:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text

Though this is not recommended.

VERY IMPORTANT NOTE:

Adding a Text File to your Project:

Right-click on your project in Visual Studio.
Select Add | New Item from the context menu.
Pick Text File from the available templates and click Add.
Type in the data for the test file or paste in the text from the example at the top of this post.
Save the file within Visual Studio. This creates a UTF-8 formatted file.
If you plan to use the directory of the executing application, set the Copy to Output Directory to Copy always in the properties window for the file.

Converting the resulting UTF-8 file to ANSI format:

Right-click on the file and select Open With …
Select Notepad.
Select File | Save As.
Set the Encoding to ANSI and click Save.

Enjoy!