Tuesday, December 31, 2013

Convert a CSV file to DataTable in C#

Here is a simple code to read CSV file and convert it to a DataTable using CSharp (C#). String enclosed with double quotes will not break the convertion process. In my previous post I have converted a DataTable to CSV with it's header. Check my previous post Click Here. So I have taken the first line in CSV as DataTable column headers. C# Code:


 public static DataTable ConvertCSVtoDataTable(string strFilePath)
 {
            StreamReader sr = new StreamReader(strFilePath);
            string[] headers = sr.ReadLine().Split(','); 
            DataTable dt = new DataTable();
            foreach (string header in headers)
            {
                dt.Columns.Add(header);
            }
            while (!sr.EndOfStream)
            {
                string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");
                DataRow dr = dt.NewRow();
                for (int i = 0; i < headers.Length; i++)
                {
                    dr[i] = rows[i];
                }
                dt.Rows.Add(dr);
            }
            return dt;
 } 

Call the ConvertCSVtoDataTable function like below by passing path of the CSV file.
static void Main(string[] args)
{
      string filepath = "d://ConvertedFile.csv";
      DataTable res = ConvertCSVtoDataTable(filepath);
}

9 comments:

  1. Hi.. I used your code to convert csv to datatable. But when I run it, there was an error that said that "Index was outside the bounds of the array" on line dr[i] = rows[i]. Can you explain why it become like that?

    ReplyDelete
    Replies
    1. In my sample the first line of the CSV file having the headers.
      String[] headers = sr.ReadLine().Split(',');
      Using the above code i am getting all the value from CSV first line. Using that i am creating DataColumns. So all other lines should have the same length as the header count else it will throw error. May be u can provide me your CSV for testing.

      Delete
    2. Ap ghalat khai rahe hain!

      Delete
  2. for (int i = 0; ((i < headers.Length)&&(i < rows.Length)); i++)

    ReplyDelete
  3. Hi ! first of all thank you for sharing your code ! i'm still quite new to the language and i was wondering on how the code differentiate the first line (which is usually column headers) to the others lines that are supposed to be normal rows ?

    ReplyDelete
  4. what would you do if you have some blanks in the values?

    ReplyDelete
  5. This code was what i was looking for and it is working very good for my case, thank you.

    ReplyDelete
  6. Thank you! this is very helpful. I was stuck for a long time but it has helped me to move forward!

    ReplyDelete