cancel
Showing results for 
Search instead for 
Did you mean: 
ManishSolanki

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:

ManishSolanki_0-1699188928444.png

 

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:

ManishSolanki_1-1699188969663.png

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):

ManishSolanki_2-1699189033440.png

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):

ManishSolanki_0-1699190661387.png

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.

ManishSolanki_4-1699189232582.png

 

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:

ManishSolanki_5-1699189440649.png

 

 

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.

About the Author
  • Experienced Consultant with a demonstrated history of working in the information technology and services industry. Skilled in Office 365, Azure, SharePoint Online, PowerShell, Nintex, K2, SharePoint Designer workflow automation, PowerApps, Microsoft Flow, PowerShell, Active Directory, Operating Systems, Networking, and JavaScript. Strong consulting professional with a Bachelor of Engineering (B.E.) focused in Information Technology from Mumbai University.
  • I am a Microsoft Business Applications MVP and a Senior Manager at EY. I am a technology enthusiast and problem solver. I work/speak/blog/Vlog on Microsoft technology, including Office 365, Power Apps, Power Automate, SharePoint, and Teams Etc. I am helping global clients on Power Platform adoption and empowering them with Power Platform possibilities, capabilities, and easiness. I am a leader of the Houston Power Platform User Group and Power Automate community superuser. I love traveling , exploring new places, and meeting people from different cultures.
  • Read more about me and my achievements at: https://ganeshsanapblogs.wordpress.com/about MCT | SharePoint, Microsoft 365 and Power Platform Consultant | Contributor on SharePoint StackExchange, MSFT Techcommunity
  • Encodian Owner / Founder - Ex Microsoft Consulting Services - Architect / Developer - 20 years in SharePoint - PowerPlatform Fan
  • Founder of SKILLFUL SARDINE, a company focused on productivity and the Power Platform. You can find me on LinkedIn: https://linkedin.com/in/manueltgomes and twitter http://twitter.com/manueltgomes. I also write at https://www.manueltgomes.com, so if you want some Power Automate, SharePoint or Power Apps content I'm your guy 🙂
  • I am the Owner/Principal Architect at Don't Pa..Panic Consulting. I've been working in the information technology industry for over 30 years, and have played key roles in several enterprise SharePoint architectural design review, Intranet deployment, application development, and migration projects. I've been a Microsoft Most Valuable Professional (MVP) 15 consecutive years and am also a Microsoft Certified SharePoint Masters (MCSM) since 2013.
  • Big fan of Power Platform technologies and implemented many solutions.
  • Passionate #Programmer #SharePoint #SPFx #M365 #Power Platform| Microsoft MVP | SharePoint StackOverflow, Github, PnP contributor
  • Web site – https://kamdaryash.wordpress.com Youtube channel - https://www.youtube.com/channel/UCM149rFkLNgerSvgDVeYTZQ/