cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
r96359
Helper V
Helper V

CSV to PAD Table - How to query table using sql? Loops to change column data takes way to long.

I need to change a date (start date) in a column from a CSV file by adding 7 days to the date.

I'm stuck on knowing the best way to do this.

Using PAD I have imported the CSV file into a table called CSVtable.

Now I want to perform a SQL update against said table and cannot find a way to do this.

There is a "Execute SQL Statement" but you can't apply that to PAD's own tables? That seems very odd.

I tried uing "Update Data Table Item" but you have to specify a row. I want to change all rows of data from a date column.

Any type of loop would take forever as there can be about > 1,000 records.

 

Any ideas?

Thx!

7 REPLIES 7
Kaif_Siddique
Impactful Individual
Impactful Individual

Hi,

 

You can create a temporary excel file and use excel as database. Refer below link for more info:

Connection String for Csv file( csv as a database) - Power Platform Community (microsoft.com)

 

Please refer below PAD flow for excel as database:

Kaif_Siddique_0-1660853524868.png

 

Hope this will help.

 

Regards

Kaif

r96359
Helper V
Helper V

The issue to using excel is that excel automatically applies data types to a csv file during import so numbers with leading zeros needed like 001. Convert to 1. a value of False gets converted to FALSE. etc. etc.

 

I can already convert the csv into a PAD table.

r96359_0-1661185572476.png

I am very shocked I cannot use SQL to query Power automate Desktop's own table.

r96359
Helper V
Helper V

Is there any other possible method?

I also looked at using Power Automate Desktop to open excel within power query and remove the data type assignment step but PAD doesn't support recording or build UI elements from Power Query.

ZS440
Resolver III
Resolver III

Hi @r96359

If you want to manipulate data in a CSV file, it is easy to use the PowerShell script execution action.

r96359
Helper V
Helper V

Hi @ZS440 , Would it work where I need to look at a date column and 7 days to each date?

Can you share any example you might already have?

Thanks!

VJR
Super User
Super User

@r96359 

 

Check out how this goes.

- Set a connection string to the CSV file using the "Open SQL Connection" action

- In the "Execute SQL statement" write a Select query with the "addition of dates SQL function" for the date columns.

- Since this is a Select statement we are not directly writing to the CSV so no messing up with Excel, but the result will be returned in the resulting CSVTable datatable.

 

ZS440
Resolver III
Resolver III

Hi. @r96359 

CleanShot 2022-08-27 at 20.17.10@2x.png

I posted on my blog about how to manipulate CSV data tables from the "Run PowerShell Script" action.

https://www.syszero.net/post/csvdate/

 

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Top Solution Authors
Top Kudoed Authors
Users online (1,956)