In this Power Automate Learning series post, we are going to access microsoft excel as a database and perform SQL operations.
Actions Covered in this session:
- Open SQL Connection
- Used to connect to various kind of data sources
- Execute SQL Statements
- Used to specify which SQL statements to be executed in the data source
- the Sheet name will be the table name in Query, and don't forget to add a $ at the end of table name. for eg: If sheet name is Employees, then the query will look like SELECT * FROM [Employees$]
- Cose connection
- Used to close connection
- After every operation, don't forget to close the SQL connection.
In this session, in Open SQL Connection action, we have to provide a connection string.
A connection string will look like this:
Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\shank\Music\test.xlsx;Extended Properties="Excel 12.0 Xml;HDR=YES";Persist Security Info=False
For more information about connection strings, and options Visit Microsoft official documentation here
Note : Unfortunatley, we can't perform a DELETE operation using SQL. Instead we can empty the cells using update query.
Comments
Post a Comment