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

Highlighted
Five Stars

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

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.

コメント 2019-08-21 234914.pngコメント 2019-08-21 234941.png

Thank you.


Accepted Solutions
Nine Stars JR
Nine Stars

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

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!).

1.jpg

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

2.jpg

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

3.jpg

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

4.jpg

 

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.


All Replies
Nine Stars JR
Nine Stars

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

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!).

1.jpg

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

2.jpg

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

3.jpg

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

4.jpg

 

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.

Five Stars

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

Thanks for reply.

 

I tried it.

But, "Output Column" is empty.

Is something wrong?

コメント 2019-08-23 135331.png

 

tFixedFlowInput settings:

コメント 2019-08-23 135445.png

 

 

tFixedFlowInput schema settings:

コメント 2019-08-23 135509.png

 

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.

Nine Stars JR
Nine Stars

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

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

Five Stars

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

Oh, I did not...

I set it like this now:

コメント 2019-08-23 143448.png

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

コメント 2019-08-23 143538.png

 

Thank you.

Nine Stars JR
Nine Stars

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

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.

Five Stars

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

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:

コメント 2019-08-23 161525.png

 

tMap component setting:

コメント 2019-08-23 161646.png

 

 

 

tWriteJSONField2 schema setting:
コメント 2019-08-23 161743.png

 

 

 

 

 

 

 

 

 

 

 

tWriteJSONField2 setting:

コメント 2019-08-23 161937.png

 

tWriteJSONField2 JSON tree setting:

コメント 2019-08-23 162121_3.png

 

Thank you.

 

Nine Stars JR
Nine Stars

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

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

Calling Talend Open Studio Users

The first 100 community members completing the Open Studio survey win a $10 gift voucher.

Start the survey

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch