cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ericonline
Community Champion
Community Champion

How to parse csv's to SQL

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!

1 ACCEPTED SOLUTION

Accepted Solutions

I actually don't see the "Accept as Solution" button on my post. You'll have to click it!

 

Also, screenshots of the Flow are not showing up under "Looks like this:" in my original post. Weird.

View solution in original post

5 REPLIES 5
v-litu-msft
Community Support
Community Support

Hi @ericonline,

 

Thanks for your solution that parse csv's to SQL, you've done a lot for the community.
You could go ahead and mark the post as solved by clicking “Accept as Solution” so that this thread will be marked for other users to easily identify!

Best Regards,
Community Support Team _ Lin Tu

I actually don't see the "Accept as Solution" button on my post. You'll have to click it!

 

Also, screenshots of the Flow are not showing up under "Looks like this:" in my original post. Weird.

View solution in original post

v-litu-msft
Community Support
Community Support

Hi @ericonline,

 

The screenshot couldn't be seen, you could edit the original reply and insert them.

 

Best Regards,
Community Support Team _ Lin Tu

As I mentioned, when I try to insert screenshots into the Original Post, it does not work.

 

Here is proof:

image.pngimage.pngimage.png

ericonline
Community Champion
Community Champion

image.pngimage.png

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Solution Authors
Users online (2,942)