 |
Database »
Database »
General
Intermediate
License: The Code Project Open License (CPOL)
Read text file (txt, csv, log, tab, fixed length)By Pradeep KVThis article is mainly focused on reading text files efficiently. It includes log, csv, tab delimited, fixed length files, etc. Instead of using StreamReader(.NET)/FileSystemObject(VB 6.0), we consider the file as a database table and read the data by querying it. |
C#, VB (VB 6), .NET, SQL Server, Dev
|
||||||||
|
Advanced Search Add to IE Search |
|
|
|
||||||||||||||||
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 that 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
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.
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.
Steps to read the data:
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 uploaded in the database. Let us see the advantages with this approach
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.
| 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.
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.
Example:
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.
General 
News 
Question 
Answer 
Joke 
Rant 
Admin
|
PermaLink |
Privacy |
Terms of Use
Last Updated: 9 Jun 2008 Editor: |
Copyright 2008 by Pradeep KV Everything else Copyright © CodeProject, 1999-2009 Web21 | Advertise on the Code Project |
\"");
