Reading a CSV File in ASP.NET

Recently I came across a programming need of reading a CSV (Comma Separated Values) file in ASP.NET. Thanks to ADO.NET, reading a CSV file in ASP.NET is a breeze. I am writing this post to share my understanding with you.

Reading a CSV File

A comma-separated-value (CSV) file is commonly used for daily programming needs. CSV files are used to import/export data from databases and spreadsheets. These are simple text file (*.txt, *.csv, *.dat) where data values are separated by a comma or some other character. Usually, the first row has the column-names followed by rows of data. Each row has the data in columns separated by some character. Following is a sample (Northing database – Customers table) of what data looks like in a CSV file with the first row having column-names:

CustomerID, CompanyName, ContactName, ContactTitle
ALFKI, Alfreds Futterkiste, Maria Anders, Sales Representative
ANATR, Ana Trujillo Emparedados y helados, Ana Trujillo, Owner

In ASP.NET, we can easily read and get data from a CSV file using ADO.NET. Listing 1 illustrates the code for reading a CSV file:

Listing 1

// filePath: Path where file resides
// filename: File name to be read
private DataTable ReadCSVFile (string filePath, string fileName)
{
    string              connString;
    OleDbConnection     conn;
    OleDbDataAdapter    da;
    OleDbCommand        cmd;
    DataTable           dt;

    connString  = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath +
                  ";Extended Properties='text;HDR=Yes;FMT=Delimited'";

    conn = new OleDbConnection (connString);
    conn.Open ();

    cmd = new OleDbCommand ("SELECT * FROM " + fileName, conn);
    da  = new OleDbDataAdapter (cmd);
    dt  = new DataTable ();

    da.Fill (dt);

    return dt;
}

The above code is straight forward with a few points:

1. In the connection string, HDR stands for header indicating that the first row has the column-names.
2. Similarly, FMT stands for format and specifies the formatting type. By default it is set to Delimited which specifies a comma-delimited value. The other values include Delimited (x) where x is the specific character, TabDelimited which specifies tab-delimited values and FixeLength which specifies fields with fixed length.
3. The filePath specifies the file path on the machine hosting the application. If the application is hosted on the server then filePath reflects path on the server.

For your interest I would like to mention that we can also make a join between different CSV files. For example, we can use the following query to join two files.

“SELECT * FROM fileName1 f1, fileName2 f2 WHERE f1.CustomerID = f2.CustID”;

Hope this post has been useful for you. Stay tuned for more…