Problem Note 59005: The XLSX engine does not read all the rows in a Microsoft Excel spreadsheet when you use the LIBNAME statement
 |  |  |  |
The XLSX engine might fail to read all the rows in a Microsoft Excel file. For example, this problem occurs when you perform steps similar to the following:
- Create an Excel file and write to it using the following code:
- Open the workbook in Excel and add observations to the bottom of the spreadsheet.
- Run this code to read the spreadsheet into your SAS® software:
libname test xlsx "c:\sastest\mystuff.xlsx";
data test.air;
set sashelp.air;
run;
libname test xlsx "c:\sastest\mystuff.xlsx";
data mystuff;
set test.air;
run;
libname test clear;
When you read the spreadsheet into SAS, you see that the added observation rows are missing.
The problem occurs because the LIBNAME statement creates a named range in the workbook. When you add observations to the bottom of the spreadsheet, the XLSX engine adds them outside of the named range. The XLSX engine always searches for the named range first. If the named range is present, the XLSX engine reads it instead of the full worksheet.
To circumvent the problem, use the IMPORT procedure instead of the LIBNAME statement as shown below:
proc import datafile='c:\sastest\mystuff.xlsx'
out=sasdata
dbms=xlsx replace;
sheet=air;
run;
Operating System and Release Information
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SAS System | SAS/ACCESS Interface to PC Files | Microsoft® Windows® for x64 | 9.4_M2 | 9.4 TS1M2 | ||
| Microsoft Windows 8 Enterprise 32-bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 8 Enterprise x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 8 Pro 32-bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 8 Pro x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 8.1 Enterprise 32-bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 8.1 Enterprise x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 8.1 Pro 32-bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 8.1 Pro x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows 10 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows Server 2008 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows Server 2008 R2 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows Server 2008 for x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows Server 2012 Datacenter | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows Server 2012 R2 Datacenter | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows Server 2012 R2 Std | 9.4_M2 | 9.4 TS1M2 | ||||
| Microsoft Windows Server 2012 Std | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Enterprise 32 bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Enterprise x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Home Premium 32 bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Home Premium x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Professional 32 bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Professional x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Ultimate 32 bit | 9.4_M2 | 9.4 TS1M2 | ||||
| Windows 7 Ultimate x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| 64-bit Enabled AIX | 9.4_M2 | 9.4 TS1M2 | ||||
| 64-bit Enabled Solaris | 9.4_M2 | 9.4 TS1M2 | ||||
| HP-UX IPF | 9.4_M2 | 9.4 TS1M2 | ||||
| Linux for x64 | 9.4_M2 | 9.4 TS1M2 | ||||
| Solaris for x64 | 9.4_M2 | 9.4 TS1M2 | ||||
The XLSX engine might not read all the rows in an Excel spreadsheet. The problem is especially likely to occur if a LIBNAME statement is used. To circumvent the problem, use the IMPORT procedure.
| Type: | Problem Note |
| Priority: | high |
| Date Modified: | 2016-10-18 08:18:06 |
| Date Created: | 2016-09-19 12:07:11 |