Skip to main content

What format should JSON be to convert a single set of data into a CSV file for FTP?



  • Scott Henderson CTO
    Answer Pro
    Top Contributor

    I think the easiest format is an array of objects (and no 'one to many').


    {"field1": "value1", "field2": "value2"},

    {"field1": "value3", "field2": "value4"}


    If this is not working, then please provide more details in your post.  For example, please share a screenshot of your import configuration, field -> column mappings, the resulting output file vs the expected output file, etc...

  • Randy McDowell

    thanks for your help Scott, this still doesn't seem to work.

    Here's the preview from my transformation after the export from BigQuery...

    Here's my import set up for FTP...


    The CSV generator helper doesn't seem to recognize the input data...

    But the mapper does...

    A file is created and sent to S3, but contains only a header and single blank line...

    I am a rookie :-) What am I doing wrong?

  • Scott Henderson CTO
    Answer Pro
    Top Contributor

    This is tricky to explain here, and it might help to call support for more info, but it looks like the core issue is that each single "record" in your transformation script is being represented by an array of objects; and it would be much easier if each single record was just a single object.

    At a very high level, a transformation script always executes on a single record at a time, and if you see an array of objects in your transformation input, then your export might not be setup in the best way possible.

    Here are some things to consider.

    1. Did you set the "Path to records in HTTP response body" field so that knows where the records are in an API response vs treating the entire API response as a single record?
    2. Instead of using a "Transformation" -> "JavaScript" to change the BigQuery export data, can you use a "Pre save page" hook, and then in your hook code make sure the data that you return is a simple array of objects. Hooks are different than transformations in that they always operate on arrays of objects (i.e. pages of records), and this is where you can reshape multiple records at once and remove complicated structures that span multiple records, etc... Here is some sample code to help.

    function preSavePage (options) {
      return {
        data: [ {"field1": "value1", "field2": "value2"},  {"field1": "value3", "field2": "value4"}  ]




Please sign in to leave a comment.