web
You’re offline. This is a read only version of the page.
close
Skip to main content

Announcements

News and Announcements icon
Community site session details

Community site session details

Session Id :
Power Platform Community / Forums / Power Automate / Debug help - CSV to JS...
Power Automate
Unanswered

Debug help - CSV to JSON, send emails to 'Email' column

(0) ShareShare
ReportReport
Posted on by 50

Hi everyone,

 

I am pretty new to PowerAutomate and have a use-case where I would need to build to a flow that does the following:

 

- reads a CSV file from my emails

- saves the CSV file to sharepoint

- converts the CSV into JSON (directly from the attachment, not from the file saved on sharepoint)

- loops through the 'email' column

- checks the @domain.com value and skips certain domains

- merges the remaining email addresses with an email template and sends out an email to each email address in the array

 

Since this is something beyond my capabilities, I outsourced this to a freelancer who all-in-all did a decent job and managed to get the flow "somewhat" working. Somewhat in the sense that it works fine in a staging environment, but fails in a live production environment. Here the symptoms we spent over a week debugging:

 

Error:

LiXiaoPai_0-1702609599123.png

Sequence:

0. JsonArray output

LiXiaoPai_4-1702611847676.png

Note: I am not overly familiar with CSV manipulation in PA, but the addition of double quotes (") before every value seems a little odd to me, but since it works fine with the edited CSV I dont see an issue

 

1. Extract email address column from JSON Array

LiXiaoPai_3-1702611520280.png

Note: its a bit funny to me that this is not `outputs('JsonArray'[5])` but I am guessing PA can address items by the header value?

 

2. Split email domain for whitelisting/blacklisting

LiXiaoPai_1-1702610506537.png

 

 

 

Observations:

- PA says there is no 'Email' column, however I can confirm that the JSON array does contain the 'Email' column and all records have a valid email address on file

- the output of the JSON array looks fine, no invalid characters, no extra commas, no funny things in there that would cause a miscalculation of columns or something like this

- I have a "Comments" column that at times has "/" or "\" characters, hence have opted to replace their original value with "None" during the JSON creation process, still the same error remains

- variable sanitisation is in place for all columns (escaping of ", ', /, \, etc.)

 

The flow works fine IF:

- I download the CSV file to my computer, open it in Excel, change every value in the "Comments" column to "None" manually, save the CSV file, and then send the update CSV to myself for processing

 

I am now at the stage where I am thinking that there is a problem with the CSV, although it's kind of unlikely since the JSON output has accurately capture all the columns and all the values. The error makes no sense and we have now spent 10 days on this project with the freelancer ultimately giving up since he couldn't find the root cause.

 

I have attached the flow and a sample CSV file (edited and saved, hence likely to work), I would love to get some feedback from the community on how I could best debug this, what kind of conditional statements I should add to verify data exists and determine where the flow "really" breaks. I develop mostly in PHP / JS / SQL and am missing var_dump(); a lot 🙂

 

Appreciate any guidance from the PA gods in here, thank you!

Categories:

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Season of Sharing Community Challenge Winners!

Congratulations to our community stars!

Kudos to our 2025 Community Spotlight Honorees

Expanding mentorship, skilling, and AI innovation

Leaderboard > Power Automate

#1
11manish Profile Picture

11manish 321 Super User 2026 Season 2

#2
Mohsin Ali Profile Picture

Mohsin Ali 271

#3
Henil Profile Picture

Henil 115

Last 30 days Overall leaderboard