Power Automate Excel to SQL Server

(0) Share

Report

Posted on by tjestesjr

145

Hi,

I am trying to create a flow from a canvas app that lets users upload a file of their choosing. This file is saved to a SharePoint Site and is then supposed to be loaded to a SQL table. Because the table is loaded "dynmically" the column names from the Excel table are not showing up as dynamic content. So, my question is how to reference the columns from the Excel table? I have tried using

items('Apply_to_each')['Supplier Name'] but it only puts the literal string in the database. My other issue is that the file has ~400 rows and I am only getting 1 entry in the database. Do I need the "SQL Insert Rows" outside of the For Each loop or does it need to be in a separate "Fore Each Loop"?

Categories:

Building Power Apps

I have the same question (0)

All responses (4)

Answers (1)

Verified answer

v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!
Hi @tjestesjr ,

First you need another Apply to each below List rows action, inside this Apply to each control, use the insert row to SQL table action.

Second, you need to write expressions for each field, select add dynamic content then choose expression and write in formula bar. I assume Apply to each 2 is the new control added that include the insert row action, then the expressions will be similar to:

items('Apply_to_each_2')?['Supplier Name']

Best regards,

Was this reply helpful? Yes No
tjestesjr 145 on at

Like (0)

Report
Copy link

Link copied!

I have embedded another Apply to Each and that functionality works. I used the function items('Apply_to_each_2')?['Supplier Name'] in the supplier column but I am still getting it as a literal string in the data bases (see below)

Was this reply helpful? Yes No
v-jefferni Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @tjestesjr ,

It's expression, need to write in the same way as ImportDate field.

Best regards,

Was this reply helpful? Yes No
tjestesjr 145 on at

Like (0)

Report
Copy link

Link copied!

I understand now and was able to import the Supplier Name, thank you very much.

I am trying to import the next column now but I am getting an error on the expression: items('Apply_to_each_2')?['Wendy's Eng_x002e_ Prefix']
I got the column name from the raw JSON produced by the "List Rows" step.

Was this reply helpful? Yes No