cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kris_T
Frequent Visitor

PAD Execute SQL statement insert into

all,

I am unable to successfully execute an "Execute SQL statement" to obtain an insert from an excel instance.

 

My connection works perfectly,

however my SQL statement bombs out on :

 

insert into Tbl_FN_invoices
select * from "%ExcelInstance%"

 

The table exists / error is 'invalid object name ExcelInstance'

 

any suggestions ?

3 REPLIES 3
Ankesh_49
Super User
Super User

@kris_T I guess there could be 2 possible solutions:


1.  SQL statement seems to be incorrect, it should be(without quotes)

      insert into Tbl_FN_invoices
      select * from %ExcelInstance%

2. I believe read from excel worksheet action is missing. Therefore, I would recommend to use following actions:

  1. Launch excel
  2. Read from excel worksheet
  3. Execute SQL statement inside For each loop

OR

      Do this(Considering excel file as database, efficient way to retrieve data):

Ankesh_49_0-1655755575935.png

Hope it helps!!


Thanks
Ankesh

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

VJR
Super User
Super User

Hi @kris_T 

 

- The right syntax is to do a select * from TableName.

Since you are interacting with Excel, here TableName should be the Sheet Name whereas you have used the ExcelInstance.

 

Take a look at line number 3 in @Ankesh_49's post.

 

- The solution above is for using Excel as a Database.

Not sure if you are looking for that or not.

 

If you are looking to insert records from Excel into database then use the BULK Insert statement.

https://powerusers.microsoft.com/t5/Power-Automate-Desktop/Bulk-Insert-from-Excel-to-SQl/m-p/1621121

 

 

Ahammad_Riyaz
Super User
Super User

Hi @kris_T ,

I think the syntax what you are provided wrong.

select * from TableName.

here table name refers Sheetname

select * from [$Sheet1]

 

Regards

Ahammad Riyaz

--------------------------------------------------------------------------------
If this post helps answer your question, please click on “Accept as Solution” to help other members find it more quickly. If you thought this post was helpful, please give it a Thumbs Up.

 

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Community Calls Conversations

Community Calls Conversations

A great place where you can stay up to date with community calls and interact with the speakers.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Top Solution Authors
Top Kudoed Authors
Users online (5,491)