Getting Data from CSV in One Drive for Business

[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

[manuelstgomes]

Hi @Simmo86 

You have three steps to get the information:

1. Trigger the Power Automate on a schedule. I'll leave it up to you when to send the email.
2. Get the contents of the file.
3. 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]

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]

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]

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]

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]

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]

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! 

[manuelstgomes · Accepted solution]

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

[Simmo86]

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]

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

[Simmo86]

Hi @manuelstgomes 

Sorry for the delay in coming back on this. 

So all I have done is take your document, change the date and email address:

However, when it runs it fails each time. This is on the first record.....

The next record is the same, the third record has an ! on 'Get the date' which makes sense because the third record doesnt have a date in it, then the 4th record, which has today's date has an ! on the bottom one.....

Then when I open that up, I get the following:

And then when I download the output, thats when I get the file that I showed above. 

I have tried everything. It definitely looks like it is working but just can't seem to get the email for some reason to send the email?

[Simmo86]

Hi @manuelstgomes ,

I think I have figured out what is going wrong. 

The JSON string somehow has "\r" at the end of the email address. I think this is the reason that it fails to send the email. 

I have gone through the flow but I cant work out how to prevent the \r being returned. 

Do you know how this can be removed?

Thanks,

Alex

[Simmo86]

I created an additional column and it worked perfectly! The only issue I have now is that office 365 somehow thinks all my dates are american dates rather than UK so no matter what I do, it returns the month and day the wrong way around!

Anyway, I am sure I will figure it out somehow! Thank you so much for all your help!

[PeterBenderExP]

Thanks so much for sharing your work! Any chance you could remove the Outlook 365 components from a sample of the package you created? I'd love to see if I can import and re-use this concept for other similar purposes, but I don't have an account where Outlook 365 works so I can't load it at all.