Using variables with CVTDBFXL

A question frequently asked is 'How do I use a variable with the CoolSpools CVTDBFXL command'.

These examples demonstrate the CVTDBFXL command but similar methods can be used with any of the CoolSpools CVT* commands.

Here are some examples:

1. Pass the variable as a parameter:

CoolSpools commands are like any other iSeries command, variables can be passed as parameters. In this example the email address is a variable

DCL        VAR(&EMAILADDR) TYPE(*CHAR) LEN(50)
                                              
CHGVAR     VAR(&EMAILADDR) +                  
             VALUE(support\ariadnesoftware.co.uk)

CVTDBFXL   FROMFILE(QIWS/QCUSTCDT) +            
             TOSTMF('/customer/example.xlsx') +
             STMFOPT(*REPLACE) EMAIL(*YES) +    
             EMAILOPT(*NO 'CVTDBFXL example') + 
             EMAILTO((&EMAILADDR)) EXCEL(*XLSX) 

2. Use a CoolSpools Variable.

This is similar to the previous method but uses a CoolSpools variable. The benefit of this is that the variable can be used multiple times within the command

DCL        VAR(&EMAILADDR) TYPE(*CHAR) LEN(50)
                                              
CHGVAR     VAR(&EMAILADDR) +                  
             VALUE(support\ariadnesoftware.co.uk)

CVTDBFXL   FROMFILE(QIWS/QCUSTCDT) +                 
             TOSTMF('/customer/example.xlsx') +  
             STMFOPT(*REPLACE) EMAIL(*YES) +         
             EMAILOPT(*NO 'CVTDBFXL example') +    
             EMAILTO(('<:ADDRESS:>')) +
             EXCEL(*XLSX) OPTIONS(('<:ADDRESS:>'  &EMAILADDR))   

Note: this approach can be used to pass values into a CoolSpools Database to excel map, with the Coolspools variable being included in the map.

3. Use a CoolSpools Variable 2.

This is another example of using CoolSpools variables. It uses two CoolSpools variables, with <:STATE:> being included in a SQL select an as before in the email subject text.

DCL        VAR(&EMAILADDR) TYPE(*CHAR) LEN(50)

DCL        VAR(&STATE) TYPE(*CHAR) LEN(2)
                                              
CHGVAR     VAR(&EMAILADDR) +                  
             VALUE(support\ariadnesoftware.co.uk)

CHGVAR     VAR(&STATE) VALUE('MN')

CVTDBFXL   FROMFILE(*SQL) +                            
             TOSTMF('/customer/example.xlsx') +    
             STMFOPT(*REPLACE) SQL('select * from qiws/qcustcdt where state = ''<:STATE:>''') +
             EMAIL(*YES) EMAILOPT(*NO +
             'CVTDBFXL example state = <:STATE:>') +   
             EMAILTO(('<:ADDRESS:>')) EXCEL(*XLSX) +   
             OPTIONS(('<:ADDRESS:>' &EMAILADDR) +      
             ('<:STATE:>' &STATE))                     

Note: there are a number of predefined CoolSpools variables, details of these can be found here.