topic Re: Address column having comma ',' value is not converting into JSON format from Excel file in Talend Studio

Address column having comma ',' value is not converting into JSON format from Excel file

AbhishekBoga — Tue, 21 May 2019 09:49:11 GMT

Hi All,

I have a task where I need to convert excel file data into JSON format. I'm able to convert the excel file but some of the data rows from file is having comma ',' values in fields say Address and that row is not converting into JSON format.

Row 1 & 3 : Address1 fields is having a value with comma and due to this this row is not converted int JSON format, whereas Row 2 is not having comma and thus converted into JSON format. Refer fig.1

What I want is to retain the comma in the JSON format as shown in fig. 2:

Fig 2 - Pls ignore the JSON format as I might be wrong

Re: Address column having comma ',' value is not converting into JSON format from Excel file

PhilHibbs — Tue, 21 May 2019 10:06:58 GMT

Those both look identical to me, did you paste the "good" results twice?

Re: Address column having comma ',' value is not converting into JSON format from Excel file

AbhishekBoga — Tue, 21 May 2019 11:31:53 GMT

Yes... bymistake pasted twice. Find the figures as below

Fig 1:

Fig 2:

Re: Address column having comma ',' value is not converting into JSON format from Excel file

Anonymous — Tue, 21 May 2019 13:07:41 GMT

Hi @AbhishekBoga ,

The json form you are trying is not a valid json, please let us know the actual json form that you are trying to make.

Re: Address column having comma ',' value is not converting into JSON format from Excel file

Anonymous — Tue, 21 May 2019 15:47:03 GMT

The correct format of the JSON file is :

{
	"ma_liste":[
		{"id":1,
		"address1": "mon addresse1 1",
		"address2": "mon addresse1 2",
		"address3": "mon addresse1 3"}
		,
		{"id":2,
		"address1": "mon addresse2 1",
		"address2": "mon addresse2 2",
		"address3": "mon addresse2 3"}
		,
		{"id":3,
		"address1": "mon addresse3 1",
		"address2": "mon addresse3 2",
		"address3": "mon addresse3 3"}
	]	
}

Re: Address column having comma ',' value is not converting into JSON format from Excel file

AbhishekBoga — Tue, 21 May 2019 16:22:33 GMT

Thank buddy for correcting JSON format.

Could you please help me with the solution.

Re: Address column having comma ',' value is not converting into JSON format from Excel file

Anonymous — Tue, 21 May 2019 16:37:24 GMT

I think you need to add one more column to group all the address fields. I just added an additional field in tmap named as groupcolumn.

Job:

tMap:

WriteJsonComponent:

Json Mapping:

The above mapping will give you the final json form as follows.

{"address_list":[{"id":1,"address1":"A/14, Grant road","address2":"St. Bolt road","address3":"Mumbai"},{"id":2,"address1":"A/14 Grant road","address2":"St. Bolt road","address3":"Mumbai"},{"id":3,"address1":"A/14, Grant road","address2":"St. Bolt road","address3":"Mumbai"}]}

Note: Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.