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

Excel To DataBase Table

By , 3 Nov 2012
Rate this:
Please Sign up or sign in to vote.

Introduction

Saving Excel in our database table is required in business product software. I am writing this article because I tried a lot to find a better way to insert Excel data in SQL database table.

Background

To insert the data in SQL database table logically, the steps that should be taken are as follows:

  1. Upload Excel to a place so we can work with it.
  2. Get the data of uploaded Excel in your dataset or datatable.
  3. Insert the dataset or datatable in your database table.

Step 1: Prerequisites

To export Excel data in our database table, some important things to do are as follows:

  • Add Microsoft.Office.Interop.Excel.dll in BIN folder of your project. (Right click on BIN folder > Add Reference > Under .NET Tab > Select Microsoft.Office.Interop.Excel.dll > OK)
  • Namespaces
    using System.Data.OleDb;  // oledb namespace to create connectivity with Excel
    using System.IO;    // To save Excel in a specific folder
    using System.Data;  // To insert data in Database   
  • Create a folder in any drive and give the path accordingly in code (You can create a folder in your web app or in server's drive).
  • Database Table should contain Columns (can contain extra column in comparison with your given Excel) accordingly to the Excel which is to be uploaded.

Step 2

As you are ready with prerequisites now, create an .aspx with the following code.

Add asp file upload in .aspx page and button to launch the event.

 <asp:FileUpload ID="FileUpload" runat="server"  />

 <asp:Button ID="btnsave" runat="server" Text="Save"
           OnClick="btnsave_Click" />

Step 3: Create a Method to Connect with Excel using Oledb

Create a method to get the Excel data in DataSet. After the data comes in dataset, data can be easily inserted in our database.

    protected void FillDataSet()
    {
        try
        {
             // create object like sheet and app for office
            Microsoft.Office.Interop.Excel.ApplicationClass app = 
            	new Microsoft.Office.Interop.Excel.ApplicationClass();
            Microsoft.Office.Interop.Excel.Worksheet oSheet;

            string strFilename = FileUpload.PostedFile.FileName;
            strFilename = System.IO.Path.GetFileName(strFilename);
            string ext = Path.GetExtension(strFilename);
            bool hasHeaders = true;
            string HDR = hasHeaders ? "Yes" : "No";
            string strConn;

            //check extension of file

            if (ext.ToLower() == ".xls")
            {
                //create connection with excel using OLEDB
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + 
                fu + " ;Extended Properties=\"Excel 8.0;HDR=" + 
                HDR + ";IMEX=1\"";

                Microsoft.Office.Interop.Excel.WorkbookClass workBook = 
                (Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open
                (fu, 0, true, 5, "", "", true, 
                Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, 
                "\t", false, false, 0, true, 1, 0);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
                app.Visible = false;

                //You must use the $ after the object you reference in the spreadsheet

                OleDbDataAdapter myCmd = new OleDbDataAdapter
                ("SELECT * FROM [" + oSheet.Name + "$]", strConn);

                myCmd.Fill(myDs);

                string source = @"D:\ExelFileForDetail\" + strFilename;
                string target = @"D:\ExelFileForDetail\temp\" + strFilename;

                //move file to another folder if exists 
                //(useful when client have to upload file on daily basis)
                if (File.Exists(target))
                    File.Delete(target);
                File.Move(source, target);
            }

            else if (ext.ToLower() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + 
                fu + ";Extended Properties=\"Excel 12.0;HDR=" + 
                HDR + ";IMEX=2\"";

                //You must use the $ after the object you reference in the spreadsheet

                Microsoft.Office.Interop.Excel.WorkbookClass workBook = 
                	(Microsoft.Office.Interop.Excel.WorkbookClass)app.Workbooks.Open
                	(fu, 0, true, 5, "", "", true, 
                	Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "\t", 
                	false, false, 0, true, 1, 0);
                oSheet = (Microsoft.Office.Interop.Excel.Worksheet)workBook.ActiveSheet;
                app.Visible = false;

                //You must use the $ after the object you reference in the spreadsheet

                OleDbDataAdapter myCmd = new OleDbDataAdapter
                ("SELECT * FROM [" + oSheet.Name + "$]", strConn);

                //fill dataset with your excel data

                myCmd.Fill(myDs);
                string source = @"D:\ExelFileForDetail\" + strFilename;
                string target = @"D:\ExelFileForDetail\temp\" + strFilename;


                if (File.Exists(target))
                    File.Delete(target);
                File.Move(source, target);
            }
            else
            {
                Response.Write("Check the extension of uploaded file.");
            }
        }
        catch (Exception ex)
        {

            Response.Write("Error !" + ex.Message);
        }
    }

Step 4: Save File and Insert Data to Table

To insert data, I am using LINQ. You can use any technique to insert.

protected void btnsave_Click(object sender, EventArgs e)
    {
        try
        {
            int x, j;

            string strFilename = FileUpload.PostedFile.FileName;
            strFilename = System.IO.Path.GetFileName(strFilename);
            string ext = Path.GetExtension(strFilename);

            // check file extension and give alert to upload the right extension
            if (ext.ToLower() != ".xls" && ext.ToLower() != ".xlsx")
            {
                string str = "<script language="'javascript'">alert
                	('File should be in Excel Format')</script>";

                if (!Page.IsStartupScriptRegistered("clientScript"))
                {
                    Page.RegisterStartupScript("clientScript", str);
                }

                return;
            }
            //save file to  folder
            FileUpload.PostedFile.SaveAs(@"D:\ExelFileForDetail\" + strFilename);

            fu = @"D:\ExelFileForDetail\" + strFilename;

            myDs.Clear();
            int t;

            try
            {
                FillDataSet();
                t = myDs.Tables[0].Rows.Count;
            }
            catch (Exception ex)
            {
                //throw ex;
                Response.Write(ex.Message);
                return;
            }

            DataSet ds1 = new DataSet();

            if (myDs.Tables[0].Rows.Count == 0)
            {
                Response.Write("This file Can not Upload /error in File");
                return;
            }
            t = myDs.Tables[0].Rows.Count;

            try
            {
                for (x = 0; x < myDs.Tables[0].Rows.Count; x++)
                {
                    for (j = 0; j < 4; ) //Traverse loop to all column of your Dataset
                    {

                        Detail Dt = new Detail();
                        Dt.Name = Convert.ToString
                        	(myDs.Tables[0].Rows[x][j]).ToString(); j = j + 1;
                        Dt.BirthDate = Convert.ToDateTime
                        	(myDs.Tables[0].Rows[x][j]); j = j + 1;
                        Dt.Address = Convert.ToString
                        	(myDs.Tables[0].Rows[x][j]).ToString(); j = j + 1;
                        Dt.Mobile = Convert.ToString
                        	(myDs.Tables[0].Rows[x][j]).ToString(); j = j + 1;

                        //insert data in your database

                        db.StoredProcedure1(Dt.Name, Dt.BirthDate, Dt.Address, Dt.Mobile);
                    }
                }
            }

            catch (Exception ex)
            {
                Response.Write("Error" + ex.Message);
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex.Message);
        }
        myDs.Clear();  //clear your dataset
    } 

Update

Soon I will update this article with insert data using SqlBulCopy.

License

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

About the Author

Sarvesh Kushwaha
Software Developer
India India
I do believe life is to help others ... So here i am .. in my spare time i learn new things of programming and try to help people with my knowledge .
I'm an energetic, self-motivated and hard-working Developer and Information Technology Professional with experience in projects, website design and development.
 
Visit My Technical Blog
Follow on   Twitter   Google+   LinkedIn

Comments and Discussions

 
SuggestionLoading database using database utilities is generally the way to go. Pinmemberekareem24-Jun-13 7:34 

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
Web04 | 2.8.140421.2 | Last Updated 4 Nov 2012
Article Copyright 2012 by Sarvesh Kushwaha
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid