Click here to Skip to main content
Click here to Skip to main content

SQL Import Data from Excel

By , 21 Apr 2012
Rate this:
Please Sign up or sign in to vote.

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.

License

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

About the Author

pramodhegde88
Software Developer
India India
No Biography provided

Comments and Discussions

 
QuestionHELP Pinmembersamiji19-Sep-12 0:10 
AnswerRe: HELP Pinmemberpramodhegde8824-Sep-12 0:31 
AnswerRe: HELP PinmemberMr Matt Becker10-Oct-12 11:24 
GeneralRe: HELP Pinmemberpramodhegde8810-Oct-12 22:33 
QuestionHow toimplement this in website . PinmemberShibiny21-Aug-12 23:42 
AnswerRe: How toimplement this in website . Pinmemberpramodhegde8830-Aug-12 6:44 

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

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

| Advertise | Privacy | Mobile
Web03 | 2.8.140421.2 | Last Updated 21 Apr 2012
Article Copyright 2012 by pramodhegde88
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid