Forum Discussion

deeps's avatar
deeps
New Contributor III
3 years ago
Solved

Rest Get output is in filename.json.gz format need to convert to csv

Hello All, I have file called “filename.json.gz” which is obtained using REST GET Snap and I need to convert it to a CSV format file. I need this transformation to be done in one pipeline. Any h...
  • RoyB's avatar
    RoyB
    2 years ago

    figured it out. the ID field needs to be assigned to $original.ID

    The "orginal", which is case sensitive, lifts it into a sub-document in the json

    which is then ignored by the SET, but you are still able to include it in the WHERE

    (I had tried this already, but had typed it as Original, which didn't work, but revisited it once I found sometihng in the help for the SQL SERVER UPDATE Snap

Karthi's avatar
Karthi
New Contributor
2 years ago

Thanks for this post, This is Karthi, I am part of Data Engineering Team, we have Workday in our company, we are facing performance issue on downloading the WD web enabled URL in CSV file format.

We have SSIS ETL tool where we use script task (C#) to hit WD web enabled URL to download the files, last couple of month we are facing some performance issue in this task, some times this task is running longer, generally this will complete in 10 mins but sometime its running in Hours/days and timed out finally.

We checked with SQL Server side there server performance is good, but we are not able to find why this download part is running longer and timed out, it will really helpful if you have some suggestion on this, and is there any alternative way to download the WD reports data in file formats?   

 

  • walkerline117's avatar
    walkerline117
    Contributor
    7 years ago

    yes,sorry i didn’t use clear words, but you are right, what I mean is a document with an array of values.

  • walkerline117's avatar
    walkerline117
    Contributor
    7 years ago

    So in my use case, its simple: I have 5 input documents, each document is an array of values.
    e.g. the first array has 3 values, the 2 array has 2 values.

    after the CSV formatter, i want to see 5 documents/files( the first one should have 3 rows, the second one should have 2 rows…)

    The 5 documents comes out of a mapper snap, then I tried to put a CSV formatter snap behind the mapper, i noticed that the number of output of the CSV is not 5, but 1 document contains all values across all arrays.

    Then I tried to move just the CSV formatter to a subpipeline, I got the same result.

    • tstack's avatar
      tstack
      Former Employee
      7 years ago

      Since the CSV Formatter currently doesn’t work with arrays, you will need to use a child pipeline that splits the array into separate docs that go into a CSV Formatter.

      I’m attaching a couple of pipelines to demonstrate this flow:

      FormatArrayToCSV_2019_02_28.slp (5.6 KB)
      WriteCSVFiles_2019_02_28.slp (5.4 KB)

      The FormatArrayToCSV pipeline is the child. It splits the “$array” property in the incoming document and then passes the results to a CSV Formatter and FileWriter. You’ll need to change the JSON Splitter to split your array property.

      The WriteCSVFiles pipeline is the parent. It just contains some mock data and feeds it into the child pipeline.

      • walkerline117's avatar
        walkerline117
        Contributor
        7 years ago

        This really helps.

        Thanks!