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!
Content-Type: text/plain; charset="us-ascii"
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
libname exbk excel 'translations.xls';
SET exbk.'Sheet1$'n (DBSASTYPE=(Language='char(1024)'));
DDS Data Extraction
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
> 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
> fields (>255 characters).
> William W. Viergever suggested DDE, which seemed to work for him. He
> me how I could establish that the fields were truncated at 255
> Well, I showed them (not with PROC PRINT, which truncates the values
> characters, but) with PUT, with CALL SYMPUT and subsequent %PUT, the
> VIEWTABLE (which does not allow changing row heigth with the mouse)
> Enterprise Guide. I did not try DDE (yet), but thanks Bill!
> Arthur Tabachneck suggested to insert a top row of long bogus fields,
> 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
> relief! Indeed TEXTSIZE and SCANTEXT don't provide the solution on
> own (see also below).
> Paul Choate suggested to increase the TypeGuessRows in the Windows
> for the Microsoft Jet Engine to ffff or 65535. This would cause Proc
> to scan until the last non-empty data row in the workbook to determine
> the column width. It's under
> \Engines\Excel\TypeGuessRows . I did not try that solution (yet) as
> program should run fine on all machines without altering the registry.
> Thanks anyway Paul!
> Mark Payne (privately) suggested me to look at
> which handles the IMPORT WIZARD (I want working code to run in batch),
> discusses the 255 limit problem in relation to SAS scanning only the
> 8 records of the .xls file. The article (by Patrick G. Cunningham)
> to solutions suggested by SAS itself in
> www.sas.com/service/techsup/unotes/SN/006/006123.html, which in its
> discusses the possible values of TypeGuessRows in the Windows
> 0..16, 0 indicating 16384, virtually unlimited. Thanks a lot Mark!
> Chang Chung (also privately) explained to me PROC IMPORT seems to set
> length as the min(maximum string length in the column, textsize). To
> impression that may be very true, but only after SAS has correctly
> determined (scanned) the maximum string length from the first few
> 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
> are good for! Possibly something for SAS birdies to answer. Anyway
> you Chang!
> To my impression reading Excel files (at least via PROC IMPORT) is
> risky adventure, that has not yet been designed optimally by the SAS
> developers. I do understand that partially, Excel files have no
> with column metadata, it varies between cells! But how does a program
> DBMS/copy or StatTransfer read and interprete Excel files? (I did not
> DBMS/copy yet, I want everything be done with SAS code.)
> Regards - Jim.
> Jim Groeneveld, Netherlands
> Statistician, SAS consultant
> P.S. And (of course!) I received a few o-o-o messages on this
> On Thu, 6 Apr 2006 10:27:12 -0400, Jim Groeneveld
> >Hi friends,
> >I am reading an Excel file with at least one cell (of column
> >more than 255 characters (probably around 750 characters). But I get
> >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.;
> >PROC CONTENTS says the format and informat of Language both are
> >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
> >increase this length?
> >Regards - Jim.
> >Jim Groeneveld, Netherlands
> >Statistician, SAS consultant