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
Process Advisor

Introducing Process Advisor

Check out the new Process Advisor community forum board!

MPA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

Users online (30,488)