Save data from excel to database

Question

User229488726 posted

i have a excel sheet where total data is 150000 .

when i insert in to database it inserted but the problem is only 20000 data insert rest data not insert how to solve the problem this is my code

private void saveformxls()
{
lblmsg.Text = "";
try
{

int bankid = 0, stateid = 0, distid = 0, cityid = 0, flag = 1, id = 0, retval = 0;
DataTable dtbank = new DataTable();
DataSet ds = new DataSet();
if (fildetails.HasFile)
{
string fileExtension = System.IO.Path.GetExtension(fildetails.FileName);

if (fileExtension == ".xls" || fileExtension == ".xlsx")
{
string fileLocation = Server.MapPath("/BankDetails/") + fildetails.FileName;

if (System.IO.File.Exists(fileLocation))
{

}
fildetails.SaveAs(fileLocation);
string excelConnectionString = string.Empty;
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
//connection String for xls file format.
if (fileExtension == ".xls")
{
excelConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 8.0;HDR=Yes;IMEX=2\"";
}
//connection String for xlsx file format.
else if (fileExtension == ".xlsx")
{
excelConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
fileLocation + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
}
//Create Connection to Excel work book and add oledb namespace
OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
excelConnection.Open();
DataTable dt = new DataTable();

dt = excelConnection.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
if (dt == null)
{
return;
}

String[] excelSheets = new String[dt.Rows.Count];
int t = 0;
//excel data saves in temp file here.
foreach (DataRow row in dt.Rows)
{
string x = row["TABLE_NAME"].ToString();
if (x != "Sheet1$_" && x != "Sheet2$_" && x != "Sheet3$_" && x != "Sheet4$_" && x != "Sheet5$_")
{
excelSheets[t] = row["TABLE_NAME"].ToString();
t++;
}
}
OleDbConnection excelConnection1 = new OleDbConnection(excelConnectionString);

int totalsheet = excelSheets.Length;
for (int i = 0; i < totalsheet; i++)
{
string query = string.Format("Select * from [{0}]", excelSheets[i]);
using (OleDbDataAdapter dataAdapter = new OleDbDataAdapter(query, excelConnection1))
{
dataAdapter.Fill(ds);
}
}
}
if (fileExtension.ToString().ToLower().Equals(".xml"))
{
string fileLocation = Server.MapPath("~/Content/") + Request.Files["FileUpload"].FileName;
if (System.IO.File.Exists(fileLocation))
{
System.IO.File.Delete(fileLocation);
}

Request.Files["FileUpload"].SaveAs(fileLocation);
XmlTextReader xmlreader = new XmlTextReader(fileLocation);
// DataSet ds = new DataSet();
ds.ReadXml(xmlreader);
xmlreader.Close();
}
for (int j = 0; j < ds.Tables.Count; j++)
{
for (int i = 0; i < ds.Tables[j].Rows.Count; i++)
{
////here check duplicate data if exist then  return the id else insert and return id  ///
if (!DBNull.Value.Equals(ds.Tables[j].Rows[i][0]))
{
dtbank = objbankbl.findbank(4, ds.Tables[j].Rows[i][0].ToString(), "", "", "");
if (dtbank.Rows.Count > 0)
{
bankid = Convert.ToInt32(dtbank.Rows[0]["ID"]);
}
else
{
string bankname = ds.Tables[j].Rows[i][0].ToString();
if (bankname != " " || bankname != null)
{
bankid = objbankbl.Insert(1, ds.Tables[j].Rows[i][0].ToString(), "", "", 0, "", 0);
}
}
}

////here check duplicate data if exist then  return the id  else insert and return id ///
if (!DBNull.Value.Equals(ds.Tables[j].Rows[i][8]))
{
dtbank = objbankbl.findbank(1, "", ds.Tables[j].Rows[i][8].ToString(), "", "");
if (dtbank.Rows.Count > 0)
{
stateid = Convert.ToInt32(dtbank.Rows[0]["ID"]);
}
else
{
stateid = objbankbl.Insert(2, "", ds.Tables[j].Rows[i][8].ToString(), "", 0, "", 0);
}
}

////here check duplicate data if exist then  return the id  else insert and return id////
if (!DBNull.Value.Equals(ds.Tables[j].Rows[i][7]))
{
dtbank = objbankbl.findbank(2, "", "", ds.Tables[j].Rows[i][7].ToString(), "");
if (dtbank.Rows.Count > 0)
{
distid = Convert.ToInt32(dtbank.Rows[0]["ID"]);
}
else
{
distid = objbankbl.Insert(3, "", "", ds.Tables[j].Rows[i][7].ToString(), stateid, "", 0);
}
}

////here check duplicate data if exist then  return the id  else insert and return id////
if (!DBNull.Value.Equals(ds.Tables[j].Rows[i][6]))
{
dtbank = objbankbl.findbank(3, "", "", "", ds.Tables[j].Rows[i][6].ToString());
if (dtbank.Rows.Count > 0)
{
cityid = Convert.ToInt32(dtbank.Rows[0]["ID"]);
}
else
{
cityid = objbankbl.Insert(4, "", "", "", stateid, ds.Tables[j].Rows[i][6].ToString(), distid);
}
}
////here check duplicate data if exist then  return the id  else insert and return id////
if (!DBNull.Value.Equals(ds.Tables[j].Rows[i][3]))
{
dtbank = objbankbl.findbank(5, ds.Tables[j].Rows[i][1].ToString(), "", "", "");
if (dtbank.Rows.Count > 0)
{
flag = 2;
id = Convert.ToInt32(dtbank.Rows[0]["ID"]);
retval = objbankbl.bankbranchInsertupdate(flag, id, bankid, stateid, distid, cityid, ds.Tables[j].Rows[i][3].ToString(), "", "", ds.Tables[j].Rows[i][1].ToString(), ds.Tables[j].Rows[i][2].ToString(), "", ds.Tables[j].Rows[i][4].ToString(), ds.Tables[j].Rows[i][5].ToString(), 0, "", false, "", "", "", "", "" ,"","","","","");
flag = 1;
id = 0;
bankid = 0;
stateid = 0;
distid = 0;
cityid = 0;
}
else
{
retval = objbankbl.bankbranchInsertupdate(flag, id, bankid, stateid, distid, cityid, ds.Tables[j].Rows[i][3].ToString(), "", "", ds.Tables[j].Rows[i][1].ToString(), ds.Tables[j].Rows[i][2].ToString(), "", ds.Tables[j].Rows[i][4].ToString(), ds.Tables[j].Rows[i][5].ToString(), 0, "", false, "", "", "", "", "", "", "", "", "", "");
totalbranch(bankid);
}
}
}
}
if (retval > 0)
{
Response.Redirect("ManageBankBranch.aspx");
}
}
}
catch {

}
}

