Exporting text file with CSV formatting, but with different numbers of fields

[RoyB]

I’m working on a pipeline that needs to create and send a text file to a third party

That text file contains rows formatted in the same was as a csv file
The file contains a header row, then the data rows, then a footer row
The header and footer rows only have 6 columns, and I cannot send it with more or less columns, because their system will reject it
the data rows have 33 columns, and I cannot send it with more or less columns, because their system will reject it

Here is my pipeline:

The first section has 3 SQL Server Execute snaps that get the 3 types of rows, then I union them all together. The select contains 2 fields that cannot be in the resulting text file, and I only need them for the sort snap, to get the rows in the correct order

The mapper after to Sort snap is to remove the 2 sort columns
The problem I get here is, if I leave null safe access unchecked, then it freaks out because Col6 to Col33 does not exist in 2 of the rows, and if I check null safe access, it creates 6 to 33 in those 2 rows and adds to many fields to those 2 rows in the text file

Is there any way to:
A) Remove the 2 fields without using a mapper
B) Remove the resulting null valued fields after the mapper
OR
C) Tell the csv formatter to not create the field if it has a null value

Thanks

[alchemiz]

If you already have a flatten data and want to filter out objects that has a null value, try using the filter method in the root object

Expression	Target Path
$.filter((val,key)=> !(val instanceof Null))	$

[bojanvelevski]

Hey @RoyB,

I have very similar case like yours at the moment, and what am I doing is creating the CSV format on my own, and than I’m using the CSV formatter just to combine the rows and create the output to be ready for writing. Explanation:

Use a mapper to create the CSV format by using the following expression:

$.values().join(',')

With this you’ll end up with one record which is actually one row in the csv file. You can easily keep the sorting field, sort the lines after this, and than just remove them at the end.

[andrew_wait]

I know I’m late to the party, but I had to do this, found this posting and then solved it this way

FileWriter allows you to append, so

6 columns > CSV (no headers) > FileWriter (overwrite)
33 columns > CSV (no headers) > FileWriter (append)
6 columns > CSV (no headers) > FileWriter (append)

This keeps all the CSV creation so you get the “” wrapping but allows you to build up the file as required.

NOTE: FileWriter is fussy about the target location for allowing append, but if you can jump that hurdle you should be fine