Read Text File (txt, csv, log, tab, fixed length)

• Download .NET code - 1.78 KB
• Download VB code - 788.44 KB

Introduction

We come across many situations where we need to read the data and process it from the text files (.txt, .CSV, .tab). The most common way is to use the StreamReader (.NET) / FileSystemObject (VB 6.0) and read the file line by line.

Suppose we are able to read the file as a database table and process the data by querying the table, we find many disadvantages with the above approach.

Some of the disadvantages are:

1. Connected environment. Locks the file until the process is complete.
2. We have to split each line separately to get individual columns of data. It is difficult to handle the rows which have comma as part of its data.
   Example: “Yes, comma is here”,2,”Hello, another comma”,4,5,6
3. We do not have the option to filter the data until we read the line and start processing it.
4. Counting the number of records in the file or counting the number of records of a particular type involves reading the whole file.

We can list out many such disadvantages. All the advantages of using the database table querying can be counted for the disadvantages of reading line by line.

Now, can we read the text file as a database table?
We wouldn't have discussed this topic if the answer is no. YES, we can read the file as a database table and we will overcome all the disadvantages mentioned above easily.

Read the Data

It is as simple as connecting to a database and querying the data from the database table. In this case, we consider the text file as table and the containing folder as database.
The steps to read the data are as follows:

1. Open a database connection
   Note: Connection string is very important here. It varies depending on the type of the file we are trying to read. We will discuss this later in this article.
2. Get the result set using the basic query language.
3. Loop through the records and read the fields.

C# Code

DataSet myData = new DataSet();
string myXML;
string strFilePath = "C:\\";
string mySelectQuery = "SELECT * FROM SampleFile.CSV";
OleDbConnection myConnection = new OleDbConnection
		("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;" + 
                   "Extended Properties=\"text;HDR=YES;FMT=Delimited\"");
OleDbDataAdapter dsCmd = new OleDbDataAdapter(mySelectQuery, myConnection);
//Fill the DataSet object
dsCmd.Fill(myData, "CustomerOwners");
//Create a XML document with the table data
myData.WriteXml("D:\\TestXML.xml");
myConnection.Close();

VB 6.0 Code

'Set the database connection
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" & strFilePath & ";" & _
                   "Extended Properties=""text;HDR=YES;FMT=Delimited"""

'Query from the file assuming it as a database table
objRecordset.Open "SELECT * FROM " & fileName, _
          objConnection, adOpenStatic, adLockOptimistic, adCmdText

'Loop through the records and insert in to the table
Do Until objRecordset.EOF
    Debug.Print objRecordset.Fields.Item("Number")
    Debug.Print objRecordset.Fields.Item("Name")
objRecordset.MoveNext
Loop

We read the text file as a database table and upload it in the database. Let us see the advantages with this approach:

1. This process is fast.
2. The first thing that we need to observe is the way in which it handles the data row and splits the data. Suppose you have a comma as part of text in one of the columns (point 2 of disadvantages listed above), this process automatically handles this case. We need not handle it separately.
3. We can select specific set of rows from the file if we give selection criteria
   Example: "SELECT * FROM SampleFile1.CSV WHERE Number >= 3"
4. By this time, it can be clearly understood that we can have WHERE, HAVING or GROUP BY clauses in the queries which can be very helpful at times.
   Ex: "SELECT Number, Count(Name) FROM SampleFile1.CSV GROUP BY Number HAVING Count(Name) >= 1"
5. We can filter the data based on the requirements once we read the whole data in the file. This can be done using the RowFilter/Filter property or DataView/RecordSet. As I said, once we read the data in to the Dataset/RecordSet, we can do all the operations that can be done with them. This includes filtering, sorting, etc.

Other File Formats

We discussed about reading a comma separated values (CSV) file. How does this approach handle the tab delimited files and fixed length files?
Everything lies in the connection string that we provide. Let us see the details of the connection string here.

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\FolderName;
Extended Properties="text;HDR=YES;FMT=Delimited

Provider – This represents the type of database. We use the OLEDB connection type.
Data Source – Folder is considered as database.
Extended Properties – These properties will define the way we want to read the file.

• The first part of it defines the file type. In our case, though the file format can be classified as comma delimited, tab delimited or fixed length data, it is all simple text. If we are reading an Excel file, we use Excel x.x, where x.x is version number.

• HDR (Header) – Used to specify whether the header is available or not. YES – First line of the input file is considered as header and the remaining lines are considered as data. NO – Considered as data from the first line.

• FMT (Format) – Specify the formatting type. It can have the following values:

  Delimited	File is considered as a comma delimited file. Comma is the default delimited character.
  Delimited(x)	File is considered as a delimited file with delimited character ‘x’.
  TabDelimited	File is considered as a tab delimited file.
  FixedLength	Reads the data with the fixed length of the field specified. You have to specify the widths and types of the columns using properties like Col1, Col2, etc. More at MSDN.

If the format specified is 'Delimited', the default character is comma (,). It is stored in the registry. You can change it in the registry at HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\Format.
As everybody says, do not mess up with the registry. Microsoft provided an alternative to this by providing us the Schema.ini file. If we are reading a FixedLength file, we must use the Schema.ini file. We specify the length of the fields in the schema file.

Reading from Multiple Files

If you have multiple files and data needs to be merged or filtered based on the common columns, we do the same way as we do in SQL. We can join tables and get the merged data. Remember that the output will be CROSS JOIN of the rows in both the files. Make sure you filter the data based on the common columns.

Examples:

SampleFile1.CSV – (EmpID, Name, Address)
SampleFile2.CSV – (EmpID, Salary, Month)

//Where clause is used to get ‘Natural Join’
string mySelectQuery = "SELECT * FROM SampleFile.CSV As Sample1, _
			SampleFile2.CSV As Sample2 " + _
                       	"Where Sample1.Number=Sample2.Number";

'Where clause is used to get ‘Natural Join’
objRecordset.Open "SELECT * FROM SampleFile.CSV As Sample1, _
		SampleFile2.CSV As Sample2 " & _ 
                  "Where Sample1.Number=Sample2.Number", _
                  objConnection, adOpenStatic, adLockOptimistic, adCmdText

Note that aliasing (SampleFile.CSV As Sample1) is required in join queries, especially when tables have columns with same names. In our case, table names (filenames) have dot (.) in between. So the query parser will be misguided by the dot in the table and may consider ‘CSV.Number’ as column name.

History

• 9^th June, 2008: Initial post