cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

convert CSV string to table

Hi,

I have an API wich return a text, suppossed to be a csv file, in the form below :

datetime;mois;semaine;joursem;heure;vacance;Text;freQ;Scored Labels
7/15/2017 6:00:00 PM;7;28;7;18;1;29,234;67;148,2313385
10/14/2016 4:00:00 AM;10;42;6;4;0;18,922;0;-9,692166328
02/04/2017 12:00;2;5;7;12;0;9,239;0;39,99219513
05/11/2017 05:00;5;19;5;5;0;17,421;0;1,262338638
10/01/2016 13:00;10;40;7;13;0;22,333;2;-0,870968521
11/20/2016 6:00:00 AM;11;48;1;6;0;11,83;0;-13,13813114
10/18/2016 4:00:00 PM;10;43;3;16;0;20,529;42;46,49481583
2/23/2018 9:00:00 AM;2;8;6;9;0;1,231;0;1,8540411
01/05/2017 05:00;1;1;5;5;1;6,426;0;0,300328046

I don't need to save a csv file, I need to transform this to a table to save 2 specifc columns on a sharepoint list.

I tried create CSV table but, I can't make it work.

 

Thank you for your help.

 

27 REPLIES 27
DamoBird365
Employee
Employee

@pedwin / @tutankh / @Paulie78 

 

I just wanted to throw my idea into the mix - I know we are waiting on a solution via ideas but why not take a look at this option

https://damobird365.birdhoose.co.uk/2021/04/03/parse-a-csv-to-json-array-in-power-automate-easily-qu...

 

I've developed a copy /paste solution which allows you can quickly re-use for different sized CSV's and quickly update the payload for the parse JSON action.  

 

Cheers,
Damien


P.S. take a look at my new blog here

I like that this is a copy paste solution for simple csv parsing.  Your particular solution doesn't (and I don't think it's meant to, so this isn't a finger-pointy type thing) account for CSVs when you don't know the field names or the number of them that you might have.  It also will break if any of your column values contain your delimiter.  So if you were parsing a CSV that had AD distinguished names in it or something, it wouldn't work.  

Otherwise - good copy/paste!

Hi @tutankh 

 

Thanks for the feedback. I appreciate what you mean with regards to a delimiter within a field, like a quote encapsulated CSV. I’ve a demo here where I convert an example into pipe delimited to cater for that exact scenario but not a copy paste sadly.

 

The copy paste solution though does cater for multiple fields and using the parse json for the unknown field names as they are dynamically generated from the header line. It also includes the sample payload. I tried to think of a way to make the number of keys/values dynamic too but stuck with 12 based on my example to then allow the next person to remove (or copy/increase) the number of lines as they see necessary. 

Did you try the whole copy paste solution or just the basic select action? 

I’m keen to explore new ideas as even if this isn’t the best solution, I learn by exploring the possibilities.

 

Cheers,
Damien


P.S. take a look at my new blog here

I get an "Access Denied" when I try and view the link pointed to by the solution.  Why?

Regards,

-S

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

takolota
Multi Super User
Multi Super User

EDIT: This only works if the records with commas have double quotes around them or if you can save the CSV in a text file format where it will apply the double quotes to those records with commas.

Hello All,

 

I ran into a similar issue when pulling CSV data in from Power Automate Desktop. It comes in as a big string and some columns have commas in the actual data. I also want to process potentially thousands of rows at a time so I need it to be more efficient than these Apply to each loop options.

 

So I custom built something in Power Automate that can change the delimiter of just the column separator commas. No loops, only a few actions regardless of the number of rows. I currently only set it to handle up to 5 columns with actual commas in their data, but I can expand that as much as needed.

https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Change-CSV-File-Data-Delimiter/td-p/1442...

 

From there I can pretty easily use the Select action splitting on line breaks for the From field and splitting on my new delimiter ( | ) in the value fields.

 

Then I use that Select action within a SharePoint batch create or SharePoint batch update set-up to load 500 rows at a time with only a few actions.

Create: https://youtu.be/2dV7fI4GUYU

Update: https://youtu.be/l0NuYtXdcrQ

Hi @takolota 

 

Thanks for sharing.  I've played with the batch process too, it's complex to setup but worth it for efficiency.  If I recall right, the batch response has individual responses for each api call, so you might want to filter them for failures, as the overall action will succeed.  @Paulie78 will be delighted to see you have enjoyed his video. 

 

I've also since explored using office scripts to parse a CSV to Excel which uses a regular expression https://youtu.be/9J6ThPWGaG0.

 

Great to see different possibilities and options!

Thanks @DamoBird365,

 

Yes, the batch actions can be more complicated to work with & understand.

I personally do change out the Results compose actions with Append to variable actions and use an expression that returns the results text if it identifies an error. Then at the end of the flow I have a condition checking the length of that variable. If it has some text/length to it, then something failed and it sends me an email with a link to the flow run.

 

However that’s a lot to explain on top of the main parts of the set-up so I don’t tend to cover that in my explanation videos & posts.

 

 

CatT
Frequent Visitor

Paulie78's process to convert csv to JSON worked perfectly for me.  thank you!

Helpful resources

Announcements

Celebrating the May Super User of the Month: Laurens Martens

  @LaurensM  is an exceptional contributor to the Power Platform Community. Super Users like Laurens inspire others through their example, encouragement, and active participation. We are excited to celebrated Laurens as our Super User of the Month for May 2024.   Consistent Engagement:  He consistently engages with the community by answering forum questions, sharing insights, and providing solutions. Laurens dedication helps other users find answers and overcome challenges.   Community Expertise: As a Super User, Laurens plays a crucial role in maintaining a knowledge sharing environment. Always ensuring a positive experience for everyone.   Leadership: He shares valuable insights on community growth, engagement, and future trends. Their contributions help shape the Power Platform Community.   Congratulations, Laurens Martens, for your outstanding work! Keep inspiring others and making a difference in the community!   Keep up the fantastic work!        

