topic Re: How to import excel which has long text in one cell in SAS Programming

How to import excel which has long text in one cell

mhollifi — Fri, 06 Apr 2018 20:41:30 GMT

I read that "A cell can contain no more than 32,767 characters. " But how to read them? I have long texts in cells. But when I read it using proc import, it cuts off in 255 characters. Some of the cells contain about 500 characters. Thank you for your help.

PROC IMPORT OUT= WORK.managers 
            DATAFILE= "e:\Users\mhollifi\Desktop\MaxWell read excel\pull
08to08.xlsx" 
            DBMS=EXCEL REPLACE;
     RANGE="Request3$"; 
     GETNAMES=YES;
     MIXED=NO;
     SCANTEXT=YES;
     USEDATE=YES;
     SCANTIME=YES;
RUN;

Alphabetic List of Variables and Attributes# Variable Type Len Format Informat Label845

All_Managers	Char	255	$255.	$255.	All Managers
All_Managers_Role_Description	Char	255	$255.	$255.	All Managers_Role Description

Re: How to import excel which has long text in one cell

SuryaKiran — Fri, 06 Apr 2018 21:03:10 GMT

Data type and length on variables using proc import are determined by guessing the rows. Use GUESSINGROWS=n higher number ( your expected count of records). By default it is 20, so it those 20 records it found that length and applied to all the records.

GUESSINGROWS=n can be applied only to delimited files.

As @Reeza mentioned change it to XLSX and try or convert your file to CSV and use GUESSINGROWS=n

Re: How to import excel which has long text in one cell

Reeza — Fri, 06 Apr 2018 20:53:22 GMT

Try a different DBMS, I believe the limitation is actually an Excel limitation in the transfer mechanism but it's been a while since I looked into this.

Try DBMS=XLSX

And worse case, can you convert the file to CSV and then import it?

Re: How to import excel which has long text in one cell

mhollifi — Fri, 06 Apr 2018 21:44:48 GMT

it doesn't work. When I click "import files" on the left it gives me only 'dbms =excel replace.' But when I change this to xlsx I got totally error message...

1    PROC IMPORT OUT= WORK.managerx
2                DATAFILE= "e:\Users\mhollifi\Desktop\MaxWell read excel\pull
3    08to08.xlsx"
4                DBMS=xlsx;
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
NOTE: The previous statement has been deleted.
5         RANGE="'Request 3$'";
NOTE: The previous statement has been deleted.
6         GETNAMES=YES;
NOTE: The previous statement has been deleted.
7         MIXED=NO;
          -----
          180
8         SCANTEXT=YES;
          --------
          180
9         USEDATE=YES;
          -------
          180
10        SCANTIME=YES;
          --------
          180
11        TEXTSIZE=2024;
          --------
          180
ERROR 180-322: Statement is not valid or it is used out of proper order.

12   RUN;

Re: How to import excel which has long text in one cell

SuryaKiran — Fri, 06 Apr 2018 21:49:07 GMT

Some options doesn't support when DBMS=XLSX, try deleting SCANTEXT,..etc

Re: How to import excel which has long text in one cell

mhollifi — Fri, 06 Apr 2018 21:59:01 GMT

Thank you for your reply. But it still not working. I tried to convert to csv file, which destroyed the data. It just cut off the multi-lines in one cell. So I need to keep xlsx. Thank you for your help

20   PROC IMPORT OUT= WORK.managerx
21               DATAFILE= "e:\Users\mhollifi\Desktop\MaxWell read excel\pull
22   08to08.xlsx"
23               DBMS=xlsx;
24        RANGE="'Sheet1$'";
25        GETNAMES=YES;
26        run;

Couldn't find range in spreadsheet
Requested Input File Is Invalid
ERROR: Import unsuccessful.  See SAS Log for details.

Re: How to import excel which has long text in one cell

Tom — Fri, 06 Apr 2018 21:58:59 GMT

If you want the first sheet then just don't specify anything.

Also try just use the XLSX libname engine.

libname in xlsx "e:\Users\mhollifi\Desktop\MaxWell read excel\pull08to08.xlsx";
proc copy inlib=in outlib=work;
run;