Read Excel File in C# / SSIS script task

Today, I will discuss how to Read an Excel File.

Read from an Excel file: Follow the below steps 

1. Create a connection, OleDbConnection, to read the excel
2. Retrieve all Sheet Names into DataTable
3. Read data from a Sheet and store into DataSet
4. Read each row from DataSet and Print to Console

Add the namespace "using System.Data.OleDb;"

STEP 1. Create a connection

you have to change the Provider Microsoft.ACE.OLEDB.12.0 as per the Excel version.
change the file name and location also.
-------------------
OleDbConnection excelConnection;

string excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;"
              + @"Data Source=C:\MyExcel.xlsx;"
              + "Extended Properties='Excel 8.0;IMEX=1;HDR=NO;TypeGuessRows=0;ImportMixedTypes=Text'";
excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
-------------------
now our Excel connection is open to read the file contants.

STEP 2:  Choose the Sheet Name to Read data.

Now we will retrieve all Sheet names into DataTable
----------------------
DataTable excelDataTable = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

foreach (DataRow row in excelDataTable.Rows)
{
string excelSheetName = row["TABLE_NAME"].ToString().Trim("'".ToCharArray());
Console.writeLine(excelSheetName);
}
----------------------

STEP 3: Read data from a Sheet and store into DataSet
by below code we will get all data of a Sheet onto excelDataSet
-------------
OleDbDataAdapter excelAdapter = new OleDbDataAdapter();
excelCommand = new OleDbCommand();
DataSet excelDataSet = new DataSet ();

excelCommand.Connection = excelConnection;
excelCommand.CommandText = "Select * from [" + excelSheetName + "]";
excelAdapter.SelectCommand = excelCommand;

excelAdapter.Fill(excelDataSet);
--------------

STEP 4: Read each row from DataSet and Print to Console

# read all data...

foreach (DataRow dr in excelDataSet.Tables[0].Rows)
{
    Console.writeLine(dr[0].ToString() + " - " + dr[1].ToString() );
}

#if you want to get actual column names of OLEDB .. try below

foreach (DataColumn dc in excelDataSet.Tables[0].Columns)
{
      Console.Write(dc.ColumnName);
}

#if the first row contains Column names then you try below code to get all values ..

foreach (object columnName in excelDataSet.Tables[0].Rows[0].ItemArray)
{
    Console.Write(columnName.ToString());
}