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

  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.