Fixed Length text file to Datatable

  • Article

*Source code can be found at  Fixed Length text file to Datatable
*
After working so hard to work in an application to read huge Fixed Length Text Files I found some information about a tricky to do it defining an INI file with specifications which will be used to read file into datatable faster than read file line by line.

I have to mention that I have no access to SSIS, Biztalk, Talend, Apatar or other ETL tool, that was the reason to create this little application.

Basically we only need to create a file called Schema.ini on the same directory where file to be read resides and, also, it must define structure in the next way:

[file_name_to_be_read]
**ColNameHeader=**true if first line has the name of columns, otherwise, false.
Format=FixedLength
**DateTimeFormat=**date format on columns
**Col1=**name_of_column type Width width of column
*Col2=*name_of_column type Width ***width of column
***Col3=name_of_column type Width ***width of column
***Col4=name_of_column type Width ***width of column
*
A live example of this is:
[fixedLengthTextFile.txt]
ColNameHeader=false
Format=FixedLength
DateTimeFormat=yyyymmdd
Col1=ID Integer Width 5
Col2=Name Text Width 25
Col3=Phone Text Width 10
Col4=DateTimeStamp DateTime Width 8

The last part is just to read file using OLEDB with this code:

// Declare Variables and Objects
string file = "fixedLengthTextFile.txt"; 
string dir = Path.GetDirectoryName(Application.ExecutablePath); 
DataTable dt;

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

    // Create OleDb Adapter object 
    using (OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT * FROM " + file, cn)) 
    { 
        dt = new DataTable("Records"); 
        adapter.Fill(dt); 

// Display results
datagridview1.DataSource = dt;
    } 
}

Now, you can use data in a datatable to display

Example is based on this article:
Schema.ini File (Text File Driver)
Check article to see more options for Schema.ini file