Reading and Writing to CSV and Text Files in Power Automate Desktop: Parsing Delimiters, Handling Headers, and Looping Through Records

Introduction

Picture this: your company's legacy inventory system exports a flat CSV file every morning at 6 AM — 3,000 rows, comma-separated, with a header row at the top. Your job is to read that file, filter out discontinued items, and push the remaining records into a web portal one by one. Nobody wants to do that manually. That's exactly the kind of task Power Automate Desktop was built to handle.

CSV (Comma-Separated Values) and plain text files are the lingua franca of data exchange. They show up everywhere: bank exports, ERP system dumps, legacy application outputs, lab instrument readings, HR roster files. Even when the rest of the world has moved to APIs and databases, someone, somewhere, is still dropping a .csv onto a network share and expecting another system to pick it up. Being able to read, parse, and write those files inside a desktop flow is one of the most immediately useful skills you can add to your automation toolkit.

By the end of this lesson, you'll be able to open a CSV or text file in Power Automate Desktop, split it into structured rows and columns, skip or use the header row correctly, loop through every record to perform actions, and write new data back to a file. We'll use a realistic scenario throughout: processing a daily sales report exported from a point-of-sale system.

What you'll learn:

• How to read a text file into a variable and understand what you actually get back
• How to parse delimited content using the Split text action and handle different delimiters (comma, pipe, tab)
• How to detect and handle header rows without corrupting your data
• How to loop through records and access individual fields by index
• How to write processed results back to a CSV or text file

---

Prerequisites

This lesson assumes you've already installed Power Automate Desktop and can create and run a basic flow. If that's new to you, start with Getting Started with Power Automate Desktop: Installing, Recording, and Running Your First Desktop Flow before continuing here.

You should also have a working understanding of variables and lists in PAD. If terms like "list," "index," and "loop variable" feel unfamiliar, read Variables, Lists, and Data Tables in Power Automate Desktop: A Complete Practitioner's Guide first — this lesson builds directly on those concepts.

---

Understanding What a CSV File Actually Is

Before touching any actions in the designer, let's make sure you have a clear mental model of what you're working with.

A CSV file is just a plain text file. Open one in Notepad and you'll see something like this:

OrderID,CustomerName,Product,Quantity,UnitPrice,Status
1001,Fernanda Oliveira,Bluetooth Headset,2,49.99,Completed
1002,James Whitfield,USB-C Hub,1,34.95,Pending
1003,Aiko Tanaka,Mechanical Keyboard,1,89.00,Completed
1004,Ravi Patel,Webcam HD,3,62.50,Cancelled

Each line is a record (a row of data). Within each line, values are separated by a delimiter — in this case, a comma. The first line is often a header row containing column names rather than actual data.

Power Automate Desktop doesn't have a magical "read CSV" button that hands you a tidy table. Instead, you read the file as raw text, then use string manipulation actions to break it into the structure you need. Once you understand that two-step process — read then parse — everything else clicks into place.

---

Step 1 — Reading the File Into a Variable

Open Power Automate Desktop and create a new flow. Call it something like Process Daily Sales Report.

In the Actions panel on the left, expand the File category. Find the action called Read text from file and drag it onto your canvas.

Configure the action with these settings:

• File path: The full path to your CSV file, for example C:\Reports\daily_sales.csv
• Store content as: Choose Single text value — this reads the entire file into one string variable
• Encoding: Leave this as UTF-8 unless you know your file uses a different encoding (Latin-1 is common for older systems)

The action will produce a variable — by default named FileContents — that holds the entire raw text of the file as a single string. Think of it as having copied everything from Notepad and pasted it into one variable.

At this point, FileContents looks like one big blob of text with newline characters (\n or \r\n) separating the rows. That's your starting point.

---

Step 2 — Splitting the File Into Lines (Records)

Now you need to break that blob into individual rows. You'll use the Split text action, found in the Text category of the Actions panel.

Configure it like this:

• Text to split: %FileContents%
• Delimiter type: Choose Standard and select New line from the dropdown

The action produces a list variable — call it AllRows. Each item in the list is one line of the file. So AllRows[0] is the first line (your header row), AllRows[1] is the first data row, and so on.

If your file uses a pipe or tab delimiter for the rows themselves (unusual, but it happens), you can choose Custom delimiter and enter whatever character separates your rows.

---

Step 3 — Handling the Header Row

This is where beginners make their first major mistake: they loop through every item in AllRows including the header, then wonder why their first "record" has column names instead of values.

You have two strategies.

Strategy A — Skip by index. Start your loop at index 1 instead of 0. The header is at index 0 and you just never touch it. Simple and effective.

Strategy B — Extract and use the header. Before your loop, grab AllRows[0], split it by your column delimiter (comma in our case), and store the resulting list as Headers. Then start your data loop at index 1. This is useful when you need to reference columns by name or write the header into your output file.

