Every now and then we have a client that needs to, on a regular basis, import data from one system to another and a lot of the time we go for the good old csv file. In the past i have always just written a simple function that reads each line of the file, splits it into cells and import it into the database. But this simple code never fully handles csv’s correctly. I kept thinking “there has to be a simpler way“.

So i got thinking…

Csv files are just like data tables, data tables can be queried… surely there must be a way to just treat this file as a datasource. After a bit of playing around and reading i can up with the following solution.

It requires that the csv data be saved to a file and then we can just use an OleDataAdapter to perform almost any simple SQL statment against it!
 


  using System;
  using System.Data;
  using System.Data.OleDb;
  using System.IO;
  
  public static DataTable ReadCSVFileIntoDataTable(string pFilePath)
  {
      string fullPath = Path.GetFullPath(pFilePath);
      string file = Path.GetFileName(fullPath);
      string dir = Path.GetDirectoryName(fullPath);
  
      string connString = "Provider=Microsoft.Jet.OLEDB.4.0;"
          + "Data Source=\"" + dir + "\\\";"
          + "Extended Properties=\"text;HDR=No;FMT=Delimited\"";
  
      string query = "SELECT * FROM " + file;
  
      DataTable dt = new System.Data.DataTable();
      OleDbDataAdapter da = new OleDbDataAdapter(query, connString);
  
      try
      {
          da.Fill(dt );
      }
      catch (InvalidOperationException /*e*/)
      { }
  
      dAdapter.Dispose();
  
      return dt;
  }
  

You may also like

ASP.NET Button – prevent double clicks

Virtually every asp.net developer has met the situation when a page postback takes quite some time and the user must be prevented from submitting the form twice. The task sounds easy, doesn’t it? Unfortunately there is no easy and straighforward way in ASP.NET to accomplish this.

Keep Reading

Asp.Net MVC convert View to Word Document

One request we always seem to come across as developers is “Can we have a version of the page avaliable as a Word Document?”. With traditional ASP.Net this is easy. Simply place something similar to the following code block in your Page_Load.

Keep Reading

Newsletter sign up

Every couple of months we send out an update on what's been happening around our office and the web. Sign up and see what you think. And of course, we never spam.