MS Power Automate
MS Power Automate
  • 54
  • 410 961
Excel Automation - Use SQL Queries to read and write to Excel
Do you know that there are more than 1 way to read and write to Excel files? You are probably already very familiar with the actions "Read from Excel worksheet" and "Write to Excel worksheet". But you can also use the following database actions - "Open SQL connection", "Execute SQL statement" and "Close SQL connection" - to read and write to Excel files. The added advantage of using the database actions instead of the Excel actions is that you can read and write much faster. This comes in especially helpful if you have a very large Excel file.
00:00 Introduction
00:13 The scenario
Some of the actions that we will cover in this tutorial include:
00:30 Open SQL connection
02:40 Execute SQL statement
03:25 Close SQL connection
Related videos of concepts used in this video:
• Excel automation playlist: ua-cam.com/video/XmRXjpMb2Rg/v-deo.html
• Run SQL queries on Excel files: docs.microsoft.com/en-us/power-automate/desktop-flows/how-to/sql-queries-excel
Keep in touch!
Website mspowerautomate.com/
Twitter MsAutomate
Facebook MS-Power-Automate-105388391663527
LinkedIn www.linkedin.com/in/power-automate/
#rpa #PowerAutomateDesktop #RoboticProcessAutomation #PowerAutomate #ExcelAutomation
Переглядів: 5 939

Відео

