Reading CSV file data into a DataTable using C#.Net


Today we will try reading a CSV file data into a DataTable using C#.Net.

The CSV file contains the data in the following format:-
1) The first row contains the column names
2) The data delimiter(separator) is Comma (",") for the columns.


The "CustomerDetails.csv" is placed in the Bin directory of the project


//initialising a StreamReader type variable and will pass the file location
StreamReader oStreamReader = new StreamReader("CustomerDetails.csv");

DataTable oDataTable = null;
int RowCount = 0;
string[] ColumnNames = null;
string[] oStreamDataValues=null;
//using while loop read the stream data till end
while (!oStreamReader.EndOfStream)
{
  String oStreamRowData = oStreamReader.ReadLine().Trim();
  if (oStreamRowData.Length > 0)
  {
    oStreamDataValues = oStreamRowData.Split(',');
    //Bcoz the first row contains column names, we will poluate 
    //the column name by
    //reading the first row and RowCount-0 will be true only once
    if (RowCount == 0)
    {
      RowCount = 1;
      ColumnNames = oStreamRowData.Split(',');
      oDataTable = new DataTable();

      //using foreach looping through all the column names
      foreach (string csvcolumn in ColumnNames)
      {
       DataColumn oDataColumn = new DataColumn(csvcolumn.ToUpper(), typeof(string));

      //setting the default value of empty.string to newly created column
      oDataColumn.DefaultValue = string.Empty;

      //adding the newly created column to the table
      oDataTable.Columns.Add(oDataColumn);
     }
    }
    else
    {
      //creates a new DataRow with the same schema as of the oDataTable            
      DataRow oDataRow = oDataTable.NewRow();

      //using foreach looping through all the column names
      for (int i = 0; i < ColumnNames.Length; i++)
      {
        oDataRow[ColumnNames[i]] = oStreamDataValues[i] == null ? string.Empty : oStreamDataValues[i].ToString();
      }
     
      //adding the newly created row with data to the oDataTable       
      oDataTable.Rows.Add(oDataRow);
    }
  }
}
//close the oStreamReader object
oStreamReader.Close();
//release all the resources used by the oStreamReader object
oStreamReader.Dispose();

//Looping through all the rows in the Datatable
foreach (DataRow oDataRow in oDataTable.Rows)

{

  string RowValues=string.Empty;

  //Looping through all the columns in a row

  foreach (string csvcolumn in ColumnNames)

  {
    //concatenating the values for display purpose
    RowValues += csvcolumn + "=" + oDataRow[csvcolumn].ToString()+";  ";
  }
  //Displaying the result on the console window
  Console.WriteLine(RowValues);
}

Happy Coding.......

Comments

  1. nice we can do also by using TextFieldParser http://www.morgantechspace.com/2013/08/how-to-read-data-from-csv-file-in-c.html

    ReplyDelete
  2. This is great. It saved me a ton of time.

    Thanks for posting this for all to use and enjoy.

    ReplyDelete
  3. It was very useful for me. Thank you!

    ReplyDelete

  4. Good video on the CSV reader. Question though, how could I make it output an XML file instead of displaying on the console?

    ReplyDelete
  5. It is creating a problem if we use (,) in field values

    ReplyDelete
  6. how to save a .csv file item in sharepoint list
    pls help me

    ReplyDelete
  7. Great stuff !!!
    How can I read multiple csv files from the same folder. please help

    ReplyDelete
  8. //adding the newly created row with data to the oDataTable
    oDataTable.Rows.Add(oDataRow);

    this line is throwing exception to me. i have multiple columns in my CSV File.

    The error i am getting is , " This row already belongs to this table

    ReplyDelete
    Replies
    1. Try :
      oDataTable.Rows.Add(oDataRow.ItemArray);

      Delete
    2. Try :
      oDataTable.Rows.Add(oDataRow.ItemArray);

      Delete
  9. hey bro actually im havong problems with getting values of my csv files. I have a csv file having 3 columns namely " Names,English,Maths,Science "( Headers are given in row wise) I want to find the maximum mark in each subject and print the name of the student..
    Sample Output:
    English : <----->
    Maths : <---->
    Science : <----->

    Can yu help me in this case :C

    ReplyDelete
  10. It was very useful for me.
    Thank very much!

    how to insert values of my csv files in my sql server db. I have a csv file having 4 columns namely " Names,Address,Mobile,Email"( Headers are given in row wise) i want to insert these column values in sql table...

    Sample Output:

    Name :
    Address :
    Mobile :
    Email :

    can please help me or sent us source on this mail id : aditraj1@gmail.com

    ReplyDelete

Post a Comment