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.

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
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

MPA Licensing.jpg

Ask your licensing questions at the Power Automate AMA!

Join Priya Kodukula and the licensing team, super users and MVPs to find answers to your questions on Power Automate licensing.

Users online (2,779)