Read Data from Delimited File using JET OLEDB and with out using JET OLEDB

Summary

This article discusses how to retrieve the data from Text Delimited file using Microsoft JET OLE DB driver / Provider's Text IISAM Driver. To study briefly about delimited files refer to the link http://en.wikipedia.org/wiki/Delimiter-separated_values




How to Read the data from the Delimited File?


Before starting to read the file we should know how to read the file. What are the various ways to get the delimited file information. 
The process is very simple process which is similar as executing the query from the application coding. The file existing folder is considered as a Database by the JET provider. The file will be treated as a Table. Other than the regular process we should know about the connection string other than this regular process (i.e, Open Database Connection, Read / Fetch the data by executing the query, Store the information into local memory as Data Table or Data Set).

Connection String Information

The following are the various connection string providers are available for reading the Delimited file data. 


OleDb Providers:
1. Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\txtFilesFolder\;Extended Properties="text;HDR=Yes;FMT=Delimited";

"HDR=Yes;" indicates that the first row contains columnnames, not data. "HDR=No;" indicates the opposite.

ODBC Providers:

1. Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq=c:\txtFilesFolder\;Extensions=asc,csv,tab,txt;

Specify the Delimiter Information


The delimiter can be specified in the registry at the following location:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
"Format" = "TabDelimited" or "Format" = "Delimited(;)"
*The delimited character is comma(,).






Read data from the file from C#


DataSet myReadData = new DataSet();
string myQuery = "select * from sampletestfile1.txt";
OleDbConnection myDBConnection = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\TestFolder;Extended Properties="text;HDR=Yes;FMT=Delimited"");
OleDbDataAdapter myDBAdapter = new OleDbDataAdapter(myQuery, myDBConnection);
myDBConnection.Open();
myDBAdapter.Fill(myReadData, "DelimitedTable");
myDBAdapter.Dispose();
myDBConnection.Close();


This will fetch the data from the delimited text file into data table. 






Read Data from the file through VB





Dim myReadData As DataSet = New DataSet()
Dim myQuery As String = "select * from sampletestfile1.txt"
Dim myDBConnection As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\TestFolder;Extended Properties=" + Chr(34) + "text;HDR=Yes;FMT=Delimited" + Chr(34))
Dim myDBAdapter As OleDbDataAdapter = New OleDbDataAdapter(myQuery, myDBConnection)
myDBConnection.Open()
myDBAdapter.Fill(myReadData, "DelimitedTable")
myDBAdapter.Dispose()
myDBConnection.Close()



Read data from the file with out using Microsoft JET in VB.Net

Other than the Microsoft JET provider, Microsoft is also provided a class to read / parse the Delimited and Fixed-Length files which is named as "TextFieldParser". This class is available under Microsoft.VisualBasic.FileIO namespace which is new in the .Net Framework Version 2.0. The TextFieldParser class provides a way to easily and efficiently parse structured text files. The following example is used to parse a comma delimited file.

        Using MyReader As New Microsoft.VisualBasic.FileIO.TextFieldParser("C:\TestFolder\test.txt")
            MyReader.TextFieldType = FileIO.FieldType.Delimited
            MyReader.SetDelimiters(",")
            MyReader.CommentTokens() = New String() {"//"}
            MyReader.HasFieldsEnclosedInQuotes = True
            MyReader.TrimWhiteSpace = True


            Dim currentRow As String()
            While Not MyReader.EndOfData
                Try
                    currentRow = MyReader.ReadFields()
                    Dim currentField As String
                    For Each currentField In currentRow
                        MsgBox(currentField)
                    Next
                Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException
                    MsgBox("Line " & ex.Message & "is not valid and will be skipped.")
                End Try
            End While
        End Using





The following conditions may cause an exception with this code:

• A row cannot be parsed using the specified format (MalformedLineException). The exception message specifies the line causing the exception, while the ErrorLine property is assigned the text contained in the line.
  
• The specified file does not exist (FileNotFoundException).
  
• A partial-trust situation in which the user does not have sufficient permissions to access the file. (SecurityException).
  
• The path is too long (PathTooLongException).
  
• The user does not have sufficient permissions to access the file (UnauthorizedAccessException).