after insert 20000  the page redirect to the ManageBankBranch.aspx  as i code

but only insert  20000 , not insert full data 

how to solve please help me 

Answer 1

User632428103 posted

Hello brajalalbrp,

have you got any error ?

which database is ?

if no error, try PERHPAS to make a sample excel file with more 20 000 records with just one column with just => record 1 record 2 ...

test your app and if you find the exact same row in database than your excel file i think there is a problem with  the data inside the excel file...

And is not possible to insert your datas with an import ?

looks at this sample :

http://stackoverflow.com/questions/19302226/import-excel-to-sql-server-2008

Hope this help

Answer 2

User632428103 posted

Hello again,

i've just to try to insert more than 100 000 records in two seconds with the link i give you ...

of course my excel file just contains one column with more than 100 000 records ...

hope this help guy

Answer 3

User229488726 posted

in my excel sheet i have  some column like bank , branch name ,ifsccode, micr code, address, state,district ,city contact

all data not insert in a single table it insert multiple table like i have a table name bank so all the bank are insert in this table i have state table ,district and city  table .

every row of the excel sheet i check the data like the bank name if already in my table the  the return the bank id if not exist the insert and return the bank id, same as state,district and city

and  all the ids insert into the branch table with rest of the data like  branch name ,ifsccode, micr code and  address.

Answer 4

User-698989805 posted

Hello!! Here is an easy solution to import excel data to database:

https://www.mssqltips.com/sqlservertutorial/203/simple-way-to-import-data-into-sql-server/?_e_pi_=7%2CPAGE_ID10%2C1068955473

You can have a look if it is not mandatory to do programmatically.

Answer 5

User229488726 posted

this is not like that what i need 

i need to i import through asp.net c#   

this is my project requirement 

Answer 6

User632428103 posted

you know it's not really possible to help you if you don't respond to some question ?

- did you meet some error when you run your application ?

- which database is it ?

why don't you try for example to make a simple excel file (one column) with more 50 000 rows and try to run you app ...

See the result  ...

If all record are well inserted there is a problem with your data i suppose ...

Hope this help

Answer 7

User229488726 posted

Thanks 

Answer 8

User632428103 posted

Hello brajalalbrp,

did you find the problem ?

if yes, is it possible to know what was the problem ..

Thx

Answer 9

User928438668 posted

Hey brajalal mee too the same project thx for your help....

upcoming bank exams

Answer 10

User527527111 posted

First, i got this problem too but now i rectified it now thanks for your help folks.

Job Alert

Answer 11

User-89093470 posted

Guys try this one, to get save the date from excel to database

dwonload Microsoft's excellibrary.dll 
and use this fuction


public static void ExportToExcel(DataSet dataSet)
{
Random rdm = new Random();
string name = rdm.Next(1000000, 9999999).ToString() + ".xls";
string fileType = "application/ms-excel";
FileInfo newFile = new FileInfo(HttpContext.Current.Server.MapPath("") + name);
ExcelLibrary.DataSetHelper.CreateWorkbook(HttpContext.Current.Server.MapPath("") + name, dataSet);

string attachment = string.Format("attachment; filename={0}", "ExcelFile1.xls");
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", attachment);
HttpContext.Current.Response.ContentType = fileType;
HttpContext.Current.Response.WriteFile(newFile.FullName);
HttpContext.Current.Response.Flush();
newFile.Delete();
HttpContext.Current.Response.End();
}


it will covert your whole excel data in dataset

Hindu Calendar