[Problem Solved] How to write date with right format in Excel using Microsoft Power Automate Desktop
Переглядів 3,8 тис.2 роки тому
[Problem Solved!] How to write the date with correct formatting in Excel using Microsoft Power Automate for Desktop In this video, we are going to teach you how to write the date with correct formatting to Excel using the Write from Excel worksheet action. There are 2 possible scenarios - the date field in Excel can be formatted as dd/mm/yyyy or mm/dd/yyyy. In the first case, you will use the C...
Struggling to read an Excel table? You might be using the wrong actions in Power Automate Desktop!
Переглядів 2,1 тис.2 роки тому
Are you having difficulty trying to read the required table from an Excel file? You might be using the wrong actions in Microsoft Power Automate for desktop! In this video tutorial, we are going to show you the difference between the actions "Get first free row on column from Excel worksheet" and "Get first free column/row from Excel worksheet". Watch this video to learn the correct actions to ...
UI Automation: Get Excel files in folder, enter data in app & write results back to Excel
Переглядів 2,9 тис.2 роки тому
In this video tutorial, we are going to show you how to automate the following steps using Microsoft Power Automate for desktop: 00:00 Introduction 00:10 The scenario 01:04 Get all Excel files (.xls, .xlsx and .xlsm) in folder 01:54 Iterate through all the Excel files using the "for each" loop 02:26 Reading Excel data (of variable size) using "get first free row/column from Excel worksheet" and...
Email Automation - How to embed Excel table in email using Microsoft Power Automate for desktop
Переглядів 24 тис.2 роки тому
In this video tutorial, we are going to show you how to embed an Excel table inside an email using Microsoft Power Automate for desktop. 00:00 Introduction 00:10 The scenario 00:44 Reading the table from an Excel file 01:57 Creating the variable which will contain the HTML table 07:27 Checking if the HTML table is created correctly 08:15 Sending the email using Outlook Related videos of concept...
How to download files from websites, including handling exceptions such as file already exists
Переглядів 15 тис.2 роки тому
In this video tutorial, we will create a desktop flow in Microsoft Power Automate for desktop that will download files from websites using a web browser. We are going to show you 2 ways of automating this task; in addition, we are going to highlight how to manipulate selectors for UI elements using the examples of the 'Save As' and 'Confirm Save As' windows 00:00 Introduction 00:10 The scenario...
Excel Automation - Add Data in 2 Columns in Microsoft Power Automate for Desktop
Переглядів 6 тис.2 роки тому
In this video tutorial, we will create a desktop flow in Microsoft Power Automate for desktop that will perform the following Excel automation: • Read the values in columns A and B • Add the 2 values together • Write the result in column C • Repeat for all the rows in the Excel file We are going to show you 2 ways of automating the above task - the first method involves looping through all the ...
Perform Excel Data Manipulations in Microsoft Power Automate for Desktop
Переглядів 4,7 тис.2 роки тому
In this video tutorial, we will create a desktop flow in Microsoft Power Automate for desktop that is able to copy data from multiple Excel worksheets, and to write the data into a consolidated worksheet. In this example, it is assumed that all the worksheets will have the same formatting. We will also show you to modify the desktop flow so that it is able to process any number of worksheets. 0...
How to convert file to Base64 in Microsoft Power Automate for Desktop
Переглядів 6 тис.2 роки тому
Let's say you want to use Microsoft Power Automate for Desktop to upload files into a database that only accepts base64 strings. You'll need to automate converting the files to base64. Here's how you can do that, as well as converting the base64 strings back to file. 00:00 Introduction 00:12 The scenario 00:40 convert file to base64 in Microsoft Power Automate for Desktop 04:58 convert base64 t...
Create dictionary variable or custom object in Power Automate cloud flow
Переглядів 3,2 тис.2 роки тому
Earlier, we've made a video to show you how to create dictionary variables in Microsoft Power Automate for Desktop (ua-cam.com/video/7WtOoTWDLBE/v-deo.html). But how about creating dictionary variables (custom object) when you're working with Power Automate cloud flow? We'll show you how in today's video: 00:00 Introduction 00:47 Initialize variables 02:24 Read Excel file containing key and val...
Custom Form Designer in Microsoft Power Automate for Desktop
Переглядів 7 тис.2 роки тому
The custom form designer is one of the latest Microsoft Power Automate for Desktop updates in February 2022. With the new Display custom form action, you can now create your own user dialogue based on the Adaptive Cards technology. This action comes with a Custom form designer that is both intuitive and easy to use. By combining different input elements, controls and actions, the designer lets ...
Convert JSON to Excel in Microsoft Power Automate for Desktop
Переглядів 12 тис.2 роки тому
Do you receive JSON files that you'd like to convert to Excel tables? In this video, we show you how to convert JSON to Excel tables in Microsoft Power Automate for Desktop. Doing this transforms JSON file data into Excel format so that it becomes very easy to see, read, and do subsequent data manipulation such as filtering, applying formulas, etc. 00:00 Introduction 00:11 Sample JSON file used...
Extract tables from PDF - Microsoft Power Automate for Desktop Tutorial
Переглядів 25 тис.2 роки тому
Recently, there's a new action in Microsoft Power Automate for Desktop called "Extract tables from PDF". That's right-now you have an option to extract the tables from PDFs directly with an action, instead of having to go through complicated text manipulation to do it. If you're interested, you can check out the text manipulation method here: ua-cam.com/play/PLtS0QJFKl_DZa30UIUeMmcQ0-vR5areKR.h...
How to create dictionary variables - Microsoft Power Automate Tutorial
Переглядів 6 тис.2 роки тому
If you're creating a bot involving values that may change at some point in time, you might want to consider using a dictionary variable type. Instead of hardcoding the values in the script, you can store this information into an Excel file and just specify the "key" of the dictionary variable in your script. This way, the process is more robust and users can change the information anytime. In t...
Integrate Cloud flows with Desktop flows - Microsoft Power Automate tutorial
Переглядів 4,8 тис.2 роки тому
The ability to seamlessly integrate cloud flows (API-based automation) with desktop flows (Robotic Process Automation or RPA) is one of the major strengths of Power Automate. You can choose from all kinds of triggers from the web portal, obtain data via cloud flows, and trigger desktop flows that automate tasks on your device with these data. In this video, we'll show you how to integrate cloud...
Batch download Google search images (full resolution)-Microsoft Power Automate for Desktop Tutorial
Переглядів 1,5 тис.2 роки тому
Batch download Google search images (full resolution)-Microsoft Power Automate for Desktop Tutorial
Dynamic Selectors for desktop apps-Microsoft Power Automate for Desktop Tutorial
Переглядів 4,3 тис.2 роки тому
Dynamic Selectors for desktop apps-Microsoft Power Automate for Desktop Tutorial
Integrate Power Automate and Microsoft Teams to obtain approval-Microsoft Power Automate Tutorial
Переглядів 1,3 тис.2 роки тому
Integrate Power Automate and Microsoft Teams to obtain approval-Microsoft Power Automate Tutorial
Discover Optimization Opportunities with Process Advisor-Microsoft Power Automate Tutorial
Переглядів 1,5 тис.2 роки тому
Discover Optimization Opportunities with Process Advisor-Microsoft Power Automate Tutorial
Microsoft Power Automate Tips and Tricks Part 3
Переглядів 6792 роки тому
Microsoft Power Automate Tips and Tricks Part 3
How to create a Dynamic Selector (Web automation)-Microsoft Power Automate for Desktop Tutorial
Переглядів 11 тис.2 роки тому
How to create a Dynamic Selector (Web automation)-Microsoft Power Automate for Desktop Tutorial
Microsoft Power Automate Tips and tricks Part 2
Переглядів 9552 роки тому
Microsoft Power Automate Tips and tricks Part 2
Top 30 Microsoft Power Automate Tips & Tricks (including Power Automate for Desktop) - Part 1 of 3
Переглядів 1,9 тис.2 роки тому
Top 30 Microsoft Power Automate Tips & Tricks (including Power Automate for Desktop) - Part 1 of 3
Retrieve emails in Microsoft Power Automate for Desktop, and save and process the attachments
Переглядів 10 тис.2 роки тому
Retrieve emails in Microsoft Power Automate for Desktop, and save and process the attachments
Extract PDF Invoice with Text Manipulation in Microsoft Power Automate Desktop (Part 3 of 3)
Переглядів 3,2 тис.2 роки тому
Extract PDF Invoice with Text Manipulation in Microsoft Power Automate Desktop (Part 3 of 3)
Extract PDF Invoice with Text Manipulation in Microsoft Power Automate Desktop (Part 2 of 3)
Переглядів 4,6 тис.2 роки тому
Extract PDF Invoice with Text Manipulation in Microsoft Power Automate Desktop (Part 2 of 3)
Extract PDF Invoice with Text Manipulation in Microsoft Power Automate Desktop (Part 1 of 3)
Переглядів 14 тис.2 роки тому
Extract PDF Invoice with Text Manipulation in Microsoft Power Automate Desktop (Part 1 of 3)
Scrape images from websites and configure folder/file name conventions
Переглядів 8 тис.2 роки тому
Scrape images from websites and configure folder/file name conventions
Install On-Premises Data Gateway: Connect cloud flows and desktop flows in Power Automate
Переглядів 9 тис.2 роки тому
Install On-Premises Data Gateway: Connect cloud flows and desktop flows in Power Automate
Installing Power Automate Desktop - What you need to know
Переглядів 2222 роки тому
Installing Power Automate Desktop - What you need to know

