Click here to Skip to main content
11,923,517 members (62,386 online)
Click here to Skip to main content
Add your own
alternative version


5 bookmarked

SQL Import Data from Excel

, 21 Apr 2012 CPOL
Rate this:
Please Sign up or sign in to vote.
Excel sheet to SQL DB


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


  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).
   List<string> siList = new List<string>();
   ChildElements.OfType<SharedStringItem>().ToList().ForEach(si =>
   // 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()

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.


Change the DB connection string in web.config:

    <add name="ImportEntities"           
         provider connection string=&quot;
         data source=localhost;initial catalog=TestDb;
         integrated security=True;multipleactiveresultsets=True;
         providerName="System.Data.EntityClient" /> 

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.


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


About the Author

Technical Lead
India India
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
Humayun Kabir Mamun7-Jun-15 4:20
memberHumayun Kabir Mamun7-Jun-15 4:20 
QuestionHELP Pin
samiji19-Sep-12 1:10
membersamiji19-Sep-12 1:10 
AnswerRe: HELP Pin
pramodhegde8824-Sep-12 1:31
memberpramodhegde8824-Sep-12 1:31 
AnswerRe: HELP Pin
Mr Matt Becker10-Oct-12 12:24
memberMr Matt Becker10-Oct-12 12:24 
GeneralRe: HELP Pin
pramodhegde8810-Oct-12 23:33
memberpramodhegde8810-Oct-12 23:33 
QuestionHow toimplement this in website . Pin
Shibiny22-Aug-12 0:42
memberShibiny22-Aug-12 0:42 
AnswerRe: How toimplement this in website . Pin
pramodhegde8830-Aug-12 7:44
memberpramodhegde8830-Aug-12 7: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.

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