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;
}
2
  • 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, 2016 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/…
    – AmitWiki
    Feb 19, 2016 at 15:22

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.