CSV Automation in Power Automate Desktop

KT Sessions November 15, 2024

CSV Automation in Power Automate Desktop

CSV (Comma Separated Values) files are a common format for storing data in a structured way. Whether it’s customer information, product inventories, or sales reports, CSV files make it easy to import and export data between different systems. In Power Automate Desktop (PAD), automating tasks involving CSV files can save you time and eliminate manual errors.

In this blog post, we'll walk you through how to automate working with CSV files in Power Automate Desktop. You'll learn how to:

  • Read data from CSV files.
  • Write data to CSV files.
  • Manipulate and process CSV data.

By the end of this post, you'll be able to automate CSV tasks and integrate them into your workflows efficiently!



What Is a CSV File?

A CSV file is a simple text file where each line represents a row of data, and the values within each row are separated by commas. Here's an example of a CSV file:

graphql
Name,Email,Phone
John Doe,john@example.com,123-456-7890
Jane Smith,jane@example.com,987-654-3210

Each column in the CSV represents a different type of data, such as a name, email address, or phone number. In Power Automate Desktop, you can automate tasks like:

  • Reading the CSV to extract specific data.
  • Writing data into a CSV file.
  • Modifying or appending data to a CSV file.

Key Actions for Working with CSV Files in PAD

Power Automate Desktop provides specific actions to help you automate CSV-related tasks. These are the most commonly used actions for handling CSV files:

  1. Read from CSV File: Reads data from a CSV file and stores it in a Data Table.
  2. Write to CSV File: Writes data from a Data Table into a CSV file.
  3. Append to CSV File: Appends data to an existing CSV file.
  4. Get Column from Data Table: Extracts a specific column from a Data Table.
  5. Get Row from Data Table: Extracts a specific row from a Data Table.

Example 1: Reading Data from a CSV File

Let’s start with a basic example: reading data from a CSV file and storing it in a Data Table.

Step 1: Create or Locate the CSV File

For this example, let’s say we have a CSV file contacts.csv with the following data:

graphql
Name,Email,Phone
John Doe,john@example.com,123-456-7890
Jane Smith,jane@example.com,987-654-3210

Step 2: Add the "Read from CSV File" Action

  1. In Power Automate Desktop, add the Read from CSV File action.
  2. Specify the File Path of the CSV file. For example, C:\Users\YourName\Documents\contacts.csv.
  3. Store the output in a variable (e.g., CSVDataTable). This will create a Data Table that holds all the rows and columns from the CSV file.

Step 3: Process the Data

Now that the data is stored in a Data Table, you can access the rows and columns to work with the data. For example, you can use a For Each loop to go through each row of the table.

  1. Add a For Each loop to iterate over the CSVDataTable.
  2. Inside the loop, you can extract individual columns using the Get Column from Data Table action.

Example:

plaintext
For Each Row in CSVDataTable:
    Get Column from Data Table: Name
    Get Column from Data Table: Email
    Get Column from Data Table: Phone

This will allow you to process each person's contact information (name, email, phone) in your workflow.

Example 2: Writing Data to a CSV File

Let’s say you need to write a set of data into a CSV file. For instance, you’ve gathered some processed data and now want to save it in CSV format.

Step 1: Create a Data Table

Before you can write to a CSV file, you need to create a Data Table. You can manually add data to a Data Table or extract it from another source (like an Excel file or a database).

  1. Add the Create Data Table action and define the columns you want to use (e.g., Name, Email, Phone).
  2. Use the Add Row to Data Table action to add rows of data to the Data Table.

Example:

plaintext
Create Data Table: Columns = Name, Email, Phone
Add Row to Data Table: John Doe, john@example.com, 123-456-7890
Add Row to Data Table: Jane Smith, jane@example.com, 987-654-3210

Step 2: Write Data to CSV File

Now that you have a Data Table, you can use the Write to CSV File action to write this data into a CSV file.

  1. Add the Write to CSV File action.
  2. Specify the file path (e.g., C:\Users\YourName\Documents\output.csv).
  3. Set the Data Table to the variable containing your data (e.g., DataTableContacts).

This will write all the rows and columns from the Data Table into the output.csv file.

Example 3: Appending Data to an Existing CSV File

Sometimes you need to add new rows of data to an existing CSV file without overwriting the existing data. PAD allows you to append data to a CSV file.

Step 1: Read the Existing CSV File

Use the Read from CSV File action to read the existing file and store it in a Data Table.

  1. Add the Read from CSV File action.
  2. Specify the path of the existing CSV file you want to append to (e.g., C:\Users\YourName\Documents\contacts.csv).
  3. Store the data in a Data Table (e.g., ExistingDataTable).

Step 2: Add New Rows

Next, use the Add Row to Data Table action to add new rows to the ExistingDataTable.

Example:

plaintext
Add Row to Data Table: Alice Cooper, alice@example.com, 111-222-3333

Step 3: Write the Updated Data to CSV

Finally, use the Write to CSV File action to save the updated Data Table back to the same CSV file.

  1. Specify the same file path where the CSV file is located.
  2. Set the Data Table to the updated one (e.g., ExistingDataTable).

This will append the new data to the end of the CSV file without overwriting the previous content.

Example 4: Filtering and Processing Data in a CSV File

You may want to process CSV data by filtering out certain rows or performing calculations based on the data. Let’s say you have a CSV file with sales data, and you want to filter out all sales that are below a certain threshold.

Step 1: Read the CSV File

Use the Read from CSV File action to load the sales data into a Data Table.

  1. Add the Read from CSV File action and specify the file path (e.g., C:\SalesData.csv).
  2. Store the data in a variable (e.g., SalesDataTable).

Step 2: Filter Rows Based on Criteria

Use the Filter Data Table action to filter the rows based on your criteria. For example, if you want to keep only sales above $1000, set the filter condition.

plaintext
Filter rows: SalesAmount > 1000

Step 3: Process and Save the Filtered Data

After filtering, you can process the filtered data, perform calculations, or save it to a new CSV file using the Write to CSV File action.

Conclusion

CSV automation in Power Automate Desktop can significantly improve your workflow by making it easier to read, write, and manipulate CSV data. Whether you're working with customer lists, generating reports, or filtering large datasets, Power Automate Desktop provides the tools you need to automate CSV file tasks with ease.

In this post, we've covered:

  • How to read data from a CSV file.
  • Writing data to a CSV file.
  • Appending data to an existing CSV file.
  • Filtering and processing CSV data.

Now that you have the basics, you can start automating your own CSV-related tasks and integrate them into your larger workflows.

KT Sessions

Posted by KT Sessions