How to query using sql against a stored PAD Table

(0) Share

Report

Posted on by r96359

228

I have a stored table in Power Automate Desktop.

I am trying to use sql to update that table for modification and cannot figure out how to do this.

I thought I could use action "Execute SQL Statement" to query the PAD table but that does not seem possible.

There are thousands of records and loops will take too long.

Any ideas out there on how to query a PAD table?

Thanks!

Categories:

Power Automate Desktop

All responses (6)

Answers (0)

Sort by

r96359 228 on at

Like
a
(0)

Report
Copy link

Link copied!

In my data set I have values such as "001", "True", "False", "1212203033322" all of these get new data type assignments that mess them up if I try to update data using excel.
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @r96359

When you mention you have a stored table in PAD, I assume you are having a data table.

Have you checked out the new data table related operations?
r96359 228 on at

Like
a
(0)

Report
Copy link

Link copied!

@VJR Hi VJR, those functions aare great except that they require you to define a row for modification. That forces you to create a loop and +1 to modify each row. This is what I am trying to prevent as it takes too long.
Would be great if I could just define the column and how I want to change the data in the column.
For example: change all dates by adding 7 days to each date.
VJR 7,635 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @r96359

1. Where is the data coming from into the data table?

2. What else do you want to do apart from the adding of 7 days?

3. 001", "True", "False", "1212203033322"

Is it a csv or you wrote it this way as an example?
r96359 228 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @VJR , the data is coming from a CSV file and stored into the PAD table. Really just want to add x days to two date columns that are start date and end date. Sometimes we have a bad file and need to make these adjustments before we use the data again.
All the data I showed above (001", "True", "False", "1212203033322" ) is actual example data and all that type of data is converted when opening in excel or when importing using power query. Data converts into (1", "TRUE", "FALSE", "1.21E+12" ) . I can remove the step in power query but I have not found a way to automate power query to remove steps.
ZS440 262 on at

Like
a
(0)

Report
Copy link

Link copied!

HI. @r96359
Shouldn't we address what we can't do in the specifications in a different way?
As I mentioned in your question over here, handling CSV from PowerShell may help you.
https://powerusers.microsoft.com/t5/Power-Automate-Desktop/CSV-to-PAD-Table-How-to-query-table-using-sql-Loops-to-change/m-p/1724284#M17265

I posted on my blog about how to manipulate CSV data tables from the "Run PowerShell Script" action.
https://www.syszero.net/post/csvdate/