КОМЕНТАРІ

  • @atrevisi
    @atrevisi 25 днів тому

    Excellent example, could you explain an additional feature like make a loop for process a whole folder plenty of pdf files and put the data in the same excel work sheet?? thanks in advance

  • @NiteliysCorner
    @NiteliysCorner Місяць тому

    Why no body telling me why excel failed to open

  • @Semaj1985
    @Semaj1985 2 місяці тому

    Hi, Can you please teach us to loop till the last row of cell by using power automate?

  • @madeshwaranmaddy2875
    @madeshwaranmaddy2875 2 місяці тому

    CSV to Json Conversion???

  • @deeptiramdas4764
    @deeptiramdas4764 2 місяці тому

    Extremely useful video. Your video was excellent to follow. However I was unable to connect to Gmail due to multiple authentication. Once i generated an APP password the issue got resolved. This is the link i followed - ua-cam.com/video/MmgIyRmynbg/v-deo.html

  • @YangLi-bf3tf
    @YangLi-bf3tf 2 місяці тому

    Thanks for the Tutorial, it is helpful, but how to change the style of the table in email body to make it more goodlooking?

  • @nam8274
    @nam8274 2 місяці тому

    Thank you a lot

  • @koialkarpranusha
    @koialkarpranusha 2 місяці тому

    THANKS A LOT

  • @firdaussnoordin1959
    @firdaussnoordin1959 2 місяці тому

    Thank you! It is a great tutorial. I have a scenario whereby I have an excel with a multiple row count. How can I make the looping value dynamic based on the excel row count? Thanks in advance.

  • @jesperaverycuyugan4488
    @jesperaverycuyugan4488 3 місяці тому

    Hello sir, how can we loop through only unique values on the excel data or data table? For example if the data table contains identical rows how can we instruct power data automate to filter each unique value? Thank you

  • @liakat26
    @liakat26 3 місяці тому

    Your videos are amazing. Thank you so much.

  • @aradman2k
    @aradman2k 4 місяці тому

    Thank you.. how can I copy the value of any cell (e.g. first name then last name) and copy them in online system using record function

  • @shakisalim4016
    @shakisalim4016 4 місяці тому

    I need to Extract the table from PDF but PDF table is not a table format it looks like table,i used extract table from PDF unable to get entire data,any other option

  • @soundaryaguduri3506
    @soundaryaguduri3506 4 місяці тому

    Thankyou for this video, I followed the same way but header are not added for my excel. how to solve this?

  • @aa-xl7go
    @aa-xl7go 4 місяці тому

    Arshdeep

  • @aa-xl7go
    @aa-xl7go 4 місяці тому

    Arshdeep

  • @noorfatinnajwa8971
    @noorfatinnajwa8971 4 місяці тому

    hi i need more video like this but have many loops

  • @josevaldesv
    @josevaldesv 5 місяців тому

    This is very helpful. Thank you. Very well explained. How do you separate the information in this row? Item Quantity Unit Cost Amount (.+) or something like that?

    • @josevaldesv
      @josevaldesv 5 місяців тому

      I found the answer on your part 2!!!

  • @theprofessor4487
    @theprofessor4487 5 місяців тому

    Great video! Two questions arose while watching your video. If I create a "For each" my datatable contains blank lines. Every other line is blank. How do I remove the blank lines? Secondly, I would like to write the data in my datatable to another Excel file in specified cells. Is this possible?

  • @MonteBStJohns
    @MonteBStJohns 5 місяців тому

    This looks like exactly what I need but I'm getting the following error "Failed to save attachment(s) for message with uid 121" at line 9. Any ideas?

    • @MonteBStJohns
      @MonteBStJohns 5 місяців тому

      If I do NOT CHECK Save Attachments - then I can Retrieve email and write to text file for example the Email Body content. Just errors when I CHECK the Save attachment?

    • @MonteBStJohns
      @MonteBStJohns 5 місяців тому

      JUST FYI - it seems like the number of emails to iterate impacts the Error. I have a number of ways to filter/loop how many emails to go thru - just haven't figured out what the number to run in any one 'batch''!

  • @ynasmalaca9059
    @ynasmalaca9059 5 місяців тому

    I don't see the Transaction ID being written back to the file.

  • @accentureinc1951
    @accentureinc1951 5 місяців тому

    melayu ke😂

  • @YinTungCheng
    @YinTungCheng 5 місяців тому

    I'm stuck on the populate text field step. WHen I run the flow Power Automate could not find the UI element.

    • @gabrieledid9568
      @gabrieledid9568 3 місяці тому

      Same here, any other solution or how did you fix it?

  • @liakatali4063
    @liakatali4063 6 місяців тому

    brilliant method to extract complex invoice data. Thank you sir.

  • @AppuChandran-f7g
    @AppuChandran-f7g 6 місяців тому

    I need the overlay to automatically close after 1 minutes is it possible

  • @adoleh
    @adoleh 6 місяців тому

    Thank you very much; simple and easy to follow.

  • @anwarkrg9292
    @anwarkrg9292 6 місяців тому

    hi, how to automate download from web with login and input password?

  • @salvadorvelascotelle
    @salvadorvelascotelle 7 місяців тому

    Hi. How did you made the regular expression? "([^"]+?"\s*:

  • @parmeshwarbhadange8689
    @parmeshwarbhadange8689 7 місяців тому

    Thank you so much man it worked awesome work🎉 God bless you

  • @oneBoredFish
    @oneBoredFish 7 місяців тому

    Worked perfectly. Exactly what I needed. Thank you!

  • @rolandobrabant3345
    @rolandobrabant3345 8 місяців тому

    hi, great video. My problem is I cannot select the "yes" button because it is hidden behind another window. Any thoughts.

  • @rafaelataide5403
    @rafaelataide5403 8 місяців тому

    Thank you! I was so annoyed by this "bug" and not fiding any good awnser in the community. You, sir, is a legend!

  • @AbhijeetSinghs
    @AbhijeetSinghs 8 місяців тому

    Thanks it was helpful

  • @AnitakitchenDelhi
    @AnitakitchenDelhi 8 місяців тому

    Can we use this in a pad? Without creating a separate dictionary for desktop flow

  • @JesusSanchez-dv2ps
    @JesusSanchez-dv2ps 8 місяців тому

    amazing! thanks!

  • @Smddlvvs
    @Smddlvvs 9 місяців тому

    How do we save all the data in single excel sheet and how to give multiple pdfs as input

  • @joellopez889
    @joellopez889 9 місяців тому

    Thanks dude, it is a good option.

  • @tharic007
    @tharic007 9 місяців тому

    I can't thank you enough for this one, I simply couldn't find anything related to this type of iteration and was having to do a long and tedious job manually, this just saved my day (and project deadline as well)

  • @suraj_singh321
    @suraj_singh321 9 місяців тому

    Hi sir make a videp on run macro code without maintaing macro excel file through powershell script in power automate desktop

  • @sivadarling6256
    @sivadarling6256 10 місяців тому

    Great work 👏

  • @123456789santia
    @123456789santia 10 місяців тому

    Does this work for 1000 PDF?

  • @simonwraith3488
    @simonwraith3488 10 місяців тому

    how did you setup the If condition - I can see CurrentItem[' '] but what is Second operand <> too??

    • @simonwraith3488
      @simonwraith3488 10 місяців тому

      Any chance you can upload the code so I can copy and paste to a test flow. I can then make the necessary amendments to fit my Excel document.

  • @hasham77
    @hasham77 10 місяців тому

    Have to try it

  • @AnkitShikhare
    @AnkitShikhare 11 місяців тому

    I have a excel file which contains a range Of Id Numbers which i have to search one by one on our company's website there a search bar on it and click show button after clicking show button i have save that specific Page as PDf with the name range i got in excel one by one can you help me with that....

  • @tmt6571
    @tmt6571 11 місяців тому

    thank you so much

  • @khalidkholaidy
    @khalidkholaidy 11 місяців тому

    What about if I have multi sheets in my file?

  • @ok_AI_
    @ok_AI_ 11 місяців тому

    Is it possible to upload files to a web page using a similar process?

  • @daluadavi
    @daluadavi Рік тому

    very nice!!! thank you for class!!!

  • @arkapravadutta8319
    @arkapravadutta8319 Рік тому

    Can you please post the entire thing as a file so that we can download and use jt

  • @davidediguida8968
    @davidediguida8968 Рік тому

    Does the recieving user (user who gets the flow shared with him) need to have a premium plan too?