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());
}

Blank "initial catalog" problem on OLEDB connection string of SQL Server Database

see also -- Got Error: Invalid object name 'master.dbo.spt_values'

As usually we use below connection string to connect to SQL Server using OLEDB provider.

Data Source=DBSERVER;Initial Catalog=DB_NAME;Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;

What if “Initial Catalog” is blank like below one?

Data Source=DBSERVER;Initial Catalog=;Provider=SQLNCLI10.1;Integrated Security=SSPI;Persist Security Info=False;

We know when we create a login user; we have to mansion a database as default to connect. Due to this, OLEDB provider connects to this default database in case of missing Initial Catalog. So that we have to more careful to run DDL using OLEDB.

To bypass this default fault/mistake, we can create a dummy blank database and point all users to this blank database as default database.