Resolved Unable to load first record in excel(.xls)

[anandbe22]

Hello All,

I am doing POC where .xls data insert into SQL Server DB.
But behavior of the excel its taking records getting inserted from third rows.
Example excel file contails header as columns first row and second row starts records but insertion start from third rows its ignores the first record.
Please help me on this issue to resolve.

THanks
Anand.

 

[Skydiver]

Can you show us your code where you read the spreadsheet data and import that into the SQL server?

It's kind of hard to help you just looking at the screenshot. If you forced me to guess based on a screenshot, all I can tell you is that you likely have an off-by-one logic error where you were expecting some collection to be zero based, but it is actually one based when indexing through the collection.

 

[anandbe22]

Can you show us your code where you read the spreadsheet data and import that into the SQL server?

It's kind of hard to help you just looking at the screenshot. If you forced me to guess based on a screenshot, all I can tell you is that you likely have an off-by-one logic error where you were expecting some collection to be zero based, but it is actually one based when indexing through the collection.

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$]";
            string myexceldataquery = string.Format("Select * FROM  [{0}]", "Sheet1$");
            //  string myexceldataquery = "SELECT Request#,User,Application,Data's BU,Instance or Module,Group,Role to Add.:Role,Role to Remove.:Role,User Manager,Manager Approval Status,Manager Statement Date,Group Owner Name,Owner Approval Status,Owner Statement Date,IT Owner Name,IT Owner Approval Status,IT Owner Statement Date,Role to Add.:RoleUniqueFilterinApp,Role to Remove.:RoleUniqueFilterinApp,Role to Add.:Associated Groups\\Roles to Add,Role to Add.:Associated Groups\\Roles to Remove,Role to Remove.:Associated Groups\\Roles to Add,Role to Remove.:Associated Groups\\Roles to Remove,Item Type,Path 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=***** ;Initial Catalog=*********;User ID=sa;Password=Sep@2019";
                //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;


             

                while (dr.Read())
                {
                  

                    bulkcopy.WriteToServer(dr);
                   
               
                }
}

 

[cjard]

You need to hit the edit button, then put [code] ... your code here .... [/code] tags around your code

 

[cjard]

Delete the while (dr.Read()) line. it is advancing the reader by one record before the bulk copy takes over. The loop never runs more than once, so it is useless, and you should not use a loop with BulkCopy

When in doubt, read the manual. Look; no loop:

 

[Skydiver]

I can't test right now, but you only need line 41. You don't need the whole while loop. WriteToServer() does the looping itself. Internally it does something like this pseudo-code:

while (dr.Read()) // get next row
{
    read values from current row
    write values into destination
}

but with your while loop, you now end up with:

while (dr.Read())
{
    while (dr.Read()) // get next row
    {
        read values from current row
        write values into destination
    }
}

 

[cjard]

That second code block is a great way of visualizing the problem

 

[anandbe22]

Delete the while (dr.Read()) line. it is advancing the reader by one record before the bulk copy takes over. The loop never runs more than once, so it is useless, and you should not use a loop with BulkCopy

When in doubt, read the manual. Look; no loop:

View attachment 2610

Thanks alot ur solution works for me .