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
- Manually Trigger Flow
- SharePoint Get file content
- Compose (just a general compose), press Enter/Return on your keyboard ONCE. This will count as valid
- Filter Array
- Select
- SharePoint - Get File Content
- Pick the Site Address
- Pick the File Identifier of your CSV
- Add a New Action - "Compose" (Data Operation)
- In the INPUT, just hit Enter/Return ONCE, it should just make the box bigger. Nothing else should be here. I was unable to get "\n" or "\r" to work here.
- Click the three dots on the right of the header for this action, rename it to "Newline" --- without quotes and CASE SENSITIVE.
- In the INPUT, just hit Enter/Return ONCE, it should just make the box bigger. Nothing else should be here. I was unable to get "\n" or "\r" to work here.
- Add a New Action - "Filter array" (Data Operation)
- In the From section, click in the box
- The dynamic content/expression pop-up window should appear.
- Click on Expression within the pop-up window
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',
'')
)
)
- Press OK. If you are unsure if it all pasted, just hover your mouse over the expression inside the "From" field, it should show a little pop-up of the code. Alternatively, you can click the three dots on the upper right hand of the action and "peak code" it should show it all there.
- Click where it says Choose Value
- The Dynamic Content window should pop up again.
- Click on Expression inside the pop up window.
- Type "item()" in the field where it says fx
item()
- Click OK
- Set the logic to "is not equal to"
- Keep the third value completely blank! This will filter out any lines that are blank. If you have blank lines, it will break the flow in the Select action which is next.
- Add A New Action - "Select" (Data Operation)
- Click into the From field
- The Dynamics and Expression pop-up window should appear.
- Click Expression
- Copy and Paste this in the fx field. This is so that it skips your header rows.
skip(body('Filter_array'),1)
- Click OK
- In the Mapping section, this is where you will set your JSON values. In my case, I uploaded a simple CSV file that has two columns: Firstname and Lastname. So if I wanted the first column:
split(item(),';')[0]
If I wanted to change columns, I would change the 0 to 1... or 2... or 3... etc.
- Each column is represented via the code below:
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
- As for your "Key," you can define that however you like. I like to stick to the column names for clarity.
- The End Result should look something similar to this when testing:
- The Full Picture:
Still no joy. I have no idea what I am doing wrong. below is the failed run and then the code.
his idea working well, but i don't use the json schema. I'm grabbing the data directly with split(item(),<delimiter>) and then formating it inside the select.
