Troubleshooting
Problem
When trying to import an .xlsx spreadsheet into Analyst's Notebook, an error is thrown, and the import cannot be completed.
Symptom
On import, the following error is thrown:
Unable to open spreadsheet. This might be because it is password
protected or in an unexpected format.
Retry or Cancel
Cause
This error can appear in two circumstances:
1. The workstation running Analysts Notebook does not have Microsoft Office 2007 or later installed; or
2. The 64 bit version of Microsoft Office is installed, rather than the 32-bit version.
Prior to Microsoft Excel 2007, Excel saved spreadsheets in a proprietary binary format. This legacy Excel spreadsheet format uses file extension .xls
Starting with Office 2007, Microsoft introduced the new 'Open Office XML' format for Office documents, standardized as ECMA-376. Unlike the older binary formats for Office documents, these newer formats are compressed XML files. An Excel spreadsheet saved in this newer Open Office XML format uses file extension .xslx
From Office 2007 on, Excel can save spreadsheets in either the legacy .xls format, or the new .xlsx format.
To import Excel spreadsheets, Analysts Notebook uses a Microsoft-supplied OLE DB library to open and read the spreadsheet. Microsoft Data Access Components (MDAC) built in to Windows contains the JET OLE DB 4.0 library. This version can only read older .xls format spreadsheets; it cannot read the newer .xlsx format. If Analysts Notebook is installed on its own without Office, it uses this library built in to Windows to open and read .xls format spreadsheets. Because the library built-in to Windows cannot read .xlsx spreadsheets, neither can Analysts Notebook.
To open and import a .xlsx spreadsheet, Analyst's Notebook requires an updated component from Microsoft Office.
Microsoft Office 2007 and later installs an updated version of the JET library called the Access Connectivity Engine. This version can read both .xls and.xlsx format spreadsheets. If Microsoft Office is installed on the workstation, Analysts Notebook will load this more recent Office component instead of the older driver from Windows. Analysts Notebook can then read and import both .xls and .xlsx format spreadsheets.
Microsoft Office ships in both 32-bit and 64-bit versions. Analysts Notebook is a 32-bit application. It it can only use the driver from 32-bit office. It cannot load the updated Access Connectivity Engine driver in 64-bit Office. If the 64-bit version of Office is installed, Analysts Notebook falls back to the older 32-bit driver built in to Windows. Therefore it can only open .xls files, it cannot read .xlsx files.
Resolving The Problem
- Save the spreadsheet in .xls format instead of .xlsx. It is not sufficient just to change the spreadsheet's file extension, the spreadsheet must be saved in Excel 97-2003 Workbook (*.xls) format.
- Replace the 64-bit version of Office with the 32-bit version of Office, on the workstation running Analysts Notebook. Excel Spreadsheets created with 64-bit Excel have exactly the same on-disk format as spreadsheets created with 32-bit Excel. A spreadsheet can be created on one workstation using 64-bit Excel, then copied to the workstation running Analysts Notebook and imported using the 32-bit driver.
For information comparing the 32-bit and 64-bit versions of Microsoft Office, see:
Choose between the 64-bit or 32-bit version of Office
https://support.office.com/en-us/article/Choose-between-the-64-bit-or-32-bit-version-of-Office-2dee7807-8f95-4d0c-b5fe-6c6f49b8d261
Compatibility Between the 32-bit and 64-bit Versions of Office
https://msdn.microsoft.com/en-us/library/office/mt662476.aspx
Was this topic helpful?
Document Information
More support for:
i2 Analyst's Notebook
Software version:
8.9.11, 9.0.0, 9.0.1, 9.0.2, 9.0.3, 9.0.4, 9.0.5
Operating system(s):
Windows
Document number:
558167
Modified date:
16 June 2018
UID
swg22001668