Problem Note 52624: Support for importing Microsoft Excel files into SAS® Visual Analytics
 |  |  |  |
Overview
Beginning with SAS Visual Analytics 6.3, PROC IMPORT is used to import Microsoft Excel files. Because of this change, older versions of Excel spreadsheets and Excel spreadsheets that contain DBCS characters might fail to import correctly. This SAS note describes errors that you might see when importing these spreadsheets, and errors that you might see when using tables created from these spreadsheets. It also lists the file versions and types that should import correctly.
Generally, you can avoid problems by importing spreadsheets in XLSX format instead of XLS format.
Errors
During the import process
When importing, you might see one of the following errors:
-
com.sas.bicommon.selfserv.extractor.ExtractorException: OpenXML file with XLS content type
at com.sas.bicommon.selfserv.extractor.impl.excel.poi.event.xls.XlsParseUtil.getDocumentInputStream(XlsParseUtil.java:52)
at com.sas.bicommon.selfserv.extractor.impl.excel.poi.event.xls.XlsParseUtil.processEvents(XlsParseUtil.java:26)
...
Caused by: org.apache.poi.poifs.filesystem.OfficeXmlFileException: The supplied data appears to be in the Office 2007+ XML.
You are calling the part of POI that deals with OLE2 Office Documents. You need to call a different part of POI to process this data
(eg XSSF instead of HSSF)
at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:131)
at org.apache.poi.poifs.storage.HeaderBlock.(HeaderBlock.java:104) -
com.sas.bicommon.selfserv.extractor.ExtractorException: Unable to open Excel workbook
-
java.io.IOException: Invalid header signature; read 0x78206C6D74683C0A, expected 0xE11AB1A1E011CFD0
Using imported data in SAS® Visual Analytics Explorer
Even if the import is successful, you might have problems when you attempt to use the table. Specifically, in SAS Visual Analytics Explorer, you might see an error that is similar to the following:
content = (null)
errorID = 0
faultCode = "Server.Processing"
faultDetail = (null)
faultString = "com.sas.biv.exception.BivException : com.sas.lasr.LASRException:
ERROR: Failed to derive a formatted value.
(errorCode=GENERIC_EXCEPTION, params=null)"
message = "faultCode:Server.Processing faultString:'com.sas.biv.exception.BivException : com.sas.lasr.LASRException:
ERROR: Failed to derive a formatted value. (errorCode=GENERIC_EXCEPTION, params=null)' faultDetail:'null'"
name = "Error"
rootCause = (Object)#1
cause = (null)
errorResponseCode = "GENERIC_EXCEPTION"
localizedMessage = "com.sas.lasr.LASRException: ERROR: Failed to derive a formatted value.
(errorCode=GENERIC_EXCEPTION, params=null)"
message = "com.sas.lasr.LASRException: ERROR: Failed to derive a formatted value.
(errorCode=GENERIC_EXCEPTION, params=null)"
params = (null)
suppressed = (Array)#2
null
Supported Microsoft Excel versions and files for importing
Supported
Note: XLSB and XLSM files are supported beginning with release 7.1.
- XLSX files.
- XLS files created in Microsoft Excel 2007 and later that do not use DBCS characters.
- XLSB files (Windows only). For other limitations related to this file type, see the SAS Visual Analytics: User's Guide .
- XLSM files.
Not supported
- XLS files generated from SAS® Web Report Studio.
- XLS files created prior to Microsoft Excel 2007.
- XLS files that contain DBCS characters, regardless of the Excel version.
- XLST files or any other Excel file type.
Note: For links to all of the available documentation for SAS Visual Analytics, see the SAS Visual Analytics Product Documentation page.
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS Visual Analytics | Microsoft® Windows® for x64 | 6.3 | 9.4 TS1M1 | ||
| Linux for x64 | 6.3 | 9.4 TS1M1 | ||||
| Type: | Problem Note |
| Priority: | medium |
| Date Modified: | 2016-01-21 10:06:07 |
| Date Created: | 2014-03-24 11:25:21 |