none
Reading Excel File Issue RRS feed

  • Question

  • Hi,

    I am facing one issue regarding reading excel file (.xls) pro grammatically using c#.Issue is the line below the header row[second row] is missing during reading. I am using below code to read the file. Can anybody tell me how to resolve this issue.

    string myexceldataquery = "select [Business ID],[Customer Name] from [Sheet1$]";   
    string sexcelconnectionstring = @"provider=microsoft.jet.oledb.4.0;data source=" + path + ";extended properties=" + "\"excel 8.0;HDR=Yes;IMEX=1;\"";
    string ssqlconnectionstring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;

    string sclearsql = "truncate table " + sqltable;
    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 = sqltable;
    while (dr.Read())
    {
        bulkcopy.WriteToServer(dr);
    }
    dr.Close();
    oledbconn.Close();

    • Edited by Cas Raj Sunday, February 4, 2018 5:45 AM
    • Moved by Fei Hu Monday, February 5, 2018 7:58 AM Excel related
    Saturday, February 3, 2018 11:57 AM

All replies

  • You need to edit your post so we can see everything rather than a one liner.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, February 3, 2018 12:58 PM
  • Hi Cas,

    According to your description, your issue is more related about Excel Development. And this forum is discussing and asking questions about C# programming language, IDE, libraries, samples and tools, I will move this thread to corresponding forum: Microsoft Office for Developers > Excel for Developers Forum for dedicated information.

    Thank you for your understanding.

    Regards,

    Stanly


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, February 5, 2018 7:57 AM
  • Hello Cas Raj,

    I could reproduce your issue, please try to use below code as wordaround.

     SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
    
                SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);
                sqlconn.Open();
                sqlcmd.ExecuteNonQuery();
                
    
                OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
         
    
                OleDbDataAdapter objAdapter = new OleDbDataAdapter(myexceldataquery,oledbconn);
    
                DataSet objDataset = new DataSet();
             
                System.Data.DataTable objTable = new System.Data.DataTable();
                objAdapter.Fill(objTable);
                for (int i = 0; i < objTable.Rows.Count; i++)
                {
                    try
                    {
                        sqlcmd.CommandText = "insert into "+ sqltable+" values("+ objTable.Rows[i][0]+ ",'"+ objTable.Rows[i][1] + "')";
                        sqlcmd.ExecuteNonQuery();
                    }
                    catch (Exception ex)
                    {
                        continue;
                    }
                }
                oledbconn.Close();
                sqlconn.Close();
    

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, February 6, 2018 10:36 AM