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

(0) Share

Report

Posted on by r96359

228

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!

Categories:

Power Automate Desktop

I have the same question (0)

All responses (8)

Answers (0)

Kaif_Siddique 2,108 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
r96359 228 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
r96359 228 on at

Like (0)

Report

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.

Was this reply helpful? Yes No
ZS440 262 on at

Like (0)

Report

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

Was this reply helpful? Yes No
r96359 228 on at

Like (0)

Report

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!

Was this reply helpful? Yes No
VJR 7,635 on at

Like (0)

Report

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

Was this reply helpful? Yes No
ZS440 262 on at

Like (0)

Report

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/

Was this reply helpful? Yes No
rahullakshmanan 30 on at

Like (0)

Report

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 | Medium

Was this reply helpful? Yes No