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

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
manuelstgomes
Community Champion
Community Champion

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

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!

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 will be READ ONLY July 16th, 5p PDT -July 22nd

Dear Community Members,   We'd like to let you know of an upcoming change to the community platform: starting July 16th, the platform will transition to a READ ONLY mode until July 22nd.   During this period, members will not be able to Kudo, Comment, or Reply to any posts.   On July 22nd, please be on the lookout for a message sent to the email address registered on your community profile. This email is crucial as it will contain your unique code and link to register for the new platform encompassing all of the communities.   What to Expect in the New Community: A more unified experience where all products, including Power Apps, Power Automate, Copilot Studio, and Power Pages, will be accessible from one community.Community Blogs that you can syndicate and link to for automatic updates. We appreciate your understanding and cooperation during this transition. Stay tuned for the exciting new features and a seamless community experience ahead!

Summer of Solutions | Week 4 Results | Winners will be posted on July 24th

We are excited to announce the Summer of Solutions Challenge!    This challenge is kicking off on Monday, June 17th and will run for (4) weeks.  The challenge is open to all Power Platform (Power Apps, Power Automate, Copilot Studio & Power Pages) community members. We invite you to participate in a quest to provide solutions to as many questions as you can. Answers can be provided in all the communities.    Entry Period: This Challenge will consist of four weekly Entry Periods as follows (each an “Entry Period”)   - 12:00 a.m. PT on June 17, 2024 – 11:59 p.m. PT on June 23, 2024 - 12:00 a.m. PT on June 24, 2024 – 11:59 p.m. PT on June 30, 2024 - 12:00 a.m. PT on July 1, 2024 – 11:59 p.m. PT on July 7, 2024 - 12:00 a.m. PT on July 8, 2024 – 11:59 p.m. PT on July 14, 2024   Entries will be eligible for the Entry Period in which they are received and will not carryover to subsequent weekly entry periods.  You must enter into each weekly Entry Period separately.   How to Enter: We invite you to participate in a quest to provide "Accepted Solutions" to as many questions as you can. Answers can be provided in all the communities. Users must provide a solution which can be an “Accepted Solution” in the Forums in all of the communities and there are no limits to the number of “Accepted Solutions” that a member can provide for entries in this challenge, but each entry must be substantially unique and different.    Winner Selection and Prizes: At the end of each week, we will list the top ten (10) Community users which will consist of: 5 Community Members & 5 Super Users and they will advance to the final drawing. We will post each week in the News & Announcements the top 10 Solution providers.  At the end of the challenge, we will add all of the top 10 weekly names and enter them into a random drawing.  Then we will randomly select ten (10) winners (5 Community Members & 5 Super Users) from among all eligible entrants received across all weekly Entry Periods to receive the prize listed below. If a winner declines, we will draw again at random for the next winner.  A user will only be able to win once overall. If they are drawn multiple times, another user will be drawn at random.  Individuals will be contacted before the announcement with the opportunity to claim or deny the prize.  Once all of the winners have been notified, we will post in the News & Announcements of each community with the list of winners.   Each winner will receive one (1) Pass to the Power Platform Conference in Las Vegas, Sep. 18-20, 2024 ($1800 value). NOTE: Prize is for conference attendance only and any other costs such as airfare, lodging, transportation, and food are the sole responsibility of the winner. Tickets are not transferable to any other party or to next year’s event.   ** PLEASE SEE THE ATTACHED RULES for this CHALLENGE**   Week 1 Results: Congratulations to the Week 1 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge.   Community MembersNumber SolutionsSuper UsersNumber Solutions Deenuji 9 @NathanAlvares24  17 @Anil_g  7 @ManishSolanki  13 @eetuRobo  5 @David_MA  10 @VishnuReddy1997  5 @SpongYe  9JhonatanOB19932 (tie) @Nived_Nambiar  8 @maltie  2 (tie)   @PA-Noob  2 (tie)   @LukeMcG  2 (tie)   @tgut03  2 (tie)       Week 2 Results: Congratulations to the Week 2 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 2: Community MembersSolutionsSuper UsersSolutionsPower Automate  @Deenuji  12@ManishSolanki 19 @Anil_g  10 @NathanAlvares24  17 @VishnuReddy1997  6 @Expiscornovus  10 @Tjan  5 @Nived_Nambiar  10 @eetuRobo  3 @SudeepGhatakNZ 8     Week 3 Results: Congratulations to the Week 3 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 3:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji32ManishSolanki55VishnuReddy199724NathanAlvares2444Anil_g22SudeepGhatakNZ40eetuRobo18Nived_Nambiar28Tjan8David_MA22   Week 4 Results: Congratulations to the Week 4 qualifiers, you are being entered in the random drawing that will take place at the end of the challenge. Week 4:Community MembersSolutionsSuper UsersSolutionsPower Automate Deenuji11FLMike31Sayan11ManishSolanki16VishnuReddy199710creativeopinion14Akshansh-Sharma3SudeepGhatakNZ7claudiovc2CFernandes5 misc2Nived_Nambiar5 Usernametwice232rzaneti5 eetuRobo2   Anil_g2   SharonS2  

Check Out | 2024 Release Wave 2 Plans for Microsoft Dynamics 365 and Microsoft Power Platform

On July 16, 2024, we published the 2024 release wave 2 plans for Microsoft Dynamics 365 and Microsoft Power Platform. These plans are a compilation of the new capabilities planned to be released between October 2024 to March 2025. This release introduces a wealth of new features designed to enhance customer understanding and improve overall user experience, showcasing our dedication to driving digital transformation for our customers and partners.    The upcoming wave is centered around utilizing advanced AI and Microsoft Copilot technologies to enhance user productivity and streamline operations across diverse business applications. These enhancements include intelligent automation, AI-powered insights, and immersive user experiences that are designed to break down barriers between data, insights, and individuals. Watch a summary of the release highlights.    Discover the latest features that empower organizations to operate more efficiently and adaptively. From AI-driven sales insights and customer service enhancements to predictive analytics in supply chain management and autonomous financial processes, the new capabilities enable businesses to proactively address challenges and capitalize on opportunities.    

Updates to Transitions in the Power Platform Communities

We're embarking on a journey to enhance your experience by transitioning to a new community platform. Our team has been diligently working to create a fresh community site, leveraging the very Dynamics 365 and Power Platform tools our community advocates for.  We started this journey with transitioning Copilot Studio forums and blogs in June. The move marks the beginning of a new chapter, and we're eager for you to be a part of it. The rest of the Power Platform product sites will be moving over this summer.   Stay tuned for more updates as we get closer to the launch. We can't wait to welcome you to our new community space, designed with you in mind. Let's connect, learn, and grow together.   Here's to new beginnings and endless possibilities!   If you have any questions, observations or concerns throughout this process please go to https://aka.ms/PPCommSupport.   To stay up to date on the latest details of this migration and other important Community updates subscribe to our News and Announcements forums: Copilot Studio, Power Apps, Power Automate, Power Pages

Users online (2,233)