Problem Note 46472: Character strings can be truncated at 255 or 1,024 characters when you import Microsoft Excel files into SAS®
 |  |  |  |
Character strings imported from Excel can be truncated at 255 or 1,024 characters. The problem can have several causes.
Cause 1 - The Number of Rows Scanned by the Microsoft Access Connectivity Engine
SAS uses the Microsoft Access Connectivity Engine to read Excel files. The engine uses a TypeGuessRows registry key to sense how many rows to scan before determining the length of the column. By default, the engine uses 8 rows to do so but you can change this number using the following instructions:
If you are running SAS® 9.2 TS2M0 or later, the change that you make depends on the version of Microsoft Office that you have installed.
To change the registry key for Microsoft Office 2007, complete these steps:
- In Windows operating environments, select Start ► Run and type REGEDIT to display the Registry Editor.
- In the registry tree, select HKEY_LOCAL_MACHINE Software ► Microsoft ► Office ► 12.0 ► Access Connectivity Engine ► Engines.
- Double-click the Excel node.
- In the right panel, double-click the TypeGuessRows entry.
- Change the value data from 8 to 0.
- Click OK.
- Select File ► Exit to exit the Registry Editor window.
To change the registry key for Microsoft Office 2010, complete these steps:
- Under Windows, select Start ► Run and type REGEDIT to display the Registry Editor.
- In the registry tree, select HKEY_LOCAL_MACHINE ► Software ► Microsoft ► Office ► 14.0 ► Access Connectivity Engine ► Engines.
- Double-click the Excel node.
- In the right panel, double-click the TypeGuessRows entry.
- Change the value data from 8 to 0.
- Click OK.
- Select File ► Exit to exit the Registry Editor window.
If you are running under Windows 7 or X64, you need to go to a different registry key.
For Microsoft Office 2007, follow this path: HKEY_LOCAL_MACHINE ► Software ► Wow6432Node ► Microsoft ► Office ► 12.0 ► Access Connectivity Engine ► Engines
For Microsoft Office 2010 follow this path: HKEY_LOCAL_MACHINE ► Software ► Wow6432Node ► Microsoft ► Office ► 14.0 ► Access Connectivity Engine ► Engines
Cause 2 - The Length of the TEXTSIZE Parameter
If the string truncates at 1,024 characters, this means that the TEXTSIZE parameter is normally the limiting factor. The default length for the TEXTSIZE parameter is 1,024. This can be increased by adding TEXTSIZE=32767 to the IMPORT procedure or LIBNAME statement.
Cause 3 - The Format of Column Types in Excel
Other causes can actually be in Excel itself. Sometimes, selecting the Excel column and formatting the column as Text instead of General solves the problem.
Other Alternatives
If all of the above circumventions fail and you are running SAS® 9.3 TS1M1 or later, you can use the XLSX engine rather than the EXCEL engine. It automatically scans the lengths and is independent of the Microsoft Access Connectivity Engine. The following is sample code:
getnames=yes;
sheet='mysheet';
run;
Operating System and Release Information
| Product Family | Product | System | SAS Release | |
| Reported | Fixed* | |||
| 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 | |||