How to import excel which has long text in one cell

[mhollifi]

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

[SuryaKiran]

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

[Reeza]

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?

[mhollifi]

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;

[SuryaKiran]

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

[mhollifi]

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.

[Tom · Accepted solution]

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;