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

[AbhishekBoga]

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

[PhilHibbs]

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

[AbhishekBoga]

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

Fig 1:

Fig 2:

[Anonymous]

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.

[Anonymous]

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"}
	]	
}

[AbhishekBoga]

Thank buddy for correcting JSON format.

Could you please help me with the solution.

[Anonymous]

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.