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();
}
}