Errror:cli execute microsoft odbc excel driver spreadsheet is full

[rinsabraham]

Hi Team,

I have fetch the 1384026 records from the table and trying to export into .xlsx file.

Getting error:

Error: cli execute Microsoft odbc excel driver spreadsheet is full.

Code:

proc datasets lib=_ibxlsx;

delete data;

run;

proc append data=_ibxlsx.data base= abc;

run;

PROC EXPORT DATA= _ibxlsx.data
    OUTFILE= 'C:\excel files\data.xls'
    DBMS=EXCEL REPLACE;
     SHEET="data";
RUN;

I know the threshold of xlsx is 1048576.is it possible to increase the threshold value from sas to export.

can you please guide me??

[andreas_lds]

---

@rinsabraham wrote:

Hi Team,

I have fetch the 1384026 records from the table and trying to export into .xlsx file.

Getting error:

Error: cli execute Microsoft odbc excel driver spreadsheet is full.

Code:

proc datasets lib=_ibxlsx;

delete data;

run;

proc append data=_ibxlsx.data base= abc;

run;

PROC EXPORT DATA= _ibxlsx.data
    OUTFILE= 'C:\excel files\data.xls'
    DBMS=EXCEL REPLACE;
     SHEET="data";
RUN;

I know the threshold of xlsx is 1048576.is it possible to increase the threshold value from sas to export.

can you please guide me??

---

Right now you are creating an xls-file, limited to 65k rows, change dbms and file-extensions to xlsx.

[rinsabraham]

Thanks for the quick reply

 let me try and get back to you.

but one more doubt my colleguage tried to copy that file to some other location getting the same error.

code:

proc datasets lib=_ibxlsx;

delete data;

run;

proc append data=_ibxlsx.data base= abc;

run;

data _null_;

src== 'C:\excel files\data.xls';

dest== 'C:\excel files\out\data.xls';

if (fileexist (src)) then do

call system ('copy' ||quote(src) ||' ' ||quote(des));

end;

getting the same error

[rinsabraham]

I have changed the dbms  to xlsx and changed the file extension still getting the same error.

[RW9]

Please post your code and log, all we can see above is that you are exporting to XLS which is an ancient binary file format.  What operating system are you running on, how are you exporting the data etc.

[rinsabraham]

Sorry I can't paste the log because it is in client server.

operating system: windows 7

But now I have  removed the duplicate records so I got less than 1 million records so I can  exported the data now. previously I didn't remove the duplicate records due to which data has came up more than 1 million.

Thanks you for reply..

[RW9]

"Sorry I can't paste the log because it is in client server." - then how do you know what is going on?  Log is a fundamental things to see what is going on with the process.  

"But now I have  removed the duplicate records so I got less than 1 million records" - why on earth are you pushing out millions of records to a spreadsheet?  That is truly madness.  No-one will ever look at a million records in Excel, nor will that format be useful for anyone.  If you are transferring data then use CSV, Json, XML, trusted platform independent streamable data sources.  Excel is really not the tool for this (whatever your process is).

[rinsabraham · Accepted solution]

ok