Monday, 26 August 2013

How to read data from csv file in c#

 We can read data from CSV file in many of custom ways. In this article, I am going to write the code to read data from CSV file into .NET DataTable by using TextFieldParser. don't try to search this class in C# library because which is not available in C#. TextFieldParser is the Visual basic class. So we need to add reference dll Microsoft.VisualBasic.

  • Open Visual Studio
  • Go to File ->New ->Project.
  • Then go to Visual C# ->Windows and select Console Application
  • Rename the project name as ReadCSVFile.
  • Right-click the Reference, click Add Reference,select Microsoft.VisualBasic, and click OK button

Import CSV file into DataTable C#

You can use the following code example to read data from CSV file in C#

using System;
using System.Data;
using Microsoft.VisualBasic.FileIO;

namespace ReadDataFromCSVFile
  {
    static class Program
      {
        static void Main()
        {
            string csv_file_path=@"C:\Users\Administrator\Desktop\test.csv";
            DataTable csvData = GetDataTabletFromCSVFile(csv_file_path);
            Console.WriteLine("Rows count:" + csvData.Rows.Count);            
            Console.ReadLine();
        }
    private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();
            try
            {
              using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                 {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    //read column names
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return csvData;
        }
      }
    }

Related Articles:

Bulk Insert into SQL Server using SqlBulkCopy in C#
Import CSV File Into SQL Server Using SQL Bulk Copy
Read CSV File and Insert Into SQL Server using Bulk Insert
Convert Object To Byte Array and Byte Array to Object in C#
Add or Remove programs using C# in Control Panel 
Show balloon tooltip c#



Thanks,
Morgan
Software Developer

29 comments:

  1. amazing....thank you so much...its work perfectly.

    ReplyDelete
    Replies
    1. Thank you for ur comment

      Delete
    2. Sir can you tell me how to do the same in ASP.Net using C# ?

      Delete
  2. Thank you for easy way :)

    ReplyDelete
  3. But the first line of the CSV file is not inserted in the datatable...why?? If you have N lines, only N-1 lines are inserted in the datatable object.

    Tankyou

    ReplyDelete
    Replies
    1. The very 1st line is for header which represents the columns in your DB

      Delete
  4. I have two csv files which I have loaded in to data table(thanx to U :) )
    how do I compare these tables and delete columns from one table

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
  6. How to do the same in windows form application ??

    ReplyDelete
    Replies
    1. Hi, Farhan, you can do it by same C# in Windows Form (:... but anyway I have written this article: http://www.morgantechspace.com/2014/02/How-to-Upload-a-CSV-file-in-C-Sharp-Windows-Form-Application.html
      for your help, sorry for late response .

      Delete
    2. This comment has been removed by the author.

      Delete
    3. First of all Thank You+Thanks a_lot . Hi Morgan..Your post inspired me , changed many things for me . You are amazing man.

      Delete
  7. At the time when .csv File is beign read is it possible to sort the column fields. The column data that I have consists of negative values so the type while sorting should be either decimal or Double.how to do it?

    ReplyDelete
  8. How to import the same as type double and not as string

    ReplyDelete
    Replies
    1. Hi Farhan , you can give column type as INT64 or double while create DataTable

      DataTable workTable = new DataTable("Customers");
      workTable.Columns.Add("Purchases", typeof(Double));

      Delete
    2. This comment has been removed by the author.

      Delete
  9. Hi Morgan,how are you ? hope you are doing well
    Can you make a tutorial about how to write DataTable into a CSV file.

    ReplyDelete
    Replies
    1. Fine Farhan, yes I will do it as soon as possible

      Delete
  10. This comment has been removed by the author.

    ReplyDelete
  11. Hi Morgan. I Successfully Imported DataTable to CSV file. Nevertheless your tutorials are clear and really helpful , It will be helpful for lot of people :)

    ReplyDelete
  12. I had troubles loading in some dates via AOD.
    But this code works likes a charm!

    ReplyDelete
  13. Thanks a lot, I was trying to import a CSV file to a Datatable and it was driving me mad because one of the fields (wich was a money value) was imported as an integer all the time, so the decimals were lost. But with your method I could import the correct data, finally!! Thanks!

    ReplyDelete
  14. Can you please tell me ....How to delete the empty cells in the data table once the CSV file is imported.

    ReplyDelete
  15. This comment has been removed by the author.

    ReplyDelete
  16. Hi, i have used the same code its working for smaller size of files, while i am working with the file of size 273MB and more its throws an OutOfMemory exception could you please help me out from this error. Thanks in advance looking for your reply.

    ReplyDelete
  17. hey can you tell me how to copy the column of csv file to another file using c#

    ReplyDelete