Click here to Skip to main content
15,867,568 members
Articles / Web Development / ASP.NET
Tip/Trick

SQL Import Data from Excel

Rate me:
Please Sign up or sign in to vote.
3.86/5 (5 votes)
21 Apr 2012CPOL1 min read 49.5K   5.1K   6   7
Excel sheet to SQL DB

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.

    Image 1

  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'.

    Image 2

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.

Image 3

Onclick of Upload, the following code gets executed:

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

ExcelHelper, Upload method would look like:

C#
// 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:

C#
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.

C#
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.

Image 4

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:

XML
<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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer
India India
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
GeneralMy vote of 5 Pin
Humayun Kabir Mamun7-Jun-15 3:20
Humayun Kabir Mamun7-Jun-15 3:20 
QuestionHELP Pin
samiji19-Sep-12 0:10
samiji19-Sep-12 0:10 
AnswerRe: HELP Pin
pramod.hegde24-Sep-12 0:31
professionalpramod.hegde24-Sep-12 0:31 
AnswerRe: HELP Pin
Mr Matt Becker10-Oct-12 11:24
Mr Matt Becker10-Oct-12 11:24 
I experienced the same problem. Turns out, the problem went away when I deleted the extra (default) worksheets in the Excel workbook. HTH.
GeneralRe: HELP Pin
pramod.hegde10-Oct-12 22:33
professionalpramod.hegde10-Oct-12 22:33 
QuestionHow toimplement this in website . Pin
Shibiny21-Aug-12 23:42
Shibiny21-Aug-12 23:42 
AnswerRe: How toimplement this in website . Pin
pramod.hegde30-Aug-12 6:44
professionalpramod.hegde30-Aug-12 6:44 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.