Answered

Getting Data from CSV in One Drive for Business

(0) Share

Report

Posted on by Simmo86

I am trying to build an automated birthday email flow. We have a CSV of all our client data in one drive for business that is overwritten everyday from the main database. In the CSV we have date of birth but I am not sure how I can retrieve this data in Flow?

Any help would be much appreciated.

Many thanks in advance,

Alex

Categories:

Using connectors

All responses (14)

Answers (1)

Sort by

manuelstgomes 6,643 on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Simmo86

You have three steps to get the information:

Trigger the Power Automate on a schedule. I'll leave it up to you when to send the email.

Get the contents of the file.

Create a table based on the contents of the file.

Here's an example.

After that, you can do a For Each and compare if the date is today and send the email. If you don't know how to do it, please let me know, and I'll help you with this part.

Can you please try it and let me know if you have some issues?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel
danielbazetto Microsoft Employee on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @manuelstgomes ,

I was about to suggest the same when I pressed reply I've seen you already replied to the thread.

So @Simmo86 let us know later on if the suggestion from @manuelstgomes helped.
Simmo86 69 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi Manuel,

This looks great. Do you mind letting me know how you do that next bit? i.e. the For Each and Compare for the date of birth field?

Thank you so much for your help.

Alex
manuelstgomes 6,643 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Simmo86

Sure. Give me a few minutes and I'll try to build you a working template.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel
Simmo86 69 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @manuelstgomes

Did you have any luck with that second bit?

Many thanks in advance.

Thanks again for all your help.

Best wishes,

Alex
manuelstgomes 6,643 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @Simmo86

I sure did. I started writing your answer and building the template and then I thought that I could write a detailed explanation that the community could benefit from.

You can find the freshly published articled here:

https://manueltgomes.com/microsoft/powerautomate/how-to-parse-csv-file/

And the template can be downloaded here:

https://manueltgomes.com/wp-content/uploads/2020/08/ParseCSVfromOneDrive_20200803163228.zip

You can import it and call if from any of your Power Automates. You just need to pass it the path and if the first line contains the header columns. Please make sure that you're using a solution so that you can call a "Run Child Flow". If you're not using a solution please let me know and I'll explain how to call it from anywhere.

Then you can get a nicely formatted JSON that you can call each element like this:

The article explains everything but if you need anything please let me know.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel
Simmo86 69 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @manuelstgomes

Thank you so much for that, that is really useful! I have managed to get it to run on a test csv containing two records with Headers, Name, Email, DOB and it returns the following:
I am assuming that is the desired response?
Please don't hate me but I still had a couple of questions.

1. As the purpose of this was to run every day to check today's date against the date of birth field and email the individual's happy birthday, how do I create the trigger to be a recurrence? Rather than a manual input of the path each time?

2. How do I use the output shown above to match against Today's Date to provide me with the automated email function?

Sorry to be a pain!
Verified answer

manuelstgomes 6,643 on at

Like
a
(1)

Report
Copy link

Link copied!

Hey @Simmo86

Ok, let's do this. Quite easy.

Use the template that I provided you and remove the trigger and add a daily one.

You need, in the next step, to hard-code the path and then, in the end, fetch the date and send the email.

I've attached the template and the example CSV that I used.

Does this help?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel

ParseCSVFileRecur...
example.csv.zip
Simmo86 69 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @manuelstgomes

That is amazing! I think it is all working, I can see it retrieving the dates etc. but when it matches it fails on the email as it says a Bad Request. Feels like something really simple all things considered!!!

When I download the output I get:

Seems like it isnt pulling through the email to send the email to?

Thank you so much for your help on this, absolutely amazing!
manuelstgomes 6,643 on at

Like
a
(1)

Report
Copy link

Link copied!

Hey @Simmo86

You're very welcome... Almost there!!! 😀

The error indicates that the "To" field is empty, so please double check if the CSV has all email columns filled in. I suspect that you have 1 column without email and since you pass the value from the CSV and it's empty, it fails.

The error message doesn't help, but the log that you provided was great, thanks for adding it.

Can you please check and get back to me?

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

Cheers
Manuel