Import Excel Data to SQL Server

In this article will see how to import the data from Excel to a database using a C# console application. Many of the times, you need to work with these problems where you want to read data from the excel and store it into a database.

Step1: Create Console Application

Visual Studio -> New Project -> Console Application

Step2: Create an excel file with some sample data (Open XML spreadsheet)

Step3: To import an Excel file into SQL Server we need to create a table in the database. While creating the table, you will make sure that the Schema of the table is the same as the data in the Excel Sheet. Such as the same column names and data types of each domain.

Step4: Writing console app logic for fetching excel spreadsheet data in datatable.

DataTable dt = new DataTable();
string filePath = “C:\\Users\\Vaibhav\\Downloads\\Inventory.xlsx”;
string connectionStr = ConfigurationManager.ConnectionStrings[“conn”].ToString();
using (SpreadsheetDocument spreadSheetDocument = SpreadsheetDocument.Open(filePath, false))
{
//Get sheet data
WorkbookPart workbookPart = spreadSheetDocument.WorkbookPart;
IEnumerable<Sheet> sheets = spreadSheetDocument.WorkbookPart.Workbook.GetFirstChild<Sheets>().Elements<Sheet>();
string relationshipId = sheets.First().Id.Value;
WorksheetPart worksheetPart = (WorksheetPart)spreadSheetDocument.WorkbookPart.GetPartById(relationshipId);
Worksheet workSheet = worksheetPart.Worksheet;
SheetData sheetData = workSheet.GetFirstChild<SheetData>();
IEnumerable<Row> rows = sheetData.Descendants<Row>();
// Set columns
foreach (Cell cell in rows.ElementAt(0))
{
dt.Columns.Add(cell.CellValue.InnerXml);
}
//Write data to datatable
foreach (Row row in rows.Skip(1))
{
DataRow newRow = dt.NewRow();
for (int i = 0; i < row.Descendants<Cell>().Count(); i++)
{
if (row.Descendants<Cell>().ElementAt(i).CellValue != null)
{
newRow[i] = row.Descendants<Cell>().ElementAt(i).CellValue.InnerXml;
}
else
{
newRow[i] = DBNull.Value;
}
}
dt.Rows.Add(newRow);
}
}

Step5: Inserting datatable into the SQL server using SqlBulkCopy.

The SqlBulkCopy class is part of the System.Data.SqlClient namespace. In this class, you have always 2 parts, the source, and the destination. The source could be XML, Access, Excel or SQL (in other words any type of datasource you have). This data can be loaded into a datatable or datareader after the destination portion of the data is inserted very quickly into the table.

SqlBulkCopy bulkCopy = new SqlBulkCopy(connectionStr, SqlBulkCopyOptions.KeepIdentity);
try
{
columns.ForEach(col => { bulkCopy.ColumnMappings.Add(col, col); });
bulkCopy.DestinationTableName = “Inventory”;
bulkCopy.WriteToServer(dt);
}
catch (Exception ex)
{
throw ex;
}
finally
{
bulkCopy.Close();
}

If you don't want to insert data using SqlBulkCopy you can prefer to operate on datatable data one by one for that you can use below snippet instead of the SqlBulkCopy.

using (SqlConnection conn = new SqlConnection(connectionStr))
{
conn.Open();
for (int i = 0; i < dt.Rows.Count; i++)
{
SqlCommand cmd = new SqlCommand(“Select count(*) from [dbo].[Inventory] where AZId=’” + dt.Rows[i][“AZId”].ToString() + “‘“, conn);
int count = (int)cmd.ExecuteScalar();
if (count > 0)
{
Console.WriteLine(“Perform operation because entry already exists!!”);
}
else
{
Console.WriteLine(“Insert Entry because entry not exists in table!!”);
}
}
conn.Close();
}

Thank You, See you in the next article !!

You can reach out to me here,

LinkedIn: https://www.linkedin.com/in/vaibhav-bhapkar

Email: vaibhavbhapkar.medium@gmail.com