Collapsing Columns in Excel: Handling Empty Values with a Talend tMap

If you're generating Talend code in Java, you can use the tertiary operator (the old C programming language 3-argument operator) to handle empty values in an Excel spreadsheet.

If you create a Talend job with a tMap component that outputs its result to an Excel spreadsheet, you may find that the spreadsheet columns collapse if all the values aren't available in a given record.  For example, the following spreadsheet was generated from input that did not require "Street Address 1" and "Street Address 2".  The result of the Talend run is a spreadsheet that consolidates the record, resulting in values being misaligned with their columns.

In the tMap component, each non-required field needs to be surrounded with a null check expression, returning an empty string if the value is not found.  Here's the Talend expression followed by a screen shot of the output schema in the tMap.

(row1.Street_Address_1 == null) ? "" : row1.Street_Address_1


If you're not familiar with the question mark (?) and colon (:), this statement reads "If Street Address 1 is null, then return an empty string.  Otherwise, return Street Address 1."  The part following the question mark is the "then" and the part following the colon is the "else".

After running the job, here is the resulting spreadsheet with the values correctly aligned with their columns.  Empty string ("") hold the values in their proper position.

As a standard practice it's a good idea to check for null values for any non-required columns in Talend.  This keeps the jobs simple as all of the null handling is wrapped up in a single tMap.