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

[r96359]

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!

[Kaif_Siddique]

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:

Hope this will help.

Regards

Kaif

[r96359]

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.

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

[r96359]

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]

Hi @r96359

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

[r96359]

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]

@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]

Hi. @r96359 

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

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

[rahullakshmanan]

Hi,

I have written a post on how to run sql queries on CSV files in PAD. You can check it out here.

Run SQL Magic on CSV Files with Power Automate Desktop! 💻✨ | by Rahul Lakshmanan | Jan, 2024 | Medi...