Re: Excel XLSX Generation, ACS on IBM i, and 0-rows select-statement

FYI

I asked directly to Scott Forstie in his Gist and here Scott's answer:

The cldownload plugin currently does not include an option to generate an
empty spreadsheet. The team requests that you open an RFE if you'd like the
support added.

For reference, there are some controls for column headers.

9.1.17 CLDownload
/PLUGIN=cldownload /system=
[/userid=]
{/hostfile=<library/filename> | /sql="statement"}
{/clientfile=. | /display}
[/]

/userid - user id to use when connecting to the target system
/hostfile - Source library and file on the IBM i system for the download
e.g. /hostfile=QIWS/QCUSTCDT
/sql - specify an SQL statement
e.g. /sql="select CUSNUM,LSTNAM,INIT,ZIPCOD from QIWS/QCUSTCDT"
/clientfile - Target file location for the download.
The format of this file will be determined by the specified
extension (for example, .csv .ods .xlsx .xlsx)
If the file extension is not specified or is of a type
not supported, the data will be formatted as a .csv file
/display - write the output to the terminal

Valid options are:
/colheadings=<1/0> - Include column headings as the first row. When
specified, the column names will be the heading.
/usecollabels - Use column labels for the heading.


I


Il giorno mar 10 nov 2020 alle ore 11:18 Roberto De Pedrini <
roberto.faq400@xxxxxxxxx> ha scritto:

I just changed some of my programs that generated CSV files (CPYTOSTMF and
so on) to get XLXS files using this Scott Forstie suggestion
Generating spreadsheet with SQL
(https://gist.github.com/forstie/2deda50658106461f650cd71917feff9)

Very good ... just a bit slowly for ACS job and java environment ... but
it works and i got my xlsx files

There only an annoying problem ... when my SQL statement returns 0 records
... ACS won't create the XLSX file.
Is there any setting to tell ACS

"Hey guy, don't worry about rows ... could you please give me back an xlsx
with my columns' headers?, thank you!"

maybe only in one-word parameter?


Thanks

Roberto


-- Create XLXS file from query SQL string

...[cut]

SET V_SQLSTMT='select * from sampledb.employee where 1=2';

SET CMDTEXT =
'STRQSH CMD(''java -jar /ACS/acsbundle.jar '
CONCAT '/plugin=cldownload /system=xxxxxx /clientfile='
CONCAT V_IFS_PATH
CONCAT ' /sql="'
CONCAT V_SQLSMT
CONCAT '"'')' ;

CALL QSYS2 . QCMDEXC ( CMDTEXT ) ;


--
--
Roberto De Pedrini - Faq400 Srl
http://blog.faq400.com/en *A new IBM i Blog from IBM i Italian
Community Faq400*