Forum Discussion

RoyB's avatar
RoyB
New Contributor III
4 years ago

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

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

csv
mapper

7 Replies

  • RoyB's avatar
    RoyB
    New Contributor III
    4 years ago

    I’ve tried changing the mapper to do this instead:

    to strip out the 2 fields, and it does do it, but in the resulting text file the header row has only the 6 records I want, but the footer row has 33, presumably taking the additional columns from the preceding data row

  • RoyB's avatar
    RoyB
    New Contributor III
    4 years ago

    I’ve checked the JSON document coming out of the mapper, and the final row only has 6 columns, so its the csv formatter or something after that is adding the extra columns to the last row

  • RoyB's avatar
    RoyB
    New Contributor III
    4 years ago

    Thanks for the try, but it didn’t work.

    I modified my pipeline to use your method, and it does the same thing.
    The incoming document has a format of:
    6 columns
    33 columns (multiple rows)
    6 columns

    And then somewhere between the csv formatter and the File Write it turns into
    6 columns
    33 columns (multiple Rows)
    33 columns

    • alchemiz's avatar
      alchemiz
      Contributor III
      4 years ago

      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's avatar
    bojanvelevski
    Valued Contributor
    4 years ago

    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's avatar
    andrew_wait
    New Contributor
    3 years ago

    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