none
Cannot Read Excel file using ACE.OLEDB 14.0 RRS feed

  • Question

  • I'm trying to read an XLSX file using the Access 2010  ACE drivers.  The instructions as I found them say to use:

    Provider=Microsoft.ACE.OLEDB.12.0

    and then in the Extended Properties, use:

    Extended Properties="Excel 14.0;IMEX=1;Readonly=1;MaxScanRows=0;HDR=Yes"

    (Well, I include all my extended properties, but the Excel 14.0 being the key one right now)

    I haven't been able to make that work on any machine combination.  I've tried both 32 and 64 bit, with both Office 2010 installed and the Access Database Engine only.   

    The error I get is, "Could not find installable ISAM"

    Anyone have any ideas?  If I change "Excel 14.0" to "Excel 12.0" it works.  

    The instructions come from this page:
    http://www.microsoft.com/downloads/details.aspx?familyid=C06B8369-60DD-4B64-A44B-84B371EDE16D&displaylang=en

    I have a stack of other problems reading excel files, but I think this is a complain about one thing at a time sort of situation.

    Edit:  I should mention, that while I connect successfully changing things to "Excel 12.0", I can't read everything correctly.  If I have a field with mixed data types (mostly text, some numbers) and throw some text in that's >255 chars, it truncates that string.

    If I have mixed text and numbers (mostly numbers), then it gives me NULL for those items, despite having IMEX=1 in the connection string.

    I hope whoever wrote these drivers feels suitably bad for all the wasted time I've put in... :-)

     

     

    Monday, July 19, 2010 5:04 PM

Answers

All replies

  • The instructions are incorrect. It should be Excel 12.0. I wish they would fix this mistake at the download site.

    The mixed data type issue has always been a problem. First try the IMEX argument in your connection string:

    http://www.connectionstrings.com/excel-2007

    If that doesn't work then see the "System.Data.OleDb to the rescue" section at the below link:

    http://blogs.vertigo.com/personal/aanttila/Blog/archive/2008/03/28/excel-and-csv-reference.aspx

     

     


    Paul ~~~~ Microsoft MVP (Visual Basic)
    Monday, July 19, 2010 6:24 PM
  • As Paul has mentioned it should be Ecdel 12.0 not Excel 14.0. As to fixed data types you could try to use Excel reader from my website. It allows to read mixed content regardless of how many type you have in any single column
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, July 20, 2010 9:44 AM
    Moderator
  • Even you installed Access database engine 2010 version. You have to set the Provider argument of the ConnectionString property to “Microsoft.ACE.OLEDB.12.0” .

    Please view following URL. This will clearly indicate that.

    http://www.microsoft.com/en-us/download/details.aspx?id=13255

    Thanks,

    Erandika.


    Erandika Sandaruwan

    Tuesday, May 15, 2012 10:59 AM
  • I have Windows 7 32-bits installed, therefore my Excel2010 is running in 32-bit mode as well. I cannot get .XLSX working, will only export to .XLS when I select Excel2007 for the Connection Manager in SSIS. When I try to open the .XLS spreadsheet, Excel2010 complains that the file is corrupted and not in the expected format. However all the data is there.


    Achana
    • Edited by ACINAU Thursday, February 21, 2013 6:41 PM
    Thursday, February 21, 2013 6:41 PM
  • Hello,

    You could try the following, see if Excel 2010 complains or not. Would be interesting to see if it does vs using OleDb

    '
    '
    '
    Imports Microsoft.Office.Interop.Excel
    '
    '
    '
    Dim fileName = "Your path and file name"
    fileName = System.IO.Path.GetFullPath(fileName)
    If IO.File.Exists(fileName) Then
        Dim excelApp = New Application()
        Dim workbook = excelApp.Workbooks.Open(fileName, ReadOnly:=True, CorruptLoad:=Microsoft.Office.Interop.Excel.XlCorruptLoad.xlRepairFile)
    End If


    kevininstructor

    Monday, February 25, 2013 2:05 PM