Creating JSON with nested array from CSV. No Idea How!!!

[chlxco]

hi all,

Im in the following pickle. I have my csv data as follows (fictional data 😊 😞

FirstName	LastName	Class	Teacher
John	Doe	Math	Carlos
John	Doe	English	Anna
John	Doe	Art	Kim
Sarah	Miller	Math	Kelly
Sarah	Miller	Art	Matt

I dont have problems to consume the CSV from either OneDrive, Sharepoint etc. However, Once loaded, I'm having several issues to create the following JSON structure. End goal is use that content as a payload in my API.  (I dont have API call issues either, thats tested already). Ive watched tons of examples where people create flat Jason structures but nothing like this. Any idea, support, guidance? Really appreciate it.

Expected Structure:

[

{

"FirstName":"John",

"LastName":"Doe",

"Schedule":[ {"Class":"Math","Teacher":"Carlos"},{"Class":"English","Teacher":"Anna"},{"Class":"Art","Teacher":"Kim"}]

},

{

"FirstName":"Sarah",

"LastName":"Miller",

"Schedule":[ {"Class":"Math","Teacher":"Kelly"},{"Class":"Art","Teacher":"Matt"}]

}

]

[devtollon]

Hi @chlxco ,

Power Automate, as far as I know, does not have a built-in way to parse a csv file. You'll need to do it by hand in order to prepare your data to be stored inside a JSON with the structure you want.

I leave you this article which shows an example on how to do that: https://www.tachytelic.net/2021/02/power-automate-parse-csv/ 

Once you've it done, to create your JSON structure, what I would do is:

1. Create a variable (type object) to store the entire JSON;
2. Create a variable (type array) to store the entire array of objects;
3. Create a variable (type array) to store the Schedule objects;
4. Create a variable (type object) to store the single Schedule object;

Once you have this structure, you can loop though the CSV extracted values and compose the JSON with your kind of structure.

Let me know if this solved your issue!

[Matthy79]

Hello @chlxco,

As @devtollon already mentioned you need to read the csv on your own or you use a third party connector. As for the transformation you don't need variables or loops. This can also be done by xpath. Here is a sample of how it would work:

{"id":"2f3e2d07-7aa3-4896-96e8-112d0c004f41","brandColor":"#8C3900","connectionReferences":{"shared_commondataserviceforapps":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_commondataserviceforapps/connections/62947705b1a746319ca1e59aae985afc"}},"shared_sharepointonline":{"connection":{"id":"/providers/Microsoft.PowerApps/apis/shared_sharepointonline/connections/shared-sharepointonl-b959cb53-5ecb-4b41-805f-06e7795a0bd2"}}},"connectorDisplayName":"Control","icon":"data:image/svg+xml;base64,PHN2ZyB3aWR0aD0iMzIiIGhlaWdodD0iMzIiIHZlcnNpb249IjEuMSIgdmlld0JveD0iMCAwIDMyIDMyIiB4bWxucz0iaHR0cDovL3d3dy53My5vcmcvMjAwMC9zdmciPg0KIDxwYXRoIGQ9Im0wIDBoMzJ2MzJoLTMyeiIgZmlsbD0iIzhDMzkwMCIvPg0KIDxwYXRoIGQ9Im04IDEwaDE2djEyaC0xNnptMTUgMTF2LTEwaC0xNHYxMHptLTItOHY2aC0xMHYtNnptLTEgNXYtNGgtOHY0eiIgZmlsbD0iI2ZmZiIvPg0KPC9zdmc+DQo=","isTrigger":false,"operationName":"Scope_csv_array_converter","operationDefinition":{"type":"Scope","actions":{"Compose_converted_csv":{"type":"Compose","inputs":[{"FirstName":"John","LastName":"Doe","Class":"Math","Teacher":"Carlos"},{"FirstName":"John","LastName":"Doe","Class":"English","Teacher":"Anna"},{"FirstName":"John","LastName":"Doe","Class":"Art","Teacher":"Kim"},{"FirstName":"Sarah","LastName":"Miller","Class":"Math","Teacher":"Kelly"},{"FirstName":"Sarah","LastName":"Miller","Class":"Art","Teacher":"Matt"}],"runAfter":{},"metadata":{"operationMetadataId":"93bb91cd-6bbc-4985-bee7-6000e99181f6"}},"Select_add_schedule":{"type":"Select","inputs":{"from":"@outputs('Compose_converted_csv')","select":"@addProperty(item(), 'schedule', concat('{\"Class\":\"', item()['Class'], '\",\"Teacher\":\"', item()['Teacher'], '\"}'))"},"runAfter":{"Select_names":["Succeeded"]},"metadata":{"operationMetadataId":"cb2bb348-0d88-4fd9-a7a8-1dccd5b9c310"}},"Compose_xml":{"type":"Compose","inputs":"@xml(json(concat('{ \"root\": { \"user\": ', body('Select_add_schedule'), ' }}')))","runAfter":{"Select_add_schedule":["Succeeded"]},"metadata":{"operationMetadataId":"29d08333-b964-43ef-9c21-42ef9c5ebafb"}},"Select_api_payload":{"type":"Select","inputs":{"from":"@union(body('Select_names'),body('Select_names'))","select":{"FirstName":"@item()['FirstName']","LastName":"@item()['LastName']","Schedule":"@json(concat('[', join(xpath(outputs('Compose_xml'), concat('root/user[FirstName=\"', item()['FirstName'] ,'\" and LastName=\"', item()['LastName'] ,'\"]/schedule/text()')), ','), ']'))"}},"runAfter":{"Compose_xml":["Succeeded"]},"metadata":{"operationMetadataId":"96eb86b0-2873-4e87-b083-4ec11bb6df7e"}},"Select_names":{"type":"Select","inputs":{"from":"@outputs('Compose_converted_csv')","select":{"FirstName":"@item()['FirstName']","LastName":"@item()['LastName']"}},"runAfter":{"Compose_converted_csv":["Succeeded"]},"metadata":{"operationMetadataId":"a417abd8-8ad4-4598-a230-eea5bcb1e41e"}}},"runAfter":{},"metadata":{"operationMetadataId":"4976f52b-8aba-453b-8132-9bf875c42167"}}}

You can add this by using the clipboard in the old designer.

If you want to go the loop way, I would suggest to create an array of FirstName and LastName and use union to loop through it. Now you could use filter array, select and compose to create your record. After the loop you have the array you wanted by using the output of the compose action.

I would go the xpath way.

[Chriddle · Accepted solution]

From CSV to Expected Structure in only 2 Select actions.

However, this CSV parsing only works in simple cases.

DataCsv (Compose)

FirstName,LastName,Class,Teacher
John,Doe,Math,Carlos
John,Doe,English,Anna
John,Doe,Art,Kim
Sarah,Miller,Math,Kelly
Sarah,Miller,Art,Matt

Data (Select)

From

skip(
	split(
		outputs('DataCsv'),
		decodeUriComponent('%0A')
	),
	1
)

Map FirstName

split(item(), ',')[0]

Map LastName

split(item(), ',')[1]

Map ScheduleString

concat(
	'{"Class":"', split(item(), ',')[2], '","Teacher":"', split(item(), ',')[3], '"}'
)

Select

From

union(
	chunk(
		xpath(
			xml(json(concat('{"Root":{"Item":', body('Data'),'}}'))),
			'//FirstName/text() | //LastName/text()'
		),
		2
	),
	json('[]')
)

Map FirstName

first(item())

Map LastName

last(item())

Map Schedule

json(
	concat(
		'[',
		join(
			xpath(
				xml(json(concat('{"Root":{"Item":', body('Data'),'}}'))),
				concat('//Item[FirstName="', first(item()), '" and LastName="', last(item()), '"]/ScheduleString/text()')
			),
			','
		),
		']'
	)
)

Result:

[
  {
    "Firstname": "John",
    "LastName": "Doe",
    "Schedule": [
      {
        "Class": "Math",
        "Teacher": "Carlos"
      },
      {
        "Class": "English",
        "Teacher": "Anna"
      },
      {
        "Class": "Art",
        "Teacher": "Kim"
      }
    ]
  },
  {
    "Firstname": "Sarah",
    "LastName": "Miller",
    "Schedule": [
      {
        "Class": "Math",
        "Teacher": "Kelly"
      },
      {
        "Class": "Art",
        "Teacher": "Matt"
      }
    ]
  }
]

[chlxco]

Thank you so much Chriddle! I replicated the scenario and worked as a charm. Though I will be adding more fields to "group by", I think I understand now the concept about mapping and using xpath as an option. As a reference, I was trying to solved based on @devtollon  reply on this post but my flow was getting oddly big 😅

Thanks again for the detailed answer, and thank you all for your support on this.

Cheers,

[chlxco]

Hey there,

I have an extra question for you that I have been unable to overcome once I got my object created as you instructed. The array within the object (Schedule) looks as an entire string and when trying to parse it to create the final JSON, it doesnt create the array.. Here an example of what I get after I run the Select action in your answer:

{

"body": [

{

"FirstName": "a",

"LastName": "b",

"Schedule": "[{\"Class\":\"c\",\"Teacher\":\"d\"},{\"Class\":\"e\",\"Teacher\":\"f\"}]"

},...... next item in the array

This is what I get after using Parse to JSON action:

{

"body": [

{

"FirstName": "a",

"LastName": "b",

"Schedule": "[{\"Class\":\"c\",\"Teacher\":\"d\"},{\"Class\":\"e\",\"Teacher\":\"f\"}]"

},{....next item

I have tried changing the schema from Schedule attribute from String to array, object.. without success.

Here what Im expecting to get from the parse to JSON action:

{

"body": [

{

"FirstName": "a",

"LastName": "b",

"Schedule": [

            {"Class":"c","Teacher":"d"},

            {"Class":"e","Teacher":"f"}

            ]

},....... and so on.

Thanks again,

[Chriddle]

Converting the ScheduleStrings (in "Data") to Array is done in my “Map Schedule” above using json(...) function.

I don't understand what you mean by "I tried to change the schema..."

[tbar44]

Thanks for this, works as expected and is nice and quick.

Is it possible to use similar to add another layer?

So if the table was something like;

FirstName	LastName	Class	Teacher	Assignment1	Assignment2
John	Doe	Math	Carlos	Algebra	Calculus
John	Doe	English	Anna	Report	Essay
John	Doe	Art	Kim	Painting	Sculpture
Sarah	Miller	Math	Carlos	Algebra	Calculus
Sarah	Miller	English	Anna	Report	Essay
Sarah	Miller	Art	Kim	Painting	Sculpture

The output would look something like;

[{
    "Firstname": "John",
    "LastName": "Doe",
    "Schedule": [
      {
        "Class": "Math",
        "Teacher": "Carlos",
	"Assignments": [
		{
        	"Assignment1": "Algebra",
        	"Assignment2": "Calculus"
      	}
	]
      },
      {
        "Class": "English,
        "Teacher": "Anna",
	"Assignments": [
		{
        	"Assignment1": "Report",
        	"Assignment2": "Essay"
      	}
	]
      },
      {
        "Class": "Art",
        "Teacher": "Kim",
	"Assignments": [
		{
        	"Assignment1": "Painting",
        	"Assignment2": "Sculpture"
      	}
	]
      },
    ]
  },
{
    "Firstname": "Sarah",
    "LastName": "Miller",
    "Schedule": [
      {
        "Class": "Math",
        "Teacher": "Carlos",
	"Assignments": [
		{
        	"Assignment1": "Algebra",
        	"Assignment2": "Calculus"
      	}
	]
      },
      {
        "Class": "English,
        "Teacher": "Anna",
	"Assignments": [
		{
        	"Assignment1": "Report",
        	"Assignment2": "Essay"
      	}
	]
      },
      {
        "Class": "Art",
        "Teacher": "Kim",
	"Assignments": [
		{
        	"Assignment1": "Painting",
        	"Assignment2": "Sculpture"
      	}
	]
      },
    ]
  }]

Not sure if this is outside the limits of what you have done?

I did try to tweak the code myself but suspect I'm doing something dim!

Thanks!

[Chriddle]

Why would you put exactly one object into an array?

I discarded that and went for a structure like this:

"Assignments": {

    "Assignment1": "Algebra",

    "Assignment2": "Calculus"

}

To do this, all you need to do is add these new columns to the Data action above.

Map ScheduleString

concat(
	'{"Class":"', split(item(), ',')[2], '","Teacher":"', split(item(), ',')[3], '", "Assignments":{"Assignment1": "', split(item(), ',')[4], '", "Assignment2": "', split(item(), ',')[5], '"}}'
)

[tbar44]

Thanks, sorry my bad, my example could have been better.

The format is set out by the data being ingested by an API.

Was trying to mimic the data format and realise I did a poor job.

Better example would be this.

FirstName	LastName	Class	Teacher	Assignment	Due Date
John	Doe	Math	Carlos	Algebra	2024-06-01
John	Doe	Math	Carlos	Calculus	2024-07-01
John	Doe	English	Anna	Report	2024-08-01
John	Doe	Art	Kim	Painting	2024-09-01
Sarah	Miller	Math	Carlos	Algebra	2024-10-01
Sarah	Miller	English	Anna	Report	2024-11-01
Sarah	Miller	English	Anna	Essay	2024-12-01
Sarah	Miller	Art	Kim	Painting	2025-01-01

So would look something like this.

[{
    "Firstname": "John",
    "LastName": "Doe",
    "Schedule": [
      {
        "Class": "Math",
        "Teacher": "Carlos",
	"Assignments": [
		{
        	"Assignment": "Algebra",
        	"Due Date": "2024-06-01"
      	},
	{
        	"Assignment": "Calculus",
        	"Due Date": "2024-07-01"
      	}
	]
      },
      {
        "Class": "English,
        "Teacher": "Anna",
	"Assignments": [
		{
        	"Assignment": "Report",
        	"Due Date": "2024-08-01"
      	}
	]
      },
      {
        "Class": "Art",
        "Teacher": "Kim",
	"Assignments": [
		{
        	"Assignment": "Painting",
        	"Due Date": "2024-09-01"
      	}
	]
      },
    ]
  },
{
    "Firstname": "Sarah",
    "LastName": "Miller",
    "Schedule": [
      {
        "Class": "Math",
        "Teacher": "Carlos",
	"Assignments": [
		{
        	"Assignment": "Algebra",
        	"Due Date": "2024-10-01"
      	}
	]
      },
      {
        "Class": "English,
        "Teacher": "Anna",
	"Assignments": [
		{
        	"Assignment": "Report",
        	"Due Date": "2024-11-01"
      	},
	{
        	"Assignment": "Essay",
        	"Due Date": "2024-12-01"
      	}
	]
      },
      {
        "Class": "Art",
        "Teacher": "Kim",
	"Assignments": [
		{
        	"Assignment": "Painting",
        	"Due Date": "2025-01-01"
      	}
	]
      },
    ]
  }]

Apologies for the confusion!