Answered

Flow For Importing Entire Excel Worksheet into SQL Server?

(0) Share

Report

Posted on by JBR15

Hi,

My goal is to create a Power Automate Flow that will allow me to retrieve a file (In my case from SharePoint) and enter the entire worksheet(s) into a SQL Server Table. I have already created the table in SQL to match the column headers and datatypes of my Excel sheet. My question is this: I have seen other posts for "List rows present in a table" and this will not work for me because I do not have a table setup within my sheet, just raw data, and therefore I cannot programmatically reference the table in my flow. If there is an option to import a CSV using Power Automate this would also be fine as I have a staging table and then cast those values to production tables with my necessary datatypes.

Thanks!

Categories:

Building flows

I have the same question (15)

All responses (3)

Answers (1)

Verified answer

takolota1 4,980 Moderator on at

Like (0)

Report
Copy link

Link copied!

@JBR15

Yes you can get the contents of a CSV file on OneDrive or SharePoint & transform that for SQL queries. If the file contents don’t come through as text, then you can usually use a base64tostring( ) expression on the contents to get it into text.

How big are your worksheets? How many rows?

If it is just a few, then I have something you can use to get CSV data into JSON dynamic content that you can use in a regular SQL action in a loop.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-to-Dataset/td-p/1508191

EDIT: Here is a link to the template flow page where the author eventually got something to help with his external site report download to SQL request: https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Automate-Reports-From-External-Datasources/td-p/1486023

Was this reply helpful? Yes No
JBR15 11 on at

Like (1)

Report
Copy link

Link copied!

@takolota

I want to sincerely thank you for your help. In watching the video on that link, I came across your channel and saw the "Automate Reports From External Datasource" video and it perfectly fits my needs, I seriously can't thank you enough.

Was this reply helpful? Yes No
takolota1 4,980 Moderator on at

Like (0)

Report
Copy link

Link copied!

@JBR15

That’s awesome, glad I could help!

Was this reply helpful? Yes No