topic Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full in SAS Programming

Errror:cli execute microsoft odbc excel driver spreadsheet is full

rinsabraham — Thu, 21 Feb 2019 07:56:42 GMT

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??

Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full

andreas_lds — Thu, 21 Feb 2019 07:59:40 GMT

@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.

Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full

rinsabraham — Thu, 21 Feb 2019 08:28:13 GMT

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

Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full

rinsabraham — Thu, 21 Feb 2019 09:22:26 GMT

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

Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full

RW9 — Thu, 21 Feb 2019 09:50:37 GMT

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.

Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full

rinsabraham — Thu, 21 Feb 2019 11:17:55 GMT

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..

Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full

RW9 — Thu, 21 Feb 2019 11:55:40 GMT

"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).

Re: Errror:cli execute microsoft odbc excel driver spreadsheet is full

rinsabraham — Thu, 21 Feb 2019 13:26:14 GMT