Home > c#, SQL > Reading csv file in datatable

Reading csv file in datatable

Recently I had to work on one problem where I had to read the csv file and filling up the data table with the csv file data. I used the OLEDBAdapter to accomplish that with just few lines of code. Following is the code :

            string query = "SELECT Symbol, [Name of Company], FROM [just file name with extension]";
            string connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + [csv file path without file name] + ";" + "Extended Properties=’text;HDR=YES;’";

            //create dataadapter object
            OleDbDataAdapter adapter = new OleDbDataAdapter(query, connStr);

            // create table
            DataTable dtSymbolDetails = new DataTable("ScriptDetails");
            dtSymbolDetails.Columns.Add("Symbol");
            dtSymbolDetails.Columns.Add("Name of Company");

            // fill the table with data using adapter
            adapter.Fill(dtDetails);

So above code fills up the details of the csv file in the data table for further use.

Also note the text in red above. If column name contains the space then in query the column name should be put between [ ]. I learned it in bit hard way. It shows I’m so poor in my sql knowledge.

Advertisements
Categories: c#, SQL Tags: , ,
  1. Bipin Singh
    August 13, 2009 at 4:16 pm

    Dear Concerned,

    this code does’n work at my side. i’ll be very thankful if you can send me the proper code to read the data from .csv file & update into sql server databse.

    thanks & regards,
    Bipin Singh

    • August 14, 2009 at 9:48 am

      Let me know what is the problem you are facing at your end then can suggest something.

  2. Acma
    October 27, 2009 at 7:07 pm

    Hello everyone,
    I am facing a problem like this.
    I am trying to develop a tool for import CSV files into existing tables in my DB.
    but i’m still facing the problem of reading csv into datatable to copy or insert it into my DB table.
    Any help?

  3. shehab
    February 21, 2010 at 8:54 pm

    worked just fine

    • February 22, 2010 at 5:54 am

      glad to hear that it worked for you.

  4. May 12, 2010 at 3:37 pm

    hello every one,

    i m getting problem on defining the path of the file. my file is on c:\csvtestDesk.csv. then what is the path for connStr variable.

    thanks

    • May 12, 2010 at 10:30 pm

      did u try putting just “c:\\”?

  5. Jarrett
    November 23, 2010 at 12:30 am

    Change Microsoft.ACE.OLEDB.12.0 to Microsoft.Jet.OLEDB.4.0 for non 32 bit operating systems.

    DataTable tbl = new DataTable(“MyTable”);
    FileInfo file = new FileInfo(cFile);

    if (file.Extension.ToUpper() == “.CSV”)
    {
    using (OleDbConnection con = new OleDbConnection(“Provider=Microsoft.ACE.OLEDB.12.0; Data Source=” + file.DirectoryName + “;Extended Properties=Text;”))
    {
    using (OleDbCommand cmd = new OleDbCommand(string.Format(“SELECT * FROM [{0}]”, file.Name), con))
    {
    con.Open();
    using (OleDbDataAdapter adp = new OleDbDataAdapter(cmd))
    adp.Fill(tbl);

    }
    }
    }

    • December 8, 2010 at 10:59 pm

      Thanks, Jarrett for this informstion.

  6. ArranM
    April 27, 2011 at 8:35 pm

    Hi, I’ve written something similar that reads a CSV files (or files) and bulk uploads them to a DB: http://arranmaclean.wordpress.com/2010/07/20/net-mvc-upload-a-csv-file-to-database-with-bulk-upload/ hope this might help any of your readers.

  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: