05-21-2020 03:35 AM - last edited 05-21-2020 03:46 AM
Disclaimer, I did not fully write this guide. I found this elsewhere on the internet with intense Google searching. However, the guide there was rather poorly written. I was unable to find the original author, I would like to credit them for such exploration into this black hole.
I played with it for about 5 hours and finally figured it out. It was truly a pain but this is going to help my team process a report that we do weekly from ServiceNow. I hope this solves a lot of headache for some of you.
Please note that the zip file is empty. Follow the guide. I apologize about the formatting as well but the forums did not like how my ordered/unordered lists were setup with code-snippits in the HTML.
Order of Actions
Copy and Paste this into the Expression input (where it says fx) - it might look like it only pasted the last line. Don't worry, it got all of it.
json(
uriComponentToString(
replace(
replace(
uriComponent(
split(
replace(
base64ToString(
body('Get_file_content')?['$content']
),
',',';'),
outputs('Newline')
)
),
'%5Cr',
''),
'%EF%BB%BF',
'')
)
)
item()
skip(body('Filter_array'),1)
split(item(),';')[0]
If I wanted to change columns, I would change the 0 to 1... or 2... or 3... etc.
split(item(),';')[0] #COMMENT: This would be column 1 (remember we start from 0 in programming)
split(item(),';')[1] #COMMENT: This would be column 2
If anyone wants a comma delimited CSV reader / parser using only the standard actions in Power Automate that can also handle larger files, in-data commas, and in-data line-breaks, then try this template flow:
https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191