Excel To DataBase Table
5.00/5 (1 vote)
Export Excel to SQL database table (Sample Code)
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:
- Upload Excel to a place so we can work with it.
- Get the data of uploaded Excel in your dataset or datatable.
- 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.