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:
- Connected environment. Locks the file until the process is complete.
- 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
- We do not have the option to filter the data until we read the line and start processing it.
- 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:
- 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.
- Get the result set using the basic query language.
- Loop through the records and read the fields.
DataSet myData = new DataSet();
string strFilePath = "C:\\";
string mySelectQuery = "SELECT * FROM SampleFile.CSV";
OleDbConnection myConnection = new OleDbConnection
("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\\;" +
OleDbDataAdapter dsCmd = new OleDbDataAdapter(mySelectQuery, myConnection);
VB 6.0 Code
objConnection.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strFilePath & ";" & _
objRecordset.Open "SELECT * FROM " & fileName, _
objConnection, adOpenStatic, adLockOptimistic, adCmdText
Do Until objRecordset.EOF
We read the text file as a database table and upload it in the database. Let us see the advantages with this approach:
- This process is fast.
- 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.
- We can select specific set of rows from the file if we give selection criteria
SELECT * FROM SampleFile1.CSV WHERE Number >= 3"
- By this time, it can be clearly understood that we can have
GROUP BY clauses in the queries which can be very helpful at times.
SELECT Number, Count(Name) FROM SampleFile1.CSV GROUP BY Number HAVING Count(Name) >= 1"
- We can filter the data based on the requirements once we read the whole data in the file. This can be done using the
Filter property or
RecordSet. As I said, once we read the data in to the
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 – 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:
|File is considered as a comma delimited file. Comma is the default delimited character.
|File is considered as a delimited file with delimited character ‘x’.
|File is considered as a tab delimited file.
|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
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.
SampleFile1.CSV – (
SampleFile2.CSV – (
string mySelectQuery = "SELECT * FROM SampleFile.CSV As Sample1, _
SampleFile2.CSV As Sample2 " + _
'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.
- 9th June, 2008: Initial post