locked
External table is not in the expected format while uploading Excel file data to DB RRS feed

  • Question

  • User762859723 posted

    HI,

    I am doing bulk excel upload function. When i upload the excel data i get this error even though i changed the OLDB connection string

     connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=1\"";

    or

     connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";

    or

     connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 12.0 Xml;";

    I used all this option already but no use. kindly pls support me.

    This is my code:

    if (strExtension.Equals(".xls"))
    {
    connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Mode=ReadWrite;Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
    // connStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 8.0 Xml;";
    }
    else
    {
    // connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Mode=ReadWrite;Extended Properties=\"Excel 12.0 Xml;HDR=YES;\"";
    // connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties=Excel 12.0 Xml;";
    connStr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + strUploadFileName + ";Extended Properties=\"Excel 12.0;HDR=Yes;IMEX=2\"";
    }
    
    using (OleDbConnection connection = new OleDbConnection(connStr))
    {
    
    OleDbDataAdapter da = new OleDbDataAdapter();
    string selectStmt = string.Format("Select * FROM [Sheet1$]");
    
    connection.Open();
    OleDbCommand command = new OleDbCommand(selectStmt, connection);
    da.SelectCommand = command;
    
    da.Fill(dataset);
    connection.Close();
    _gi.InsertGIPendingUpload(dataset.Tables[0], "xxx", ddlPlant.SelectedValue);
    
    }

    Pls reply as soon as possible.

    Praveen.

    Tuesday, April 15, 2014 3:35 AM

All replies

  • User753101303 posted

    Hi,

    And the file format you are using is ? .xlsx, .xlsb, .xlsm, .xml etc... ? Make sure for example this is not a xls file that would have been saved with another extension. I would also create a test file to see what happens (to find it this is a problem with this particular file or a general problem with the file format you are using).

    Tuesday, April 15, 2014 6:52 AM
  • User762859723 posted

    PatriceSc,

    The file format is .xlsx  oly i m going to upload, so as mentioned on my condition it will go to else condition only i checked this with debugging. I tried many ways but no use. I get the same error. So any idea for this issue?

    Praveen

    Wednesday, April 16, 2014 1:12 AM
  • User753101303 posted

    And you have the same error when using "Excel 12.0 Xml" ?

    According to http://www.microsoft.com/en-us/download/details.aspx?id=13255 (and "Install instructions"), "Excel 12.0" is for .xlsb files... Else I'll have to give this a try.

    Wednesday, April 16, 2014 5:08 AM
  • User762859723 posted

    PatriceSc,

    I am using .xlsx file only. I get that error even after using 'Excel 12.0 Xml' tat is my problem here.

    Plz help any other to solve this.

    Wednesday, April 16, 2014 6:05 AM
  • User762859723 posted

    Hi,

    I found one issue. The excel i m going to upload, has 3 numerical column as string type. When I convert that string type to numerical then save that excel nd upload, it works. but i can't do this every time. for this only we are using IMEX = 1 in that connection string. But after that also i receive the same error. I dnt know how to solve this issue. 

    Pls help me.

    Wednesday, April 16, 2014 11:46 PM
  • User753101303 posted

    Going to try. I noticed that you have IMEX=2 rather than IMEX=1. Is this on purpose ?

    Thursday, April 17, 2014 1:22 PM