How to read data from csv file in c#

by
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:\UsersAdministratorDesktoptest.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

Related Posts

Advertisement

33 thoughts on “How to read data from csv file in c#”

  5. 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

    Reply

  9. 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?

    Reply

    • 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));

      Reply

  12. 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 🙂

    Reply

  14. 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!

    Reply

  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.

    Reply

  18. Error "The type or namespace name 'TextFieldParser' could not be found (are you missing a using directive or an assembly reference?)", please what do I do?

    Reply

  21. I read this paragraph fully concerning the difference of most up-to-date and earlier technologies, it's amazing article.

    Reply

  23. This function should just be a provided function in the DataTable Library. I don’t understand how it isn’t. big help thanks

    Reply

  24. if (fieldData[i] == “”)
    {
    fieldData[i] = null;
    }

    What if i need to assign some default values (Lets say the column type is integer and need to assign 0 if field is “”) ?

    Reply

    • There is no perfect way to detect column type. If you already know column type for the specific column, then you can write custom logic something like below.

      if (fieldData[i] == “”)
      {
      if (colFields[i] == “IntgereColumName”)
      {
      fieldData[i] = 0;
      }
      else
      {
      fieldData[i] = null;
      }
      }

      Reply

Leave a Comment