This might already be out there somewhere, but just figured it out in case someone needs it.
Scenario:
- .csv file uploaded to Azure Blob Storage
- Need to parse .csv and write new records to Azure SQL
Solution:
Trigger: Blob added to container
Control1: FOR ALL blobs added, `createArray(triggerBody()?['Path'])`
Control2: IF `CurrentItem ends with .csv`
Action1: Get Blob Content Using Path `CurrentItem`
Action2: Compose `body('Action1')`
Action3: (The first secret ingredient) Compose "@split(outputs(Action2, '\r\n')"
- The key here, is that you MUST actually type the above into the Compose inputs box NOT THE EXPRESSION FIELD
- See "Step 2" here: https://dynamicscitizendeveloper.com/2019/06/06/microsoft-flow-csv-integration-with-dynamics-365/
Control3: FOR ALL Action3, `skip(outputs(Action3, 1))`
- This skips the header row in the .csv
Control4: IF `CurrentItem is not equal to "" `
- For some reason, Action3 generates an additional blank row. This filters the blank row out.
Action4: (The second secret ingredient) Insert Row (SQL)
- In each SQL table column field, you need to add the expression `split(items('Control3'), ',')?[X]`
- Where "[X]" = the position within the split array that needs to map to that given SQL column
Looks like this:
Hope this helps!
