PROC IMPORT from Excel truncating text field at 255 chars

[CurtisMack]

Hi all,

I am trying to read in a sheet from an Excel file and it is truncating one of the text columns to 255 characters. This is despite having TEXTSIZE set to 1000 and SCANTEXT = YES. The system returns the following warning:

WARNING: Failed to scan text length or time type for column Survey Question for Tables.

Here is the code:

proc import datafile="\\devdata\e\HYS\Dropdowns - &DropDownVersion..xls"
out=crosswalk_master
dbms = excel
replace;
MIXED = no;
sheet = 'Master';
TEXTSIZE=1000;
SCANTEXT=YES;
run;

Has anybody seen this, or better yet figured out out to get arround it short of exporting the sheet to a CVS file or a similar manual step?

Thanks!
Curtis

[CurtisMack]

Well, I contacted SAS tech support and they had a solution which I will share for posterity.

It takes changing a couple of registry settings. Which ones depends on your OS, Version of Office, and version of SAS. For my Windows 7 64bit machine with Office 2007 and SAS 9.2, the TypeGuessRows value needed to be changed from 8 to 0.

HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Jet\4.0\Engines\Excel
HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\Office\12.0\Access Connectivity Engine\Engines\Excel

Tech support sent me the following. Note, that on my machine, the Jet key was in a different location.


Or to make this simpler you can follow these instructions here.

1. You bring up the registry edit by typing REGEDIT from a start +> run windows prompt.
2. Expand HKEY_LOCAL_MACHINE by clicking on the plus + sign on the left
3. Expand SOFTWARE
4. Expand MICROSOFT
5. Expand JET
6. Expand 4.0
7. Expand Engines
8. Expand Excel
9. Double click on the TypeGuessRows on the right.
10. Change the value from 8 to 0.

Then exit and you are done.

If you are running 9.2 TS2M0 or later you should change this one or these keys as well depending
on the microsoft office you have installed .

To change the registry key for office 2007:

In Windows 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.

If you are running 9.2 TS2M0 or later and have Office 2010 installed you should change this one instead.

To change the registry key:

In Windows environments, 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 on a Windows 7 or X64 system you need to go a slight different registry key located here....

for office 2007 change the key below:

HKEY_LOCAL_MACHINE => Software => Wow6432Node => Microsoft => Office => 12.0 => Access Connectivity Engine => Engines

For Office 2010 change the key below:

HKEY_LOCAL_MACHINE => Software => Wow6432Node => Microsoft => Office => 14.0 => Access Connectivity Engine => Engines Message was edited by: Curtis Mack