For our sales report scenario, we'll use Strategy B because we want to preserve the header when we write our output file.

Add another Split text action:

• Text to split: %AllRows[0]%
• Delimiter type: Custom, value: ,
• Save result as: Headers

Now Headers[0] is OrderID, Headers[1] is CustomerName, Headers[2] is Product, and so on.

---

Step 4 — Looping Through Records

Here's where the real work happens. You'll use a For each loop to iterate through AllRows, but you need to skip the header. The cleanest way to do this is with a Loop action (which gives you index control) rather than For each.

In the Actions panel, find Loops → Loop. Configure it:

• Start from: 1 (skipping index 0, the header)
• End to: %AllRows.Count - 1%
• Increment by: 1
• Loop index variable: RowIndex

Inside the loop, add a Split text action to break the current row into individual fields:

• Text to split: %AllRows[RowIndex]%
• Delimiter type: Custom, value: ,
• Save result as: CurrentRow

Now inside each loop iteration, CurrentRow[0] is the OrderID, CurrentRow[1] is the CustomerName, CurrentRow[2] is the Product, CurrentRow[3] is the Quantity, CurrentRow[4] is the UnitPrice, and CurrentRow[5] is the Status.

You can assign these to named variables to make your flow more readable:

• Use Set variable → Name: OrderID, Value: %CurrentRow[0]%
• Use Set variable → Name: CustomerName, Value: %CurrentRow[1]%
• And so on...

This is optional but makes downstream actions much easier to understand when you come back to the flow six months later.

---

Step 5 — Acting on Each Record

Now that you have clean field values, you can do something with them. In our sales scenario, let's say we want to process only "Completed" orders — we'll skip Pending and Cancelled records, and for each Completed order we'll calculate the line total and prepare it for output.

Inside your loop, after the split action, add an If condition:

• First operand: %CurrentRow[5]%
• Operator: =
• Second operand: Completed

Inside the If block, add a Set variable action:

• Name: LineTotal
• Value: %CurrentRow[3] * CurrentRow[4]%

PAD will automatically evaluate the arithmetic expression. CurrentRow[3] is Quantity (a number stored as text) and CurrentRow[4] is UnitPrice. PAD is usually smart enough to coerce text to numbers for arithmetic, but if you're getting errors, add a Convert text to number action before multiplying.

This pattern — split the row, check a condition, do something — is the backbone of virtually every CSV-processing flow you'll ever build. Whether you're entering records into a web portal (check out Web Automation in Power Automate Desktop: Browser Actions, Form Filling, and Data Extraction), pushing data into a desktop application, or aggregating totals, this loop structure handles it.

---

Step 6 — Building and Writing Your Output File

Reading and processing records is only half the job. Now you need to write results back to a file. Let's create a filtered output CSV containing only the Completed orders, plus a new LineTotal column.

Setting up the output string

Before your loop, create a variable to accumulate your output:

• Set variable → Name: OutputContent, Value: OrderID,CustomerName,Product,Quantity,UnitPrice,LineTotal

This seeds your output with a header row. Note we're adding LineTotal as a new column.

Appending inside the loop

Inside your loop, within the "Completed" If block, after calculating LineTotal, add another Set variable action to build a new CSV row:

• Name: NewRow
• Value: %CurrentRow[0]%,%CurrentRow[1]%,%CurrentRow[2]%,%CurrentRow[3]%,%CurrentRow[4]%,%LineTotal%

Then append this row to your output string using another Set variable:

• Name: OutputContent
• Value: %OutputContent%%NewLine%%NewRow%

%NewLine% is a built-in PAD variable that inserts a proper line ending. Always use this instead of trying to type \n literally — it ensures your output file will be readable across systems.

Writing the file

After your loop completes, use the Write text to file action from the File category:

