WbExport - append in XLSX

[Darshak]

Hi,

I need to append SQL (daily) output in existing worksheet OR else as new worksheet in same XLSX file.

I tried below commands 

~~~~~~~~~~~~~~~~~~~~~

WbExport -type=xlsx
         -file=myfile.xlsx
         -trimCharData=true
         -header=true
         -autoColWidth=true;
SELECT CURRENT TIMESTAMP tsp,
       CURRENT timezone zn
FROM sysibm.sysdummy1;

Result - data overwritten in SQLExport worksheet

~~~~~~~~~~~~~~~~~~

WbExport -type=xlsx
         -file=myfile.xlsx
         -append=true
         -trimCharData=true
         -title=DTTM
         -targetSheet=1
         -header=true
         -autoColWidth=true;
SELECT CURRENT TIMESTAMP tsp,
       CURRENT timezone zn
FROM sysibm.sysdummy1;

Result - data overwritten in DTTM worksheet

~~~~~~~~~~~~~~~~~~~~~~~

WbExport -type=xlsx
         -file=myfile.xlsx
         -append=true
         -trimCharData=true
         -title=DTTM
         
         -header=true
         -autoColWidth=true;
SELECT CURRENT TIMESTAMP tsp,
       CURRENT timezone zn
FROM sysibm.sysdummy1;

Error : The workbook already contains a sheet named 'DTTM'

~~~~~~~~~~~~~~~~~~~~~~~~~~~

Can you please guide which parameter I am missing for said purpose ?

Thanks,

 Darshak

[Thomas Kellerer]

If you provide -targetSheet then -append can't be used.

But if you append a new sheet, that sheet must have a unique name.
So you need to provide one that doesn't exist.

Currently there is no feature that automatically selects a unique name, but it's probably a good idea to do that automatically when -append=true is used.

You can generate a name using a variable with e.g. a timestamp in it:

-- Postgres syntax
WbVarDef date=@"select to_char(current_date, 'yyyy_mm_dd_hh24miss')";

WbExport -type=xlsx
-file=myfile.xlsx
-trimCharData=true
-header=true

-append=true
-sheetName="Export $[date]"
-autoColWidth=true;
select ....
from ...;



Thomas


Darshak schrieb am 14.12.2022 um 14:50:
> Hi,
>
> I need to *append *SQL (daily) output in existing worksheet OR else as new worksheet in same XLSX file.

>
> I tried below commands 
> ~~~~~~~~~~~~~~~~~~~~~
> WbExport -type=xlsx
>          -file=myfile.xlsx
>          -trimCharData=true
>          -header=true
>          -autoColWidth=true;
> SELECT CURRENT TIMESTAMP tsp,
>        CURRENT timezone zn
> FROM sysibm.sysdummy1;
>

> *Result *- data overwritten in SQLExport worksheet
>
> ~~~~~~~~~~~~~~~~~~
>
> WbExport -type=xlsx
>          -file=myfile.xlsx
>          *-append=true*

>          -trimCharData=true
>          -title=DTTM
>          -targetSheet=1
>          -header=true
>          -autoColWidth=true;
> SELECT CURRENT TIMESTAMP tsp,
>        CURRENT timezone zn
> FROM sysibm.sysdummy1;
>

> *Result *- data overwritten in DTTM worksheet

>
> ~~~~~~~~~~~~~~~~~~~~~~~
>
> WbExport -type=xlsx
>          -file=myfile.xlsx
>          -append=true
>          -trimCharData=true
>          -title=DTTM
>          
>          -header=true
>          -autoColWidth=true;
> SELECT CURRENT TIMESTAMP tsp,
>        CURRENT timezone zn
> FROM sysibm.sysdummy1;
>

> *Error *: The workbook already contains a sheet named 'DTTM'

[Darshak]

Thanks, I will use said approach.