Check out the Copilot Studio Cookbook today!

We are excited to announce our new Copilot Cookbook Gallery in the Copilot Studio Community. We can't wait for you to share your expertise and your experience!    Join us for an amazing opportunity where you'll be one of the first to contribute to the Copilot Cookbook—your ultimate guide to mastering Microsoft Copilot. Whether you're seeking inspiration or grappling with a challenge while crafting apps, you probably already know that Copilot Cookbook is your reliable assistant, offering a wealth of tips and tricks at your fingertips--and we want you to add your expertise. What can you "cook" up?   Click this link to get started: https://aka.ms/CS_Copilot_Cookbook_Gallery   Don't miss out on this exclusive opportunity to be one of the first in the Community to share your app creation journey with Copilot. We'll be announcing a Cookbook Challenge very soon and want to make sure you one of the first "cooks" in the kitchen.   Don't miss your moment--start submitting in the Copilot Cookbook Gallery today!     Thank you,  Engagement Team

Announcing Power Apps Copilot Cookbook Gallery

We are excited to share that the all-new Copilot Cookbook Gallery for Power Apps is now available in the Power Apps Community, full of tips and tricks on how to best use Microsoft Copilot as you develop and create in Power Apps. The new Copilot Cookbook is your go-to resource when you need inspiration--or when you're stuck--and aren't sure how to best partner with Copilot while creating apps.   Whether you're looking for the best prompts or just want to know about responsible AI use, visit Copilot Cookbook for regular updates you can rely on--while also serving up some of your greatest tips and tricks for the Community. Check Out the new Copilot Cookbook for Power Apps today: Copilot Cookbook - Power Platform Community.  We can't wait to see what you "cook" up!    

Welcome to the Power Automate Community

You are now a part of a fast-growing vibrant group of peers and industry experts who are here to network, share knowledge, and even have a little fun.   Now that you are a member, you can enjoy the following resources:   Welcome to the Community   News & Announcements: The is your place to get all the latest news around community events and announcements. This is where we share with the community what is going on and how to participate.  Be sure to subscribe to this board and not miss an announcement.   Get Help with Power Automate Forums: If you're looking for support with any part of Power Automate, our forums are the place to go. From General Power Automate forums to Using Connectors, Building Flows and Using Flows.  You will find thousands of technical professionals, and Super Users with years of experience who are ready and eager to answer your questions. You now have the ability to post, reply and give "kudos" on the Power Automate community forums. Make sure you conduct a quick search before creating a new post because your question may have already been asked and answered. Galleries: The galleries are full of content and can assist you with information on creating a flow in our Webinars and Video Gallery, and the ability to share the flows you have created in the Power Automate Cookbook.  Stay connected with the Community Connections & How-To Videos from the Microsoft Community Team. Check out the awesome content being shared there today.   Power Automate Community Blog: Over the years, more than 700 Power Automate Community Blog articles have been written and published by our thriving community. Our community members have learned some excellent tips and have keen insights on the future of process automation. In the Power Automate Community Blog, you can read the latest Power Automate-related posts from our community blog authors around the world. Let us know if you'd like to become an author and contribute your own writing — everything Power Automate-related is welcome.   Community Support: Check out and learn more about Using the Community for tips & tricks. Let us know in the Community Feedback  board if you have any questions or comments about your community experience. Again, we are so excited to welcome you to the Microsoft Power Automate community family. Whether you are brand new to the world of process automation or you are a seasoned Power Automate veteran - our goal is to shape the community to be your 'go to' for support, networking, education, inspiration and encouragement as we enjoy this adventure together.     Power Automate Community Team

Hear what's next for the Power Up Program

Hear from Principal Program Manager, Dimpi Gandhi, to discover the latest enhancements to the Microsoft #PowerUpProgram, including a new accelerated video-based curriculum crafted with the expertise of Microsoft MVPs, Rory Neary and Charlie Phipps-Bennett. If you’d like to hear what’s coming next, click the link below to sign up today! https://aka.ms/PowerUp  

Tuesday Tip | How to Report Spam in Our Community

It's time for another TUESDAY TIPS, your weekly connection with the most insightful tips and tricks that empower both newcomers and veterans in the Power Platform Community! Every Tuesday, we bring you a curated selection of the finest advice, distilled from the resources and tools in the Community. Whether you’re a seasoned member or just getting started, Tuesday Tips are the perfect compass guiding you across the dynamic landscape of the Power Platform Community.   As our community family expands each week, we revisit our essential tools, tips, and tricks to ensure you’re well-versed in the community’s pulse. Keep an eye on the News & Announcements for your weekly Tuesday Tips—you never know what you may learn!   Today's Tip: How to Report Spam in Our Community We strive to maintain a professional and helpful community, and part of that effort involves keeping our platform free of spam. If you encounter a post that you believe is spam, please follow these steps to report it: Locate the Post: Find the post in question within the community.Kebab Menu: Click on the "Kebab" menu | 3 Dots, on the top right of the post.Report Inappropriate Content: Select "Report Inappropriate Content" from the menu.Submit Report: Fill out any necessary details on the form and submit your report.   Our community team will review the report and take appropriate action to ensure our community remains a valuable resource for everyone.   Thank you for helping us keep the community clean and useful!

Users online (4,390)