Serialize as CSV

[Rickvzanden]

Hi

I am looking to export my JSON data as CSV with the serializer.
But when I look at the list of options the only thing that comes near is the XLSX version.
Unfortunately it has to be CSV and nothing else.

When I look at some documentation on the forum it shows that CSV should have been an option:

But its not there in the list for mappers or the “Write to filesystem” option.

Can someone explain to me how I can serialize my data to CSV within Alumio?

[Gugi]

Hi Rick,

We apologize that unfortunately we don’t have a serializer for CSV yet at the moment. We will be sure to pass this on to the team to see whether we can implement it in the future.

In the meanwhile, you can serialize your Alumio using the existing transformers. For example, you have the below data.

{
  "data": [
    {
      "id": 1,
      "name": "foo",
      "org": "example"
    },
    {
      "id": 2,
      "name": "bar",
      "org": "example"
    },
    {
      "id": 3,
      "name": "baz",
      "org": "example"
    }
  ]
}

And, you want to serialize the data in the data property into the below CSV.

1,foo,example
2,bar,example
3,baz,example

In order to do the above transformation, you would need a Value Mapper with “List: Implode to string” mapper first to separate each field/column of every row.

The output of the above Value Mapper is below.

{
  "data": [
    "1,foo,example",
    "2,bar,example",
    "3,baz,example"
  ]
}

Now, you should merge the lines into a string, but a new line separator is needed between every line. You can do it using a Value Setter with a help of JMESPath function.

This way, the property data will have the CSV-serialized value.

You can also add enclosures like " (double-quotes) or escapes like \ (backslash) by modifying the above transformers.

Feel free to give it a try.

[Rickvzanden]

Hi Gugi, thanks for you reply.

I though about doing something like this, but will I be able to write it to our SFTP filesystem with the current options available there?

Edit:
I also run into issue with this begin a multi type file wile where I need to keep integers and string separated. Imploding with “,” will cause everything marked as strings and without as integers.

Also using the james_path suggested I am getting the following error when trying out in the transformer tester:

JmesPath\SyntaxErrorException: Syntax error at character 5
join['\n', mapped_data]
     ^
Expected one of the following: number, colon, star; 

See screenshot for testing setup:

[Gugi]

Hi Rick,

@Rickvzanden

I though about doing something like this, but will I be able to write it to our SFTP filesystem with the current options available there?

You should be able to put the transformers as the last transformers in the outgoing configuration (before the entity enters the publisher).

@Rickvzanden

I also run into issue with this begin a multi type file wile where I need to keep integers and string separated. Imploding with “,” will cause everything marked as strings and without as integers.

I think you can enclose the fields that should be parsed as string with double-quotes. You can put the below transformer before the first Value Mapper in order to enclose string values with double-quotes.

This way, you should keep using , as the glue for the “List: Implode the string” mapper.

@Rickvzanden

Also using the james_path suggested I am getting the following error when trying out in the transformer tester:

JmesPath\SyntaxErrorException: Syntax error at character 5
join['\n', mapped_data]
     ^
Expected one of the following: number, colon, star; 

We are sorry for not putting the syntax in a text for easier reading. Please find the syntax below.

&{join(`\n`, data)}

The \n (new line) is enclosed in ` (backticks).

[Rickvzanden]

@Gugi

Thank you for your response and the solution.
With your last feedback I got it to work also with the export to the SFTP.
Although a serializer to CSV as a mapping option would be great to have in the future, this solves my problem for now.

Many thanks!

[Gugi]

We are glad to hear that you managed to get it worked, and the problem has been solved.

Yes, we passed your feedback earlier to the team. We will be sure to let you know if we have an update.

It was our pleasure to help you!

[Alumio_Bot]

Interesting, serialize and dictionary topics had each other contents.
I fixed that. I’ve also linked to this topic for ‘csv’ for now.

[Ties]

Small follow-up question regarding this topic; I did manage to generate a CSV succesfully but I was wondering if I’m able to set the headers of the CSV.
Is this possible?

[Gugi]

If you are following the steps I explained above (Serialize as CSV - #2 by Gugi), you should add the headers, comma-separated, as the first line (string) of the array.

{
  "data": [
    "id,name,org",
    "1,foo,example",
    "2,bar,example",
    "3,baz,example"
  ]
}

You can set up the comma-separated header in a separate array and merge it with the data using Operator Transformer → Array Merge, such as below.