SQL Import Data from Excel

• Download ExcelToSql - 11.2 KB
• Download ImportFromXL - 426 B

Introduction

This tip shows how to import bulk data from an Excel file to SQL DB using Office open XML SDK 2.0.

Background

1. Create a table in SQL. Let the table name be ImportFromXL.

   

2. Create a sample Excel file, which will have the same number of columns as in the table ImportFromXL. As you can see, the first row is named as 'Data', this is because, the column name in the table is 'Data'.

   

Code Walk-Through

Create a simple aspx page, add a FileUpload control and a Button. Choose an Excel file from the system, and click the Upload button.

Onclick of Upload, the following code gets executed:

if (fCtrl.HasFile) 
{
    Stream fileStream = fCtrl.FileContent;
    new ExcelHelper().Upload(fileStream);
}  

ExcelHelper, Upload method would look like:

// convert Excel sheet into DataTable
DataTable table = SheetToTable(fileStream);
// send table to DataHelper to upload data into the DB.
new DataHelper().UploadToDb(table);  

SheetToTable method converts Excel sheet into a data table:

DataTable table = new DataTable();
using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(fileStream, false))
{
   SheetData sheetData = excelDoc.WorkbookPart.WorksheetParts.ElementAt(0).
   Worksheet.ChildElements.OfType<SheetData>().ElementAt(0);
   List<string> siList = new List<string>();
   excelDoc.WorkbookPart.SharedStringTablePart.SharedStringTable.
   ChildElements.OfType<SharedStringItem>().ToList().ForEach(si =>
     {
         siList.Add(si.Text.Text);
     });
   // create columns
   // populate rows
     return table;
}

This table is then sent to UploadToDb, where the DataRow is converted to ImportToExcel entity and saved into the DB. Entity Model is used in this sample to write the data into DB.

using (ImportEntities context = new ImportEntities())
{
    foreach (DataRow row in table.Rows)
    {
        context.AddToImportFromXLs(new ImportFromXL()
        {
            Data = row["Data"].ToString()
        });                    
    }
    context.SaveChanges();
}   

So, the data is finally saved in db, and the process gets completed.

I have attached a sample solution and also the script for creating the table in DB.

Configuration

Change the DB connection string in web.config:

<connectionStrings>
    <add name="ImportEntities"           
 connectionString="metadata=res://*/Data.ImportModel.csdl|
 res://*/Data.ImportModel.ssdl|res://*/Data.ImportModel.msl;
 provider=System.Data.SqlClient;
         provider connection string=&quot;
         data source=localhost;initial catalog=TestDb;
         integrated security=True;multipleactiveresultsets=True;
         App=EntityFramework&quot;" 
         providerName="System.Data.EntityClient" /> 
  </connectionStrings> 

Why OOXML ???

SQL import from Excel can also be accomplished using Microsoft ACE Engine, which requires the software to be installed on the server. This can be avoided by using Office Open XML, which is slightly faster than the other approach.