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.
You can create a temporary excel file and use excel as database. Refer below link for more info:
Please refer below PAD flow for excel as database:
Hope this will help.
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.
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.
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.