Saturday, September 15, 2012

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 sample CSV file (CustomerDetails.csv) contains the following data:-
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);
}

Output:-



Happy Coding.......

14 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. The best of paperless and software validation tools in use :-
    It would have Validation process management been never possible in the process of out this Website done as well as related tools portal. There are FDA Paperless Validation many more tools ad good round of factor sally coming in the process which all can help in getting the order delivered Electronic Log Management in point of time. The tools are very particular in the process CSV and helpful in making sure the way it is.

    ReplyDelete
  10. 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
  11. 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