i am inserting excel sheet to database using datatables and OLEDB , the sheets have different names so i am using a data table to check for sheet names and select data from them ,the problem is the data gets inserted multiple times when the loop runs causing
duplicates. i would like the data to be inserted once but I'm not sure how to implement that on the code. Would appreciate the help, here's my code :
public void up(string sFileName = @"filename")
{
string ssqltable = "[dbo].[PB]";
//string sFileName = @"filename";
try{
string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName);
DataTable dt = new DataTable();
SqlConnection sqlconn = new SqlConnection(strConnString);
sqlconn.Open();
using (OleDbConnection connection = new OleDbConnection(sConStr))
{
connection.Open();
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
foreach(var sheet in sheets) //loop through the collection of sheets ;)
{
//your logic here...
string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
//get data
OleDbConnection oledbconn = new OleDbConnection(sConStr);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
{
//sql bulk copy here!
DataTable table = new DataTable("benlist");
table.Load(dr);
// add two extra columns to data table to be added to database table
table.Columns.Add("Filename",typeof(string));
table.Columns.Add("User",typeof(string));
//set path
string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName);
// add data to additional columns
foreach (DataRow row in table.Rows){
row["Filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath);
row["User"] = Session["Username"].ToString();
}
Session["filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath);
SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString);
bulkcopy.DestinationTableName = ssqltable;
////Mapping Table column
bulkcopy.ColumnMappings.Add("ParentId", "[ParentId]");
bulkcopy.ColumnMappings.Add("PPRDate", "[PPRDate]");
bulkcopy.ColumnMappings.Add("Caption", "[Caption]");
bulkcopy.ColumnMappings.Add("Filename","Filename");
bulkcopy.ColumnMappings.Add("User","User");
//sqlcmd.ExecuteNonQuery();
// while (dr.Read())
// {
bulkcopy.WriteToServer(table);
// }
connection.Close();
sqlconn.Close();
}
}
}
}
catch (Exception){}
ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);
the problem is the data gets inserted multiple times when the loop runs causing duplicates.
According to your desrciptions,If your excel has no duplicate values,
I suggest you could add breakpoints in your loop,and add if-else clause to judge.
zenani
bulkcopy.WriteToServer(table);
Or you could filter table before this line,such as: Distinct()...
Best Regards.
Yuki Tao
MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
using (OleDbConnection connection = new OleDbConnection(sConStr))
{
connection.Open();
/// get sheet name
dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
//var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
// foreach(var sheet in sheets) //loop through the collection of sheets ;)
// {
var sheets = dt.Rows[0].Field<string>("TABLE_NAME");
//your logic here...
string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets);
//get data
None
0 Points
11 Posts
insert excel sheet to database , prevent data from being inserted multiple times causing dupliaca...
May 02, 2019 07:55 AM|zenani|LINK
i am inserting excel sheet to database using datatables and OLEDB , the sheets have different names so i am using a data table to check for sheet names and select data from them ,the problem is the data gets inserted multiple times when the loop runs causing duplicates. i would like the data to be inserted once but I'm not sure how to implement that on the code. Would appreciate the help, here's my code :
public void up(string sFileName = @"filename") { string ssqltable = "[dbo].[PB]"; //string sFileName = @"filename"; try{ string sConStr = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES';", sFileName); DataTable dt = new DataTable(); SqlConnection sqlconn = new SqlConnection(strConnString); sqlconn.Open(); using (OleDbConnection connection = new OleDbConnection(sConStr)) { connection.Open(); dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); var sheets = dt.Rows[0].Field<string>("TABLE_NAME"); foreach(var sheet in sheets) //loop through the collection of sheets ;) { //your logic here... string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets); //get data OleDbConnection oledbconn = new OleDbConnection(sConStr); OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn); oledbconn.Open(); OleDbDataReader dr = oledbcmd.ExecuteReader(); { //sql bulk copy here! DataTable table = new DataTable("benlist"); table.Load(dr); // add two extra columns to data table to be added to database table table.Columns.Add("Filename",typeof(string)); table.Columns.Add("User",typeof(string)); //set path string CurrentFilePath = Path.GetFullPath(fuAttachment.PostedFile.FileName); // add data to additional columns foreach (DataRow row in table.Rows){ row["Filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath); row["User"] = Session["Username"].ToString(); } Session["filename"] = Path.GetFileNameWithoutExtension(CurrentFilePath); SqlBulkCopy bulkcopy = new SqlBulkCopy(strConnString); bulkcopy.DestinationTableName = ssqltable; ////Mapping Table column bulkcopy.ColumnMappings.Add("ParentId", "[ParentId]"); bulkcopy.ColumnMappings.Add("PPRDate", "[PPRDate]"); bulkcopy.ColumnMappings.Add("Caption", "[Caption]"); bulkcopy.ColumnMappings.Add("Filename","Filename"); bulkcopy.ColumnMappings.Add("User","User"); //sqlcmd.ExecuteNonQuery(); // while (dr.Read()) // { bulkcopy.WriteToServer(table); // } connection.Close(); sqlconn.Close(); } } } } catch (Exception){} ClientScript.RegisterStartupScript(GetType(), "alert", "alert('File Uploaded');", true);Contributor
3320 Points
1322 Posts
Re: insert excel sheet to database , prevent data from being inserted multiple times causing dupl...
May 03, 2019 08:02 AM|Yuki Tao|LINK
Hi zenani,
According to your desrciptions,If your excel has no duplicate values,
I suggest you could add breakpoints in your loop,and add if-else clause to judge.
Or you could filter table before this line,such as: Distinct()...
Best Regards.
Yuki Tao
Please remember to click "Mark as Answer" the responses that resolved your issue.
If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.
None
0 Points
11 Posts
Re: insert excel sheet to database , prevent data from being inserted multiple times causing dupl...
May 03, 2019 09:09 AM|zenani|LINK
hi Yuki , thank you for your response
how can i use an if statement in the loop so it runs only once?
None
0 Points
11 Posts
Re: insert excel sheet to database , prevent data from being inserted multiple times causing dupl...
May 03, 2019 12:33 PM|zenani|LINK
hi Yuki thanks for your advice, i decided to remove the loop ,the data no longer gets duplicated when i insert it into the database table,
<div class="text" itemprop="text">reference: href="https://stackoverflow.com/questions/1438083/getting-the-first-sheet-from-an-excel-document-regardless-of-sheet-name-with-ole"
using (OleDbConnection connection = new OleDbConnection(sConStr)) { connection.Open(); /// get sheet name dt = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" }); //var sheets = dt.Rows[0].Field<string>("TABLE_NAME"); // foreach(var sheet in sheets) //loop through the collection of sheets ;) // { var sheets = dt.Rows[0].Field<string>("TABLE_NAME"); //your logic here... string myexceldataquery = string.Format("Select * FROM [{0}]; ", sheets); //get data</div> <div></div> <div class="qa-info clearfix"></div>