How to convert CSV file to array of JSON objects?
Use Case
Today, we will learn how to convert csv file to array of JSON object without using 'Apply to each' as it not the optimum action when using a large csv file.
Scenario & Sample data
We will take an example of an incoming email with csv file as attachment. The flow will convert the attached csv file to an array of JSON objects.
Input csv file email attachment looks like:
Index,Customer Id,First Name,Last Name,Company,City
1,DD37Cf93aecA6Dc,Sheryl,Baxter,Rasmussen Group,East Leonard
2,1Ef7b82A4CAAD10,Preston,Lozano,Vega-Gentry,East Jimmychester
3,6F94879bDAfE5a6,Roy,Berry,Murillo-Perry,Isabelborough
4,5Cef8BFA16c5e3c,Linda,Olsen,"Dominguez, Mcmillan and Donovan",Bensonview
5,053d585Ab6b3159,Joanna,Bender,"Martin, Lang and Andrade",West Priscilla
6,2d08FB17EE273F4,Aimee,Downs,Steele Group,Chavezborough
7,EA4d384DfDbBf77,Darren,Peck,"Lester, Woodard and Mitchell",Lake Ana
8,0e04AFde9f225dE,Brett,Mullen,"Sanford, Davenport and Giles",Kimport
9,C2dE4dEEc489ae0,Sheryl,Meyers,Browning-Simon,Robersonstad
10,8C2811a503C7c5a,Michelle,Gallagher,Beck-Hendrix,Elaineberg
There are 6 fields in the header row of input csv file: Index, Customer Id, First Name, Last Name, Company, City
Solution
1. Create an automate cloud flow. Select "When a new email arrives (V3)" as the trigger. When you expand the trigger, you could set various parameters to set. For this example, set both "Include Attachments" & "Only with Attachments" parameters as "Yes". Here, I have also applied filter on the subject, but you could set other parameters as per your need:
2. Next, add "Apply to each" action & pass 'Attachments' from the trigger to iterate those. Inside that block, add compose action & rename it to 'CSV data' that stores the content of an attachment. To get the content of the attachment, expression needs to be added. Every expression is added in the expression box as highlighted in the below screenshot:
base64ToString(items('Apply_to_each')?['contentBytes'])
2.1. Now, add another compose action just below the 'CSV data' action inside the apply to each block. Rename it as 'CSV array'. Write an expression inside CSV array compose action to create a simple array of csv data using replace, split function. The chunk function has been used to create array of each data row (including the header row):
chunk(split(replace(outputs('CSV_data'),decodeUriComponent('%0A'),','),','),6)
The key point here is the second parameter '6' of chunk function. This will be decided based on the number of fields in input csv file. As in this example, the input csv file has 6 columns, so we have chosen the value as '6'.
2.2. To extract the header row from the array, add another compose action & rename it as 'CSV header'. Here, also writes an expression for extracting the header row from the array (the first element of CSV array action):
take(outputs('CSV_array'),1)
2.3. Next, add "Select" action to create the final array of JSON objects. To get only the data rows, expression needs to be added in "From" parameter. Similarly, to get the field or column name and its corresponding value, we need to write the expressions for each key & value in "Map" parameter.
Expression used for "From" parameter:
skip(outputs('CSV_array'),1)
Expression used for each key & value in "Map" parameters:
Key | Value |
first(outputs('CSV_header'))[0] | item()?[0] |
first(outputs('CSV_header'))[1] | item()?[1] |
first(outputs('CSV_header'))[2] | item()?[2] |
first(outputs('CSV_header'))[3] | item()?[3] |
first(outputs('CSV_header'))[4] | item()?[4] |
first(outputs('CSV_header'))[5] | item()?[5] |
Using expressions, we are fetching the field or column name from the header row saved in 'CSV header' compose action. The corresponding values can also be fetched from each row using index as each data row is itself an array.
Output
The output of Select action is the required array of JSON objects which will look like:
Conclusion
We have used expressions & Select action to perform the conversion which is effective way in scenarios where we have large csv to convert, and we wish to avoid adding loop for the conversion.
Comments
-
How to convert CSV file to array of JSON objects?
@ManishSolanki Hello, I used above steps but for me it is not working if data have ',' like in above provided sample CSV 4th record (index 4) company name is 'Dominguez, Mcmillan and Donovan' but in flow it is treating as 'Dominguez' as one column and 'Mcmillan and Donovan' is another
Thanks in Advance
-
How to convert CSV file to array of JSON objects?
You could try setting 'Datetime format' to "ISO 8601" and format the value for date column in 'yyyy-MM-dd' format when adding row in excel sheet:
-
How to convert CSV file to array of JSON objects?
@ManishSolanki I was able to solve this, but I got a different problem now. The Excel has a column which contains a date. The date is in 'dd-mm-yyyy' format, but when it is read by Power Automate, the date is picked up as 'mm-dd-yyyy'.
As an example, the date is '02-03-2023' which means it is 2nd March 2023, but is is being read as 3rd Feb 2023.
Is there a solution to this?
-
How to convert CSV file to array of JSON objects?
-
How to convert CSV file to array of JSON objects?
The idea that I want to accomplish is that:
- When a CSV file comes via email, I want to convert it into a JSON (it does not have any headers though) and then insert the JSON into a table in an Excel template
-
How to convert CSV file to array of JSON objects?
@ManishSolanki I am trying this solution but when I test the flow, it gives me the following error. The CSV is there as an attachment in the email and I am able to see data within the CSV as well.
*This post is locked for comments