LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (April 2006, week 1)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
Date:         Fri, 7 Apr 2006 16:07:11 -0700
Reply-To:     "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Choate, Paul@DDS" <pchoate@DDS.CA.GOV>
Subject:      Re: Import Excel file with fields longer than 255 SOLVED!
Comments: To: Jim Groeneveld <jim2stat@YAHOO.CO.UK>
Content-Type: text/plain; charset="us-ascii"

Jim -

There is a very simple answer to your question - I'm a little post-SUGI catch-up scattered, or I would have mentioned it before. Your very nice wrap-up jogged my memory. The following works nicely but requires v9 and SAS/Access for PC File Formats.

Use the libname engine instead and define the DBSASTYPE as $1024. This set the length definition on the PDV so your Excel data is not truncated regardless of the scanned width of the data. You can define any or all columns thus. All you need is prior knowledge of the incoming data formats.

libname exbk excel 'translations.xls';

DATA translation; SET exbk.'Sheet1$'n (DBSASTYPE=(Language='char(1024)')); run;

hth

Paul Choate DDS Data Extraction (916) 654-2160

> -----Original Message----- > From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of Jim > Groeneveld > Sent: Friday, April 07, 2006 2:14 AM > To: SAS-L@LISTSERV.UGA.EDU > Subject: Re: Import Excel file with fields longer than 255 SOLVED! > > Hi friends, > > I got several replies to my query on reading Excel (.xls) files with long > fields (>255 characters). > > William W. Viergever suggested DDE, which seemed to work for him. He asked > me how I could establish that the fields were truncated at 255 characters. > Well, I showed them (not with PROC PRINT, which truncates the values to 91 > characters, but) with PUT, with CALL SYMPUT and subsequent %PUT, the SAS > VIEWTABLE (which does not allow changing row heigth with the mouse) and > Enterprise Guide. I did not try DDE (yet), but thanks Bill! > > Arthur Tabachneck suggested to insert a top row of long bogus fields, > which > would determine the fields lengths. I did that as row 2 (the 1st row > contains field names) AND IT WORKED GREAT! Thanks Art, you really gave me > relief! Indeed TEXTSIZE and SCANTEXT don't provide the solution on their > own (see also below). > > Paul Choate suggested to increase the TypeGuessRows in the Windows > registry > for the Microsoft Jet Engine to ffff or 65535. This would cause Proc > Import > to scan until the last non-empty data row in the workbook to determine > the column width. It's under HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0 > \Engines\Excel\TypeGuessRows . I did not try that solution (yet) as the > program should run fine on all machines without altering the registry. > Thanks anyway Paul! > > Mark Payne (privately) suggested me to look at > http://wwwdata.forestry.oregonstate.edu/helpdesk/docs/statistics/Excel- > SASImport.pdf > which handles the IMPORT WIZARD (I want working code to run in batch), and > discusses the 255 limit problem in relation to SAS scanning only the first > 8 records of the .xls file. The article (by Patrick G. Cunningham) refers > to solutions suggested by SAS itself in > www.sas.com/service/techsup/unotes/SN/006/006123.html, which in its turn > discusses the possible values of TypeGuessRows in the Windows registry: > 0..16, 0 indicating 16384, virtually unlimited. Thanks a lot Mark! > > Chang Chung (also privately) explained to me PROC IMPORT seems to set the > length as the min(maximum string length in the column, textsize). To my > impression that may be very true, but only after SAS has correctly > determined (scanned) the maximum string length from the first few records. > Further tests, now with very long fields in record 2, pointed out that > SCANTEXT and TEXTSIZE do not have any effect at all! I wonder where they > are good for! Possibly something for SAS birdies to answer. Anyway thank > you Chang! > > To my impression reading Excel files (at least via PROC IMPORT) is still a > risky adventure, that has not yet been designed optimally by the SAS > developers. I do understand that partially, Excel files have no headers > with column metadata, it varies between cells! But how does a program like > DBMS/copy or StatTransfer read and interprete Excel files? (I did not try > DBMS/copy yet, I want everything be done with SAS code.) > > Regards - Jim. > -- > Jim Groeneveld, Netherlands > Statistician, SAS consultant > home.hccnet.nl/jim.groeneveld > > P.S. And (of course!) I received a few o-o-o messages on this > subject....... > [:-( > > > On Thu, 6 Apr 2006 10:27:12 -0400, Jim Groeneveld <jim2stat@YAHOO.CO.UK> > wrote: > > >Hi friends, > > > >I am reading an Excel file with at least one cell (of column Language) > with > >more than 255 characters (probably around 750 characters). But I get only > >the first 255 of them. My code is: > > > >PROC IMPORT OUT=Translations (KEEP=Variable Language) > > DATAFILE= "D:\Projects\[.........]\DATA\Translations.xls" > > DBMS=EXCEL REPLACE; > > SHEET="Sheet1$"; > > GETNAMES=YES; > > MIXED=NO; > > SCANTEXT=YES; > > TEXTSIZE=1024; > > USEDATE=YES; > > SCANTIME=YES; > > INFORMAT Language $1024.; FORMAT Language $1024.; > >RUN; > > > >PROC CONTENTS says the format and informat of Language both are $1024. > But > >it also says its length (Len) is 255. I have googled the internet (of > >course) and the SAS-L archives, but found nothing useful yet. How to I > >increase this length? > > > >Regards - Jim. > >-- > >Jim Groeneveld, Netherlands > >Statistician, SAS consultant > >home.hccnet.nl/jim.groeneveld


Back to: Top of message | Previous page | Main SAS-L page