Surendra Sharma

Surendra Sharma

Search This Blog

Saturday, October 6, 2018

Quick way to convert excel, csv file data to C# Datatable


I received English and Arabic data in Excel file and I have to migrate this data to SQL server.

I don’t want to use Office API for this as it’s a bit complex.

So for this, I used Nuget package “ExcelDataReader” and “ExcelDataReader.DataSet” as below

ExcelDataReader Nuget Package
ExcelDataReader Nuget Package


Note :- Must include “ExcelDataReader.DataSet” Nuget package for getting AsDataSet() method in code.

Code for reading excel file and to get data table is very simple as 

using ExcelDataReader;
using System.IO;

namespace ExcelConsoleApp1
{
    class Program
    {
        static void Main(string[] args)
        {
            //1. Create File Stream for Excel file
            FileStream stream = File.Open("d:\\NewsListWithAllColumns.xlsx", FileMode.Open, FileAccess.Read);

            //2.. Reading from a Excel file *.xlsx
            IExcelDataReader excelReader = ExcelReaderFactory.CreateReader(stream);
            //...
            //2. Reading from a OpenXml Excel file (2007 format; *.xlsx)
            //IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
            //...

            //3. DataSet Configuration - Create column names from first row
            ExcelDataSetConfiguration excelDataSetConfiguration = new ExcelDataSetConfiguration
            {
                ConfigureDataTable = (_) => new ExcelDataTableConfiguration()
                {
                    UseHeaderRow = true
                }
            };

            //4. DataSet - The result of each spreadsheet will be created in the result.Tables
            var dataSet = excelReader.AsDataSet(excelDataSetConfiguration);

            //5. Get Datatable from dataset
            var dataTable = dataSet.Tables[0];

            //6. Read one of the cell to test
            var c11 = dataTable.Rows[2][2];

            //7. Free resources (IExcelDataReader is IDisposable)
            excelReader.Close();

        }
    }
}

Below is the screenshot while to view datatable output in program debug mode as

Excel to Datatable
Excel to Datatable

Let me know if you have any other idea to read excel file and get its values.

1 comment:

  1. I don't have a single method, but I really need to read it. Doesn't display Russian in the list when I load from DataSet

    ReplyDelete