2

I'm importing an excel file into a datatable using OledbConnection and OledbDataAdapter classes. The first two rows of the excel sheet are merged and are empty and the column headers start from the third row. I have 492 rows filled starting from row 3 i.e., 492 - 2 rows = 489 + (1 row for the column header) = 490 filled rows. The issue is that when I debug the code, I get only 478 filled rows and all the excel contents aren't imported into the datatable.

My Code:

//Upload and save the file
string fileName = Path.GetFileName(excelPath.PostedFile.FileName);
string fileLocation = Server.MapPath("~/App_Data/" + fileName);
excelPath.SaveAs(fileLocation);

string conString = string.Empty;
string extension = Path.GetExtension(excelPath.PostedFile.FileName);

switch (extension)
{
    case ".xls": //Excel 97-03
        conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
        break;
    case ".xlsx": //Excel 07 or higher
        conString = ConfigurationManager.ConnectionStrings["Excel07+ConString"].ConnectionString;
        break;
}

conString = string.Format(conString, fileLocation);

using (OleDbConnection excel_con = new OleDbConnection(conString))
{
    excel_con.Open();
    string sheet1 = excel_con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null).Rows[0]["TABLE_NAME"].ToString();
    DataTable dtExcelData = new DataTable();

    using (OleDbDataAdapter oda = new OleDbDataAdapter("SELECT [ID],[Title],[Name],[Post],[Tests],[State] FROM [" + sheet1 + "]", excel_con))
    {
        oda.Fill(dtExcelData);
    }

    excel_con.Close();  
}

What might have gone wrong?

krlzlx
  • 5,752
  • 14
  • 47
  • 55
gRao92
  • 91
  • 1
  • 8

1 Answers1

0

I recently had the same issue when creating an application to move data from Excel into an SQL database.

I resolved mine by removing the merged rows/columns at the top of the sheet. I suspect that the reader looks for each row to have the format you're requesting in your query and that's causing the error.

Ian Murray
  • 339
  • 1
  • 12
  • In my case, I cannot remove the merged rows. Is there any way to specify the starting row of the table so that the reader starts reading from that row on? – gRao92 Mar 11 '16 at 07:15