• File path: C:\Reports\completed_orders.csv
• Text to write: %OutputContent%
• Append new line: Off (you've already managed your own line endings)
• If file exists: Overwrite (so each run produces a fresh output)
• Encoding: UTF-8

Run the flow and then open completed_orders.csv in Excel. You'll see a clean, properly formatted file with only the Completed orders and the calculated line totals.

---

Handling Edge Cases and Tricky CSVs

Real-world CSV files are rarely as clean as our example. Here are the situations you'll actually encounter.

Quoted fields containing commas

A value like "Smith, John" is a legitimate CSV field — the quotes tell parsers to treat the comma inside as literal content. PAD's basic Split text action doesn't understand this and will incorrectly split "Smith, John" into two fields.

For files with quoted fields, you have two options: use a Run PowerShell script action to leverage .NET's built-in CSV parser, or preprocess the file to remove problematic commas. The Scripting Inside Desktop Flows: Running PowerShell, Python, and VBScript Actions article shows you how to execute scripts directly from PAD when you need more parsing power.

Empty lines at the end of the file

Many systems append a trailing newline after the last record. When you split by new line, you'll get an empty string as the last item in AllRows. Before processing each row inside your loop, add a condition:

• If %AllRows[RowIndex]% is not equal to `` (empty string), then proceed

This prevents errors when you try to split an empty line.

Files with no header row

Some legacy systems export headerless files. In that case, start your loop at index 0 instead of 1, and simply don't extract a Headers list. Your column indices still work the same way — you just need to know the column order from the system documentation or by inspecting a sample file.

Pipe and tab delimiters

If your file uses | as a delimiter, every Split text action that was set to , should be changed to |. For tab-delimited files, in the Custom delimiter field you'll need to enter an actual tab character. The easiest way to get a tab character into PAD is to use %TabChar% — another built-in PAD special variable — as your delimiter value.

---

Hands-On Exercise

Create a fresh flow called Filter Inventory Report and work through these steps:

1. Create a text file called inventory.csv in C:\Practice\ with this content:

SKU,ProductName,Category,StockQty,ReorderLevel,Active
SKU001,Wireless Mouse,Peripherals,45,10,Yes
SKU002,17in Monitor,Displays,3,5,Yes
SKU003,Fax Machine,Legacy,0,0,No
SKU004,USB Keyboard,Peripherals,12,8,Yes
SKU005,VGA Cable,Legacy,88,0,No

2. Build a flow that reads this file, loops through all rows (skipping the header), and filters for rows where Active equals Yes AND StockQty is less than or equal to ReorderLevel (items that are active and need reordering).

3. For matching rows, build an output CSV called reorder_alert.csv in C:\Practice\ with columns: SKU, ProductName, StockQty, ReorderLevel.

4. Run the flow. Your output file should contain only SKU002 (Monitor — stock 3, reorder level 5).

Bonus challenge: Add a third column to your output called ShortfallQty that contains ReorderLevel - StockQty for each flagged item.

---

Common Mistakes & Troubleshooting

"Index out of range" errors This almost always means you're trying to access CurrentRow[5] but the row only has 5 columns (indices 0–4). Either the row has fewer columns than expected, or an empty row snuck in. Add the empty-row check described above and verify your expected column count against the actual file.

Numbers not calculating correctly PAD stores everything from a text file as a string. If %CurrentRow[3] * CurrentRow[4]% gives you an error, use Convert text to number on both values before multiplying, then store them in numeric variables.

The output file is missing its last record Check whether you're off by one in your loop's end condition. %AllRows.Count - 1% is correct because Count gives you the total number of items, and the last valid index is always Count minus one (zero-based indexing).

The output file looks fine in Notepad but Excel shows everything in one column Excel needs to know your delimiter. Either save the file with a .csv extension and double-click (Excel reads commas by default in most locales), or use Excel's Data → Text to Columns feature. For reliable Excel compatibility, consider writing data directly to an Excel file using the Excel actions covered in Automating Excel with Power Automate Desktop: Reading, Writing, and Running Macros.

Flow errors on files with special characters (accents, symbols) This is an encoding mismatch. The file is probably Latin-1 or Windows-1252 but you're reading it as UTF-8. Change the encoding setting in the Read text from file action to match your file's actual encoding. If you're unsure, open the file in Notepad, choose "Save As," and check what encoding Notepad reports in the dropdown.

---

Summary & Next Steps

You've now built a complete CSV processing pipeline in Power Automate Desktop. Here's the pattern you'll use again and again:

1. Read the file into a single text variable with Read text from file
2. Split by new line to get a list of rows
3. Extract the header (index 0) if you need column names, then start your data loop at index 1
4. Split each row by its delimiter to get a list of field values
5. Act on each record using conditions, calculations, and downstream actions
6. Accumulate output as a string using %NewLine% to separate rows
7. Write the result with Write text to file

This foundation handles the majority of flat-file automation scenarios you'll encounter in the real world. From here, several natural next steps open up:

• Combine CSV processing with application automation: read orders from a CSV, then enter them into a legacy desktop app or web portal. The article on Automating Multi-Application Workflows in Power Automate Desktop: Transferring Data Between Windows Apps, Web Browsers, and Excel in a Single Desktop Flow walks through exactly that kind of end-to-end scenario.
• Escalate to Data Tables: for more complex column-based operations, convert your parsed rows into a PAD Data Table, which gives you named-column access and built-in filtering. That approach is covered in the variables and data tables guide linked in the prerequisites.
• Run your flow on a schedule: once your flow reliably processes the daily CSV, configure it as an unattended automation that runs at 6:05 AM right after the source file lands. Attended vs Unattended RPA: Choosing a Run Mode and Configuring Machines in Power Automate explains how to make that transition.

Flat files aren't glamorous, but mastering them makes you immediately useful in almost any enterprise environment. The technique you've learned here — read, split, loop, write — is a transferable pattern that shows up in PDF parsing, log file analysis, and anywhere else structured text needs to be turned into action.