topic Re: How to change the structure when converting from CSV file to json file in Talend Studio

How to change the structure when converting from CSV file to json file

Anonymous — Sat, 16 Nov 2024 04:54:06 GMT

Hi.

I am creating on converting CSV file to JSON file job.

My CSV Format:

Product,Price,Quantity
"ABC",100,10
"DEF",200,20

Json file format I need:

{
    "app" : 1,
    "records" : [  
            {
                "Product" : { "value" : "ABC"},
                "Price" : { "value" : 100},
                "Quantity" : { "value" : 10}    
            },
            {
                "Product" : { "value" : "DEF"},
                "Price" : { "value" : 200},
                "Quantity" : { "value" : 20}    
            }
    ]
}

I have completed this:

{
    "records" : [  
            {
                "Product" : "ABC",
                "Price" :  100,
                "Quantity" : 10    
            },
            {
                "Product" : "DEF",
                "Price" : 200,
                "Quantity" : 20    
            }
    ]
}

How can I add an "app" property to the route and include "value" property?

The “1” property data can be a static value.

My job setting (sorry, it's not the English version.):

All schemas are built-in and default settings.

Thank you.

Re: How to change the structure when converting from CSV file to json file

JR1 — Wed, 21 Aug 2019 19:08:55 GMT

OK, Let's do this. You can achieve this by doing it in two stages.

Build a job that looks like the one below. In the first tWriteJSONField you create a JSON object for each row (pay attention to all the settings!).

In a tMap you simply add a new column called "app" and map the output of the previous step to a column called "records".

Then move the output into another tWriteJSONField component and configure it as follows (the output has just one column named "content"):

Finally, store your output in a file (or do whatever you want to do with it.

When I put the following input into the tFixedFlowInput (Product is a String, Price is an Integer, Quantity is an Integer - of course you could also use a CSV file as input)

Product,Price,Quantity
"ABC",100,10
"DEF",200,20

I get the following output

{"app":1,"records":[{"product":{"value":"ABC"},"price":{"value":100},"quantity":{"value":10}},{"product":{"value":"DEF"},"price":{"value":200},"quantity":{"value":20}}]}

I hope this helps.

Re: How to change the structure when converting from CSV file to json file

Anonymous — Fri, 23 Aug 2019 06:00:45 GMT

Thanks for reply.

I tried it.

But, "Output Column" is empty.

Is something wrong?

tFixedFlowInput settings:

tFixedFlowInput schema settings:

Result of confirming tFixedFlowInput output with tLogRow(Table mode):

[statistics] connecting to socket on port 3712
[statistics] connected
.--------+-----+--------.
|       tLogRow_1       |
|=-------+-----+-------=|
|Product |Price|Quantity|
|=-------+-----+-------=|
|Product1|100  |10      |
|Product2|200  |20      |
'--------+-----+--------'

[statistics] disconnected

Thanks.

Re: How to change the structure when converting from CSV file to json file

JR1 — Fri, 23 Aug 2019 06:21:04 GMT

Click on "Edit Schema" in tWriteJSONField and add the missing output column on the right side?

Re: How to change the structure when converting from CSV file to json file

Anonymous — Fri, 23 Aug 2019 06:37:54 GMT

Oh, I did not...

I set it like this now:

Then, three items will be displayed.
How do I display content only?

Thank you.

Re: How to change the structure when converting from CSV file to json file

JR1 — Fri, 23 Aug 2019 07:20:03 GMT

You will have to remove the three columns just from the right side of the schema and just add a single column there (e.g. "content"). Do not use the arrows in the middle to move columns between the schemas.

Re: How to change the structure when converting from CSV file to json file

Anonymous — Fri, 23 Aug 2019 08:24:39 GMT

Thank you very much.
It is almost completed, but I want you to help at the end.

When setting to your answers, the final output looks like this.

{"app":1,"records":{"Product":{"value":"Product1"},"Price":{"value":100},"Quantity":{"value":10}}}
{"app":1,"records":{"Product":{"value":"Product2"},"Price":{"value":200},"Quantity":{"value":20}}}

I've checked your screenshots many times, but I don't know what's wrong.

tMap mapping:

tMap component setting:

tWriteJSONField2 schema setting:

tWriteJSONField2 setting:

tWriteJSONField2 JSON tree setting:

Thank you.

Re: How to change the structure when converting from CSV file to json file

JR1 — Fri, 23 Aug 2019 09:32:07 GMT

You are missing the "Group By" settings in tWriteJSONField_2.