Hi
@Michael E. Gernaey, thank you for the response and sorry for not sharing any other details as this was my first post and I am very new with Power Automate. Post this I have been going back and forth with ChatGPT and am able to make the flow but then it is copying all the rows and not just the new ones. I will try and put the flow below to the best of my ability
this flow will run only when a new email arrives and this email will have only 1 CSV attachement and comes only from one person and the CSV and the Excel does not have 0 before the case number.
Full flow — exact actions & expressions
0) Create a new Automated cloud flow with trigger:
Trigger: When a new email arrives (V3)
Folder: Inbox (or your folder)
Include attachments: Yes
(Optional) Subject filter: set if you want only specific emails
After adding this trigger, create the rest of the steps below.
1) Get attachment (V2) — (single attachment)
Add action Get attachment (V2) and set:
Message Id → choose Message Id from the trigger dynamic content
Attachment Id → Expression (fx) — paste:
first(triggerBody()?['attachments'])?['id']
2) Decode CSV (Compose) — name: Decode_CSV
Add action Compose and in Expression (fx) paste exactly:
base64ToString(body('Get_Attachment_(V2)')?['contentBytes'])
(Saves the decoded CSV text.)
3) Split Rows (Compose) — name: Split_Rows
Add action Compose and in Expression paste:
split(replace(replace(outputs('Decode_CSV'), decodeUriComponent('%0D%0A'), '\n'), decodeUriComponent('%0D'), '\n'),'\n')
This normalizes CRLF/CR into \n and splits into an array of lines.
4) Clean Rows (Filter array) — name: Clean_Rows
Add Filter array action:
From → pick Outputs of Split_Rows (from Dynamic content) "outputs('Split_Rows')"
Click Edit in advanced mode and paste:
and(not(equals(trim(item()), '')), not(equals(trim(item()), ',,,,,,')))
This removes blank lines and rows that are only commas.
5) CSV_Data_Rows (Compose) — name: CSV_Data_Rows
Add Compose and in Expression paste:
skip(body('Clean_Rows'), 1)
This skips header row; if your CSV has no header, use outputs('Clean_Rows') instead.
6) List rows present in a table (Excel) — name: Excel_ListRows
Add List rows present in a table (Excel Online (Business)) and configure:
Location: OneDrive for Business
Document Library: OneDrive
File: navigate to your Excel file (you will update this in the UI)
Table: Table1
Important: this runs once, outside the CSV loop. It loads existing Excel rows. (Leave paging defaults.)
7) Select (Data Operations) — name: ExcelCaseNumbers_Select
Add Select action:
From → choose value from Excel_ListRows (that is the list of Excel rows) "outputs('Excel_ListRows')?['body/value']"
Map:
Key (type manually): CaseNumber
Value → click Expression (fx) and paste:
item()?['Case Number']
Result: a simple array of objects like [ {"CaseNumber":"1588208"}, {"CaseNumber":"1897848"} ]
8) Apply to each — name: Apply_to_each_CSV_Row
Add Apply to each. In Select an output use Expression and paste:
outputs('CSV_Data_Rows') "outputs('CSV_Data_Rows')"
Settings for this loop: click ellipsis (...) → Settings → Concurrency: turn Off.
(Important: Excel connector can fail with parallel inserts. Inside this loop add the following actions (order matters):
8.1) Compose_Split (Compose)
Add Compose named Compose_Split. In Expression, paste:
split(string(item()), ',')
This splits the current CSV line into an array of columns. Access with outputs('Compose_Split')[0], [1], etc.
8.2) ExtractCaseNumber (Compose)
Add Compose named ExtractCaseNumber. In Expression, paste:
if(equals(trim(outputs('Compose_Split')[0]), ''), '', string(int(replace(replace(trim(outputs('Compose_Split')[0]), '"', ''), ',', ''))))
This returns the first column (Case Number) cleaned of quotes/spaces. Example output: 01898281 or 1898281 depending on CSV.
If you need to drop leading zeros (because Excel stores numbers without leading zeros), replace with the safe conversion below only if all your case numbers are numeric and non-empty:
if(equals(trim(outputs('Compose_Split')[0]), ''), '', string(int(replace(replace(trim(outputs('Compose_Split')[0]), '"', ''), ',', ''))))
But `int()` will fail on any non-numeric rows — use only when you’re sure the Case Number column is numeric.
8.3) Filter_CheckExisting (Filter array)
Add Filter array named Filter_CheckExisting.
From → select the output of ExcelCaseNumbers_Select (the Select action). "body('ExcelCaseNumbers_Select')"
Click Edit in advanced mode and paste:
@equals(@{trim(string(item()?['CaseNumber']))},@{trim(replace(split(string(items('Apply_to_each_CSV_Row')), ',')[0], '"', ''))})
This compares each Excel CaseNumber to the CSV Case Number.
Run-check: After one test run inspect Filter_CheckExisting -> Outputs -> Body. For an existing case it should be [ {"CaseNumber":"..."} ]. For a non-existing case it should be [].
8.4) FoundCount (Compose)
Add Compose named FoundCount. In Expression paste:
length(body('Filter_CheckExisting'))
This yields 0 if not found, >=1 if found.
8.5) Condition_IsNew (Condition)
Add Condition and set:
Left box: select Outputs of FoundCount from Dynamic content "outputs('FoundCount')"
Middle: is equal to
Right box: 0 (not sure if I need to just type this as 0 or do a expression and add the 0)
If yes branch (meaning not found, new row) → add the row.
If no branch → leave empty.
8.6) Add a row into a table (only inside If yes branch)
Add Add a row into a table (Excel Online (Business)), configure same File/Table as step 6. For each column, click the field → Expression and paste the mapping below:
Case Number - outputs('ExtractCaseNumber')
Case Owner - trim(replace(outputs('Compose_Split')[1], '"', ''))
Institution Name - trim(replace(outputs('Compose_Split')[2], '"', ''))
Subject - trim(replace(outputs('Compose_Split')[3], '"', ''))
Status - trim(replace(outputs('Compose_Split')[4], '"', ''))
Date/Time Opened - trim(replace(outputs('Compose_Split')[5], '"', ''))
Date/Time Closed - trim(replace(outputs('Compose_Split')[6], '"', ''))
9) After the loop — mark email processed (recommended)
Add either Move email (V2)
Move email (V2) → Message Id: Message Id from trigger → Destination folder: “Processed”
