cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
Super User II
Super User II

Re: Getting Data from CSV in One Drive for Business

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

13 REPLIES 13
Highlighted
Super User II
Super User II

Re: Getting Data from CSV in One Drive for Business

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

Highlighted
Microsoft
Microsoft

Re: Getting Data from CSV in One Drive for Business

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.

Highlighted
Helper II
Helper II

Re: Getting Data from CSV in One Drive for Business

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

Highlighted
Super User II
Super User II

Re: Getting Data from CSV in One Drive for Business

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

Highlighted
Helper II
Helper II

Re: Getting Data from CSV in One Drive for Business

Hi @manuelstgomes

 

Did you have any luck with that second bit?

 

Many thanks in advance.

 

Thanks again for all your help. 

 

Best wishes, 

 

Alex

Highlighted
Super User II
Super User II

Re: Getting Data from CSV in One Drive for Business

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

Highlighted
Helper II
Helper II

Re: Getting Data from CSV in One Drive for Business

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! 

Highlighted
Super User II
Super User II

Re: Getting Data from CSV in One Drive for Business

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

Highlighted
Helper II
Helper II

Re: Getting Data from CSV in One Drive for Business

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!

Highlighted
Super User II
Super User II

Re: Getting Data from CSV in One Drive for Business

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

Highlighted
Helper II
Helper II

Re: Getting Data from CSV in One Drive for Business

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?

 

Highlighted
Helper II
Helper II

Re: Getting Data from CSV in One Drive for Business

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

 

Highlighted
Helper II
Helper II

Re: Getting Data from CSV in One Drive for Business

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!

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

Upcoming Events

Experience what’s next for Power Automate

See the latest Power Automate innovations, updates, and demos from the Microsoft Business Applications Launch Event.

Users online (6,238)