Skip to main content

Excel as a Database in Power Automate Desktop | LazyBots

 



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.

Labels:

Comments

Post a Comment

Popular posts from this blog

Convert to PDF using API | Power Automate Desktop

  In this Power Automate Desktop learning series video, we are learning how to convert any kind of documents to PDF using Muhimbi API for document conversion. This video covers: ------------------------------------------------------ 00:00 - Introduction 03:00 - File to base64 string conversion using Powershell script 05:00 - Invoke web service (API call) 09:20 - Create file from Base64 string 10:30 - Output Actions used: Get files in folder Foreach Run Powershell script Trim Get File Path part Invoke web service Convert Json to custom object How to get Muhimbi API Key: Visit Muhimbi website Fill in your details You'll get the API key and other details in your email How to convert file to Base64 string: Visit official microsoft documentation on power shell script. Happy Automation.
Post a Comment
Read more

Captcha solving using Power Automate Desktop | LazyBots

  In this tutorial, we are learning Captcha solving using Power Automate Desktop. This video covers: 2Captcha API Invoke web service action for accessing API Run Powershell script action to convert Captcha image to Base64 string basic web automation actions To create an account on 2captcha, click here  https://2captcha.com?from=6396966 get the API key from the dashboard and start solving captchas. Happy Automation :)
Post a Comment
Read more