Excel To Insert Statement

INTRODUCTION 

 

Sometimes we need to convert excel sheet data into SQL insert statement.  There are multiple ways to import excel data to the database. 

 

But if someone needs to modify the insert statement then here is an example.

 

Let's start.

 

STEP 1

 

Open Visual Studio 2017 => Click Add Project => Select Web Application Template => Fill in all required details.

 

 

STEP 2 

 

Select Web Application Template.

 

 

STEP 3 

 

Add new item.

Right click in project => Add => New Item => InsertQueryGenerator.aspx

 

Right click in project => Add => New Folder => ExcelFiles

 

 

STEP 4 

 

Paste the below Html code under the body tag.

<form id="form1" runat="server">
        <center><h1>Insert Query Generator</h1></center>
            <asp:Label ID="lblmsg" runat="server" ForeColor="Red"></asp:Label>
            <div style="justify-content: center; display: flex">
                <table>
                    <tr>
                        <td style="border-left: 1px solid #dddddd; border-right: 1px solid #dddddd; border-top: 1px solid #dddddd; border-bottom: 1px solid #dddddd; padding-left: 22px; padding-right: 22px;">
                            <asp:FileUpload ID="FileUpload1" runat="server" class="form-control" />
                        </td>
                    </tr>
                    <tr>
                        <td style="border-left: 1px solid #dddddd; border-right: 1px solid #dddddd; border-top: 1px solid #dddddd; border-bottom: 1px solid #dddddd; padding-left: 22px; padding-right: 22px;text-align:center;">
                            <asp:Button ID="btnUpload" runat="server" Text="Generate" OnClick="btnUpload_Click"
                                class="btn btn-primary" /></td>
                    </tr>
                </table>
            </div>
        <br />
        <center><asp:TextBox ID="txtInsertQuery" runat="server" Rows="10" Columns="100" TextMode="MultiLine" ></asp:TextBox></center>
    </form>

STEP 5 

Replace auto generated class code with the below code.

public partial class InsertQueryGenerator : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void btnUpload_Click(object sender, EventArgs e)
        {
            if (FileUpload1.HasFile)
            {
                string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);
                string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);
                string FolderPath = "ExcelFiles";
                string datetime = DateTime.Now.ToString("ddMMyyyyHHmm");

                string FilePath = Server.MapPath(FolderPath + "\\File_" + datetime + "_" + FileName);

                FileUpload1.SaveAs(FilePath);

                GenerateQuery(FilePath, Extension);
            }
            else
            {
                lblmsg.Text = "Please upload file.";
            }
        }

        protected void GenerateQuery(string FilePath, string Extension)
        {
            string conStr = "";
            string strResult = string.Empty;

            switch (Extension)
            {
                case ".xls": //Excel 97-03
                    conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1;'";
                    break;
                case ".xlsx": //Excel 07
                    conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";
                    break;
            }

            conStr = String.Format(conStr, FilePath, "YES");
            OleDbConnection connExcel = new OleDbConnection(conStr);
            OleDbCommand cmdExcel = new OleDbCommand();
            OleDbDataAdapter oda = new OleDbDataAdapter();
            cmdExcel.Connection = connExcel;

            //Get the name of First Sheet
            connExcel.Open();
            DataTable dtExcelSchema;
            dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);

            ////////////////////////// START GENERATING //////////////////////////////

            string str = string.Empty;
            txtInsertQuery.Text = "";

            for (int table = 0; table < dtExcelSchema.Rows.Count; table++)
            {
                DataTable dt = new DataTable();
                string tblname = dtExcelSchema.Rows[table]["TABLE_NAME"].ToString(); // Get Last Sheet Name Then Last - 1
                string[] ColumnNameArray;


                cmdExcel.CommandText = "SELECT * From [" + tblname + "]";
                oda.SelectCommand = cmdExcel;
                oda.Fill(dt);


                tblname = tblname.Replace("$", "");

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string ColumnName = string.Empty;
                    string ColumnValues = string.Empty;

                    ColumnNameArray = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();

                    for (int col = 0; col < ColumnNameArray.Length; col++)
                    {
                        ColumnName += ColumnNameArray[col] + ",";

                        DateTime dateValue;
                        var isNumeric = int.TryParse(Convert.ToString(dt.Rows[i][ColumnNameArray[col]]), out _);

                        if (DateTime.TryParse(Convert.ToString(dt.Rows[i][ColumnNameArray[col]]), out dateValue))
                        {
                            DateTime date_time = Convert.ToDateTime(dt.Rows[i][ColumnNameArray[col]]);
                            ColumnValues += "'" + date_time.ToString("dd-MM-yyyy") + "',";   // format datetime column
                        }
                        else if (!isNumeric)
                        {
                            ColumnValues  += "'" + dt.Rows[i][ColumnNameArray[col]] + "',"; // add single quote for text value
                        }
                        else if (isNumeric)
                        {
                            ColumnValues += Convert.ToString(dt.Rows[i][ColumnNameArray[col]]) + ",";
                        }
                    }

                    ColumnName = ColumnName.Substring(0, ColumnName.Length - 1);
                    ColumnValues = ColumnValues.Substring(0, ColumnValues.Length - 1);

                    str += "INSERT INTO " + tblname + " (" + ColumnName + ") Values  (" + ColumnValues + ");\n";  // append semicolon with next line after completion of statement
                }

                str = str.Substring(0, str.Length - 1) + "\n\n";  //append 2 time nextline after completion of per sheet data

            }

            txtInsertQuery.Text = str.Substring(0, str.Length - 2);
            connExcel.Close();
        }
    }

STEP 6 

Below is the table structure which i used to make SQL insert statement

 

tblEmployee

 

 

tblDepartment

 

 

STEP 7 

 

Run Application

 

 

Choose xls / xlsx file

 

 

Generate Insert Statement

 

 

I hope this helps.

 

Thank you.