Pentaho Kettle - Generate Excel Output With Empty Rows

Question

I am new to Pentaho kettle.

I need to generate a microsoft excel output using the result set of a Table Input step.

I am using the following steps in my transformation

Get system Data - To provide the date range for the table data to retrieve
Table Input - SQL to retrieve the table data based on the date range given in prior step
Excel output - To generate an excel with the resultset of step2.

My excel output should have 3 empty rows before the Column header. so i have tried using union to generate empty rows and constructed the column header also using the sql. but kettle wont apply the formatting on fields if i select empty rows in sql(All field values will be considered as String).

Is there anyway to generate empty rows in the excel output before column header, without disturbing my result set.

SELECT 
    '' AS 'Accounting Month',   '' AS 'Insured Name','' AS 'Policy Number','' AS 'Company Name','' AS 'Line Of Business','' AS 'Transaction Type',  '' AS 'Effective Date','' AS 'Rate','' AS 'Gross Premium','' AS 'Commission Amount',    '' AS 'Expense Constant',   '' AS 'Net Amount','' AS 'Payment Amount', 1 as ORDERS
UNION ALL

SELECT 
    '' AS 'Accounting Month',   '' AS 'Insured Name','' AS 'Policy Number','' AS 'Company Name','' AS 'Line Of Business','' AS 'Transaction Type',  '' AS 'Effective Date','' AS 'Rate','' AS 'Gross Premium','' AS 'Commission Amount',    '' AS 'Expense Constant',   '' AS 'Net Amount','' AS 'Payment Amount', 1 as ORDERS
UNION ALL

SELECT 
    '' AS 'Accounting Month',   '' AS 'Insured Name','' AS 'Policy Number','' AS 'Company Name','' AS 'Line Of Business','' AS 'Transaction Type',  '' AS 'Effective Date','' AS 'Rate','' AS 'Gross Premium','' AS 'Commission Amount',    '' AS 'Expense Constant',   '' AS 'Net Amount','' AS 'Payment Amount', 1 as ORDERS
UNION ALL


SELECT 
    '' AS 'Accounting Month',   '' AS 'Insured Name','' AS 'Policy Number','' AS 'Company Name','' AS 'Line Of Business','' AS 'Transaction Type',  '' AS 'Effective Date','' AS 'Rate','' AS 'Gross Premium','' AS 'Commission Amount',    '' AS 'Expense Constant',   '' AS 'Net Amount','' AS 'Payment Amount', 3 as ORDERS
UNION ALL

SELECT 
 'Accounting Month',
 'Insured Name',
 'Policy Number',
 'Company Name',
 'Line Of Business',
 'Transaction Type',
 'Effective Date',
 'Rate',
'Gross Premium',
'Commission Amount',
'Expense Constant',
'Net Amount',
'Payment Amount', 
2 ORDERS

UNION ALL

SELECT * FROM (
SELECT 
        CONCAT(ACCOUNT_YEAR,ACCOUNT_MONTH) AS 'Accounting Month',
        INSURED_NAME AS 'Insured Name',
        POLICY_NUMBER AS 'Policy Number',
        COMPANY AS 'Company Name',
        LINE_OF_BUSINESS AS 'Line Of Business',
        TRANSACTION_DETAIL_TYPE 'Transaction Type',
        DATE_FORMAT(CHANGE_EFFECTIVE_DATE,'%m/%d/%Y') AS 'Effective Date',
        OWNER_COMMISSION_RATE AS 'Rate',
        GROSS_PREMIUM AS 'Gross Premium',
        OWNER_COMMISSION_AMOUNT AS 'Commission Amount',
        EXPENSE_CONSTANT AS 'Expense Constant',
        NET_AMOUNT AS 'Net Amount',
        NET_AMOUNT AS 'Payment Amount' ,
        4 ORDERS
        FROM TABLE1
        WHERE           
        DATE(TRANSACTION_ENTRY_DATE) >=?
        AND 
        DATE(TRANSACTION_ENTRY_DATE) <=?
        ORDER BY TABLE1.POLICY_NUMBER,TABLE1.FLAG,TABLE1.ENDORSEMENT_NUMBER
)T
ORDER BY ORDERS;

Codek Codek · Accepted Answer · 2012-12-12T17:40:12

I See 2 options:

Use the select values step to properly change your columns from strings to integers or whatever.
Use Cast('' as Integer) or similar to make sure that the SQL query returns the columns as the correct data type.

I would have thought your union query would generate an error if you had different data types at different parts of the union anyway!? So perhaps something else is going on here?

Pentaho Kettle - Generate Excel Output With Empty Rows

2 Answers