cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Helper II
Helper II

Getting Data from CSV in One Drive for Business

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

1 ACCEPTED SOLUTION

Accepted Solutions

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

View solution in original post

14 REPLIES 14
Super User II
Super User II

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.

Screenshot 2020-08-03 at 12.27.26.png

 

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

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.

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

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

Hi @manuelstgomes

 

Did you have any luck with that second bit?

 

Many thanks in advance.

 

Thanks again for all your help. 

 

Best wishes, 

 

Alex

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:

small-Screenshot_2020-08-03_at_18_39_34.jpg

 

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

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:

Simmo86_0-1596531766803.png

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! 

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

View solution in original post

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!!!

 

Simmo86_0-1596547185943.png

When I download the output I get:

 

Simmo86_1-1596547344039.png

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

 

Thank you so much for your help on this, absolutely amazing!

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

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:

 

Simmo86_1-1596655684872.png

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

 

Simmo86_0-1596655605876.png

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.....

 

Simmo86_2-1596655975958.png

 

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

 

Simmo86_3-1596656096520.png

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?

 

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. 

 

Simmo86_0-1597436826809.png

 

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

 

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!

New Member

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.

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

MPA Community Blog

Power Automate Community Blog

Check out the community blog page where you can find valuable learning material from community and product team members!

Users online (7,562)