Hello All,
I am trying to load excel (.xls) data import to SQL DB table, but i am not able to insert first record data into SQL table.
I am using C#.net and backend SQL server 2016.
My code :
public void ImportDataFromExcel(string excelFilePath)
{
//declare variables - edit these based on your particular situation
string ssqltable = "SP_GES_Usersrequest";
DataTable dtSQlbulk = new DataTable();
// make sure your sheet name is correct, here sheet name is sheet1,
// so you can change your sheet name if have different
string myexceldataquery = "select* from [Sheet1$]";
try
{
//create our connection strings
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelFilePath +
";extended properties=" + "\"excel 8.0;hdr=yes;\"";
string ssqlconnectionstring = "Data Source=";
//execute a query to erase any previous data from our destination table
string sclearsql = "delete from " + ssqltable;
SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
sqlconn.Open();
sqlcmd.ExecuteNonQuery();
sqlconn.Close();
OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
oledbconn.Open();
OleDbDataReader dr = oledbcmd.ExecuteReader();
SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
bulkcopy.DestinationTableName = ssqltable;

Muhammad Imran AnsariPosted Jan 12, 2023, 6:35 PM
Use the below code to fix this issue:
Vishal JoshiPosted Jan 16, 2023, 9:30 AM
Hello
Please try to change connection string
string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + excelFilePath +
To
string sexcelconnectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + excelFilePath +";Extended Properties='Excel 8.0;HDR=YES'"
Thank you
Muhammad Imran AnsariPosted Jan 14, 2023, 1:26 PM
Hi Anand,
Check the excel file, either your excel file contains header row or not. If excel file doesn't contain header row then use hrd=no in sexcelconnectionstring.
If header row exists then this code should work. Please share your complete code of ImportDataFromExcel method and excel file to check further.
anand mPosted Jan 13, 2023, 12:41 PM
Hi Imran,
I am already using this below code
Issue - I am not getting first rows record from excel to insert into DB;
example in .xls there are 24 records but i am able to insert only 23 records its not getting insert first row record to the DB.
while (dr.Read()) { bulkcopy.WriteToServer(dr); }