Create Json File From Excel

[heshkaru]

{ "index" : { } }

{ "ENV":"DEV", "BATCH_ID": "2023-12-16 08:27:16", "EXPECTED_VALUE":"ADDQYU5B", "ACTUAL_VALUE":"dbdf24" }

{ "index" : { } }

{ "ENV":"DEV", "BATCH_ID": "2023-12-16 08:27:16", "EXPECTED_VALUE":"25325", "ACTUAL_VALUE":"vbdb3" }

{ "index" : { } }

{ "ENV":"DEV", "BATCH_ID": "2023-12-16 08:27:16", "EXPECTED_VALUE":"bdbd42", "ACTUAL_VALUE":"gdg42" }

/////////////////////////////////////////////////////////////////////////////////////

I need to create a json file which have the following json objects extracted from an excel. This output is used for ElasticSearch Indexing. This specific format should be maintained.

Thank you

[gjeremy1617088143]

Hi, you can use tFileInput Excel to extract the excel file then tWriteJSONfield component to create a specific JSON from the extract.

https://help.talend.com/r/en-US/7.3/processing/twritejsonfield-standard-properties

Send me love and kudos

[heshkaru]

 

The final output file should be a json file and it should be in the format above.

Each line should be a json object( and should have -> { "index" : { } } in following way). This is the format that elasticsearch accepts.

Thank you

[gjeremy1617088143]

why don't you use tElasticSearchOutput component ?

[heshkaru]

Any examples?

[Anonymous · Accepted solution]

Hi

I think you need to iterate each row from an excel, write a json string using tWriteJsonField and append it to the json file, the job looks like:

tFileInputExcel--tFlowToIterate--iterate--tFixedFlowInput1--main--tFileOutputDelimited-oncomponnetOK--tFixedFlowInput2---main-tWriteJsonField--tFileOutputDelimited2

tFixedFlowInput1: generate the string { "index" : { } } and write it to the Json file.

tFixedFlowInput2: generate the current data.

tFileOutputDelimited2: append the Json string to the Json file.

Hope it helps.

Regards

Shong

[heshkaru]

Hi Shong for tFixedFlowInput2 can i get values from tFileInputExcel. When I tried all i can see is only can pass static values defined in tFixedFlowInput2. Is there a specific way to get excel values.

Thank you

[gjeremy1617088143]

Hi, you have to use the variables generated by the tFlowToIterate component in the tFixedFlowInput_2

[heshkaru]

Thank you finally completed it

[heshkaru]

Hi @Shicong Hong​ 

Completed before task thanks to you.

Now have an issue sending this .json file for a POST API.

Attached the curl below.

curl -XPOST -u 'xxxx:xxxxx' 'xxxxxxxxxxxxxxxxxxxxxxxx/etl_staging/_bulk' --data-binary @etl-staging-job1.json -H 'Content-Type: application/json'

Can I complete this with trestClient uploading the file or do I need to pass as a string to trestclients component 'string' column.

Thank you