Problem Note 43060: The DROP TABLE statement in the SQL procedure only clears the first 65,536 rows in Excel
The first 65,536 rows of a table are cleared when you use the DROP TABLE statement to clear a workbook in Excel. Excel workbooks can contain more than a 1 million rows when they are stored in .XLSX, .XLSB, or .XLSM formats. The following code example illustrates this problem. In this example, the worksheet has 100,000 rows. However, only the first 65,536 rows are cleared.
libname test excel 'c:\ExcelFiles\myonehundredk.xlsx';
proc sql;
drop table test.'bigone$'n;
quit;
The problem occurs because of a defect in the Microsoft Access Connectivity Engine, and a case has been opened with Microsoft. To circumvent the problem, do one of the following:
Operating System and Release Information
SAS System | SAS/ACCESS Interface to PC Files | Microsoft® Windows® for 64-Bit Itanium-based Systems | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Datacenter 64-bit Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Enterprise 64-bit Edition | 9.2 TS2M0 | |
Microsoft Windows XP 64-bit Edition | 9.2 TS2M0 | |
Microsoft® Windows® for x64 | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Datacenter Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Enterprise Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 Standard Edition | 9.2 TS2M0 | |
Microsoft Windows Server 2003 for x64 | 9.2 TS2M0 | |
Microsoft Windows Server 2008 for x64 | 9.2 TS2M0 | |
Microsoft Windows XP Professional | 9.2 TS2M0 | |
Windows Vista | 9.2 TS2M0 | |
Windows Vista for x64 | 9.2 TS2M0 | |
*
For software releases that are not yet generally available, the Fixed
Release is the software release in which the problem is planned to be
fixed.
When you use a DROP TABLE statement against a Microsoft Excel worksheet, only the first 65,536 rows are cleared. The probelm occurs because of a defect in the Microsoft Access Connectivity Engine.
Type: | Problem Note |
Priority: | medium |
Date Modified: | 2011-04-27 15:48:00 |
Date Created: | 2011-04-25 11:21:55 |