Excel To DataBase Table

• Download source - 655 KB
• AlternateLink ExcelToDatabase

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.