Power Automate for desktop ”Tips on handling CSV data tables from 'Run PowerShell Script' action”

Introduction

When manipulating CSV data tables in Power Automate for desktop, it is possible to convert them to EXCEL files and use the “Execute SQL Statement” action. However, if the data table contains columns such as “007” and “008” that are to be treated as text, they will be converted to numbers during the EXCEL conversion.

So here is a flow to manipulate a CSV data table using the “Run PowerShell Script” action; PowerShell can directly read CSV files and manipulate data tables just like SQL. In this case, we will manipulate the date columns while maintaining the columns we want to treat as text, such as “007” and “008”.

sample.csv

RowID,Name,TextValue,Created,Modify
dd3341fb-bb89-ec11-93b0-000d3a40692d,AZX,00765,2022/02/09 15:21,2022/02/09 15:21
1cdf548a-8468-ec11-8943-000d3a40fca2,XZZ,0345,2021/12/29 8:51,2021/12/29 8:51
ef162ef4-8468-ec11-8943-000d3a40fca2,XZy,0385,2021/12/29 8:54,2021/12/29 8:54
d007bc9b-0967-ec11-8f8f-002248622feb,MVS,0787,2021/12/27 11:39,2021/12/27 11:39
236685ce-0967-ec11-8f8f-002248622feb,PAD,00888,2021/12/27 11:40,2021/12/27 11:40

Manipulate the date in the “Modify" column.

Note

• Please do so at your own risk.
• Windows 10 Pro 21H2
• Power Automate for desktop 2.23.114.22217
• Information for August 2022.

Flow Description

Create a sample.csv on the desktop. The key point of this flow is to create a temporary file, manipulate the data table with the “Run PowerShell script" action, and write it to the temporary file.

1. Get special folder Get the path to the desktop folder.

2. Get temporary file Create temporary file.

   

3. Run PowerShell script Run the PowerShell script. Don’t forget -NoTypeInformation when writing to the temporary file.

   

# Read CSV
$datatable = Import-Csv "%SpecialFolderPath%\sample.csv" -Encoding utf8
# Change Modify column to datetime object
$datatable | ForEach-Object{$_.Modify = [datetime]($_.Modify)}
# +7 days to the value in the Modify column
$datatable | ForEach-Object{$_.Modify = ($_.Modify).AddDays(+7)}
# Write to temporary file
$datatable | Export-Csv -Path "%TempFile%" -NoTypeInformation -Encoding utf8

4. Read from CSV file Reads a data table from a temporary file.

   

DateTime Manipulation Options

Reference

DateTime Struct In addition to DateTime.AddDays(Double) Method used in this case, there are many other methods for datetime operations.

For example, the ToString method can be used to change the format.

Data Table Operations

You can select columns with Select-Object and filter values by using Where-Object.

• Selecting Parts of Objects (Select-Object)
• Removing Objects from the Pipeline (Where-Object)

Conclusion

By using the “Run PowerShell Script” action, we manipulated the CSV data table while maintaining the columns we wanted to treat as text. In this case, we used a temporary file, but sometimes it is more convenient to output the data in JSON, depending on the data.

Power Automate for desktop ”Tips on handling data tables I wanted to know first.”

This article was written in conjunction with a post on the Microsoft Power Automate Community Forum.

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