DATA Step, Macro, Functions and more

How to import excel which has long text in one cell

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

How to import excel which has long text in one cell

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_ManagersChar255$255.$255.All Managers
All_Managers_Role_DescriptionChar255$255.$255.All Managers_Role Description

Accepted Solutions
Solution
‎04-06-2018 06:02 PM
Super User
Super User
Posts: 8,290

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

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;

View solution in original post


All Replies
PROC Star
Posts: 635

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

[ Edited ]

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

 

Thanks,
Suryakiran
Super User
Posts: 24,028

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

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?

Contributor
Posts: 41

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

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;

 

PROC Star
Posts: 635

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

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

Thanks,
Suryakiran
Contributor
Posts: 41

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

[ Edited ]
Posted in reply to SuryaKiran

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.

 

Solution
‎04-06-2018 06:02 PM
Super User
Super User
Posts: 8,290

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

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 188 views
  • 2 likes
  • 4 in conversation