0

Below is the function I used. But I receive the error message:

external table not in the expected format

As a solution provided at different sites I used it as commented below but I receive the same error. Any suggestions?

public DataTable exceldata(string pathName, string sheetName)
{
    DataTable tbContainer = new DataTable();
    string strConn = string.Empty;
    if (string.IsNullOrEmpty(sheetName)) { sheetName = "Sheet1"; }
    FileInfo file = new FileInfo(pathName);
    if (!file.Exists)
    {
        throw new Exception("Error, file doesn't exists!");
    }
    string extension = file.Extension;
    switch (extension)
    {
        case ".xls":
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            // strConn = @"Data Source="+ pathName + ";Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;";
            break; Solution provided
        case ".xlsx":
            strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + pathName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
            //  strConn = @"Data Source="+ pathName + ";Provider=Microsoft.ACE.OLEDB.12.0; Extended Properties=Excel 12.0;"; Solution provided

            break;
        default:
            strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
            break;
    }
    OleDbConnection cnnxls = new OleDbConnection(strConn);
    OleDbDataAdapter oda = new OleDbDataAdapter(string.Format("select * from [{0}$]", sheetName), cnnxls);
    DataSet ds = new DataSet();
    oda.Fill(tbContainer);
    return tbContainer;
}
  • In which way is this related to SharePoint (or SharePoint Online, as you tagged your question this way)? Is the Excel file you want to import stored in a SharePoint Online site? In case your question is not related to SharePoint (or SharePoint Online), you should ask it at stackoverflow.com, the platform for general programming questions in the StackExchange network. – jcp Feb 19 '16 at 11:04
  • 1
    This driver is very unstable and will keep you wandering through registry and concurrency issues while still causing problems. Please don't abandon questions where discussions are still open. Have replied to your comment in your question: sharepoint.stackexchange.com/questions/171330/… – SlackerAmit Feb 19 '16 at 15:22

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.