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.
 
Excel To Insert Statement
 
STEP 2 
 
Select Web Application Template.
 
Excel To Insert Statement
 
STEP 3 
 
Add new item.

Right click in project => Add => New Item => InsertQueryGenerator.aspx
 
Right click in project => Add => New Folder => ExcelFiles
 
Excel To Insert Statement
 
STEP 4 
 
Paste the below Html code under the body tag.
  1. <form id="form1" runat="server">  
  2.         <center><h1>Insert Query Generator</h1></center>  
  3.             <asp:Label ID="lblmsg" runat="server" ForeColor="Red"></asp:Label>  
  4.             <div style="justify-content: center; display: flex">                  
  5.                 <table>                     
  6.                     <tr>  
  7.                         <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;">  
  8.                             <asp:FileUpload ID="FileUpload1" runat="server" class="form-control" />  
  9.                         </td>  
  10.                     </tr>  
  11.                     <tr>  
  12.                         <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;">  
  13.                             <asp:Button ID="btnUpload" runat="server" Text="Generate" OnClick="btnUpload_Click"  
  14.                                 class="btn btn-primary" /></td>  
  15.                     </tr>                      
  16.                 </table>  
  17.             </div>  
  18.         <br />  
  19.         <center><asp:TextBox ID="txtInsertQuery" runat="server" Rows="10" Columns="100" TextMode="MultiLine" ></asp:TextBox></center>  
  20.     </form>  

STEP 5 

Replace auto generated class code with the below code.
  1. public partial class InsertQueryGenerator : System.Web.UI.Page  
  2.     {  
  3.         protected void Page_Load(object sender, EventArgs e)  
  4.         {  
  5.   
  6.         }  
  7.         protected void btnUpload_Click(object sender, EventArgs e)  
  8.         {  
  9.             if (FileUpload1.HasFile)  
  10.             {  
  11.                 string FileName = Path.GetFileName(FileUpload1.PostedFile.FileName);  
  12.                 string Extension = Path.GetExtension(FileUpload1.PostedFile.FileName);  
  13.                 string FolderPath = "ExcelFiles";  
  14.                 string datetime = DateTime.Now.ToString("ddMMyyyyHHmm");  
  15.   
  16.                 string FilePath = Server.MapPath(FolderPath + "\\File_" + datetime + "_" + FileName);  
  17.   
  18.                 FileUpload1.SaveAs(FilePath);  
  19.   
  20.                 GenerateQuery(FilePath, Extension);  
  21.             }  
  22.             else  
  23.             {  
  24.                 lblmsg.Text = "Please upload file.";  
  25.             }  
  26.         }  
  27.   
  28.         protected void GenerateQuery(string FilePath, string Extension)  
  29.         {  
  30.             string conStr = "";  
  31.             string strResult = string.Empty;  
  32.   
  33.             switch (Extension)  
  34.             {  
  35.                 case ".xls"//Excel 97-03  
  36.                     conStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1};IMEX=1;'";  
  37.                     break;  
  38.                 case ".xlsx"//Excel 07  
  39.                     conStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR={1}'";  
  40.                     break;  
  41.             }  
  42.   
  43.             conStr = String.Format(conStr, FilePath, "YES");  
  44.             OleDbConnection connExcel = new OleDbConnection(conStr);  
  45.             OleDbCommand cmdExcel = new OleDbCommand();  
  46.             OleDbDataAdapter oda = new OleDbDataAdapter();  
  47.             cmdExcel.Connection = connExcel;  
  48.   
  49.             //Get the name of First Sheet  
  50.             connExcel.Open();  
  51.             DataTable dtExcelSchema;  
  52.             dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);  
  53.   
  54.             ////////////////////////// START GENERATING //////////////////////////////  
  55.   
  56.             string str = string.Empty;  
  57.             txtInsertQuery.Text = "";  
  58.   
  59.             for (int table = 0; table < dtExcelSchema.Rows.Count; table++)  
  60.             {  
  61.                 DataTable dt = new DataTable();  
  62.                 string tblname = dtExcelSchema.Rows[table]["TABLE_NAME"].ToString(); // Get Last Sheet Name Then Last - 1  
  63.                 string[] ColumnNameArray;  
  64.   
  65.   
  66.                 cmdExcel.CommandText = "SELECT * From [" + tblname + "]";  
  67.                 oda.SelectCommand = cmdExcel;  
  68.                 oda.Fill(dt);  
  69.   
  70.   
  71.                 tblname = tblname.Replace("$""");  
  72.   
  73.                 for (int i = 0; i < dt.Rows.Count; i++)  
  74.                 {  
  75.                     string ColumnName = string.Empty;  
  76.                     string ColumnValues = string.Empty;  
  77.   
  78.                     ColumnNameArray = dt.Columns.Cast<DataColumn>().Select(x => x.ColumnName).ToArray();  
  79.   
  80.                     for (int col = 0; col < ColumnNameArray.Length; col++)  
  81.                     {  
  82.                         ColumnName += ColumnNameArray[col] + ",";  
  83.   
  84.                         DateTime dateValue;  
  85.                         var isNumeric = int.TryParse(Convert.ToString(dt.Rows[i][ColumnNameArray[col]]), out _);  
  86.   
  87.                         if (DateTime.TryParse(Convert.ToString(dt.Rows[i][ColumnNameArray[col]]), out dateValue))  
  88.                         {  
  89.                             DateTime date_time = Convert.ToDateTime(dt.Rows[i][ColumnNameArray[col]]);  
  90.                             ColumnValues += "'" + date_time.ToString("dd-MM-yyyy") + "',";   // format datetime column  
  91.                         }  
  92.                         else if (!isNumeric)  
  93.                         {  
  94.                             ColumnValues  += "'" + dt.Rows[i][ColumnNameArray[col]] + "',"// add single quote for text value  
  95.                         }  
  96.                         else if (isNumeric)  
  97.                         {  
  98.                             ColumnValues += Convert.ToString(dt.Rows[i][ColumnNameArray[col]]) + ",";  
  99.                         }  
  100.                     }  
  101.   
  102.                     ColumnName = ColumnName.Substring(0, ColumnName.Length - 1);  
  103.                     ColumnValues = ColumnValues.Substring(0, ColumnValues.Length - 1);  
  104.   
  105.                     str += "INSERT INTO " + tblname + " (" + ColumnName + ") Values  (" + ColumnValues + ");\n";  // append semicolon with next line after completion of statement  
  106.                 }  
  107.   
  108.                 str = str.Substring(0, str.Length - 1) + "\n\n";  //append 2 time nextline after completion of per sheet data  
  109.   
  110.             }  
  111.   
  112.             txtInsertQuery.Text = str.Substring(0, str.Length - 2);  
  113.             connExcel.Close();  
  114.         }  
  115.     }  

STEP 6 

Below is the table structure which i used to make SQL insert statement
 
tblEmployee
 
Excel To Insert Statement
 
tblDepartment
 
Excel To Insert Statement
 
STEP 7 
 
Run Application
 
Excel To Insert Statement
 
Choose xls / xlsx file
 
Excel To Insert Statement
 
Generate Insert Statement
 
Excel To Insert Statement
 
I hope this helps.
 
Thank you.