topic Re: Create Json File From Excel in Talend Studio

Create Json File From Excel

heshkaru — Fri, 15 Nov 2024 23:13:08 GMT

{ "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

Re: Create Json File From Excel

gjeremy1617088143 — Mon, 14 Feb 2022 07:57:04 GMT

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

Re: Create Json File From Excel

heshkaru — Mon, 14 Feb 2022 10:55:17 GMT

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

Re: Create Json File From Excel

gjeremy1617088143 — Mon, 14 Feb 2022 11:00:23 GMT

why don't you use tElasticSearchOutput component ?

Re: Create Json File From Excel

heshkaru — Mon, 14 Feb 2022 11:10:58 GMT

Any examples?

Re: Create Json File From Excel

Anonymous — Tue, 15 Feb 2022 01:00:03 GMT

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

Re: Create Json File From Excel

heshkaru — Tue, 15 Feb 2022 10:32:50 GMT

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

Re: Create Json File From Excel

gjeremy1617088143 — Tue, 15 Feb 2022 10:55:53 GMT

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

Re: Create Json File From Excel

heshkaru — Thu, 17 Feb 2022 08:46:37 GMT

Thank you finally completed it

Re: Create Json File From Excel

heshkaru — Fri, 18 Feb 2022 09:33:49 GMT

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

Re: Create Json File From Excel

Anonymous — Fri, 18 Feb 2022 12:43:34 GMT

Does this command run fine in Linux terminal? If yes, you use tSystem in Talend job to run this command.

Regards

Shong

Re: Create Json File From Excel

heshkaru — Fri, 18 Feb 2022 16:40:05 GMT

Hi @Shicong Hong

Does tSystem refers to command prompt in windows, for me it doesn't work from command prompt (Im using a windows machine), but I have installed another shell script where I can run linux commands, it works there. Does this component have an impact base on the OS its working

Re: Create Json File From Excel

Anonymous — Sat, 19 Feb 2022 02:50:58 GMT

I thought you are using Linux OS, as CURL is a built-in command on Linux. If you are using Windows OS, you should learn how to get CURL working on Windows. tSystem run a system command as you run it in a command prompt on your OS.