Skip to main content
Power Automate
    • Connectors
    • Templates
    • Take a guided tour
    • Digital process automation
    • Robotic process automation
    • Business process automation
    • Process advisor
    • AI Builder
  • Pricing
  • Partners
    • Blog
    • Documentation
    • Roadmap
    • Self-paced learning
    • Webinar
    • Business process and workflow automation topics
    • Overview
    • Issues
    • Give feedback
    • Overview
    • Forums
    • Galleries
    • Submit ideas
    • User groups
    • Register
    • ·
    • Sign in
    • ·
    • Help
    Go To
    • Microsoft Power Automate Community
    • Welcome to the Community!
    • News & Announcements
    • Get Help with Power Automate
    • General Power Automate Discussion
    • Using Connectors
    • Building Flows
    • Using Flows
    • Power Automate Desktop
    • Process Advisor
    • AI Builder
    • Power Automate Mobile App
    • Translation Quality Feedback
    • Connector Development
    • Power Platform Integration - Better Together!
    • Power Platform Integrations
    • Power Platform and Dynamics 365 Integrations
    • Galleries
    • Community Connections & How-To Videos
    • Webinars and Video Gallery
    • Power Automate Cookbook
    • Events
    • 2021 MSBizAppsSummit Gallery
    • 2020 MSBizAppsSummit Gallery
    • 2019 MSBizAppsSummit Gallery
    • Community Engagement
    • Community AMA
    • Community Blog
    • Power Automate Community Blog
    • Community Support
    • Community Accounts & Registration
    • Using the Community
    • Community Feedback
    cancel
    Turn on suggestions
    Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
    Showing results for 
    Search instead for 
    Did you mean: 
    • Microsoft Power Automate Community
    • Galleries
    • Power Automate Cookbook
    • CSV to Dataset

    CSV to Dataset

    03-18-2022 21:41 PM - last edited 10-19-2022 15:30 PM

    Super User takolota
    Super User
    17757 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    takolota
    Super User takolota
    Super User
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    CSV to Dataset

    ‎03-18-2022 09:41 PM

    Template for converting large CSV files to JSON, then sending the data to a table or list.

     

    This get data for CSV works even if there are commas in the CSV data. The only requirement is the the CSV file must follow the standard of placing double quotes “ , “ around any item eith in-data commas.

    (If you can export as a semicolon, tab, or other delimited file, then you can probably just use a simpler method like Paul’s here: https://www.tachytelic.net/2021/02/power-automate-parse-csv/?amp)

     

     

    For more information on the delimiter change piece, visit this previous post:

    https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/Change-CSV-Data-Delimiter-From-a-Text-Fi...

     

    To make a flow to send any CSV data to a new Excel table with given CSV header names without any column mappings, check this template:

    https://powerusers.microsoft.com/t5/Power-Automate-Cookbook/CSV-To-New-Excel-Table/m-p/1826096#M964


    *Copying the template into an existing flow may create issues with expressions. You may need to copy the rest of your existing flow into the CSV template flow, then move the template scope where you need it.



    (Older) Version 2 Uploaded 03/26/2022 

    (Adjusted the 1st Select input so it can now also deal with in-data commas in the 1st column. Added more lines to the 1st Select so it can now handle up to 50 columns with commas in them.)

    Google Drive Link To Download: https://drive.google.com/file/d/1UVEewW9J1m9wvSppgFrf7rs3H3txSPUN/view?usp=sharing 

     

     

    Version 3 Uploaded 04/09/2022 

    (More minor fixes & additions.

    I adjusted several expressions so it can now handle a few more scenarios with arrays in the CSV data. It should handle any array that doesn't include double quotes and any array that is all strings with double quotes, so ["String1", "String2", "String3"], but it will have issues if it is a mixed array with some double-quoted strings and some other values, for example ["String", 4, 03/05/2022, "String2"] won't work.

    I also adjusted how the LineBreak setting is set-up so it now uses the /r/n for the LineBreak. I also provided this link in the flow so anyone can look up the right string for the decodeUriComponent expression(s) if they happen to have different LineBreak characters. This change also made it possible to differentiate between in-data line-breaks and CSV row line-breaks on the files I tested, so it should now replace the in-data line-breaks, like the multiple-choice fields some sites use, with semi-colons. That should make those records much easier to deal with & parse in later actions.
    I also looked over a problem with in-data trailing commas. I added a line in the settings where anyone can toggle whether they want it to adjust for trailing OR leading commas in the data, it just can't handle both in one dataset. So if one column in one row has ",String1 String2" and another column in another row has "String 3 String4," then it will have errors.)

    Google Drive Link To Download: https://drive.google.com/file/d/1X6BGhXWSnZtFgUK0v-5bHo7RuKSNjNtV/view?usp=sharing 

     

     

    Update 06/01/2022
    Microsoft Power Platform & Paul Murana recently did a video demonstration of how to handle CSV & other files in dataflows: https://youtu.be/8IvHxRnwJ7Q
    But it will only output to a Dataverse or Dataverse for teams table.

     

     

     

    watch?v=-P-RDQvNd4A

    CSVtoJSONV3.3.zip
    Labels:
    • Labels:
    • Button flows
    • Scheduled flows
    Message 1 of 63
    17,757 Views
    6 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
      • 3
      • 4
      • …
      • 7
    • Next »
    takolota
    Super User takolota
    Super User
    In response to DukoDragon
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-27-2022 09:29 PM

    @DukoDragon 

    Yes, if the cell is null or empty, then there will not be anything there in the CSV. It will just have a delimiter, followed by another delimiter. In this case comma comma  ( ,, ).

    Are you saying this is causing other issues?
    I did a test run with a comma-containing field, followed by a date field, followed by another comma containing field to replicate a piece of your data, but I didn't see any issues yet.
    Replicate data.pngReplicate data2.pngReplicate data3.pngReplicate data4.png

    But judging from the expression you shared, it looks like that is one of the older versions of the flow. I don't recall anything that would cause what you may be describing in earlier versions, but you can try downloading Version 3 from the comment above or from the main post & try copying your flow into that new V3 template.
    Unfortunately Power Automate does have a bug copying more complex expressions in actions through the clipboard, so I can't just give you individual actions to replace in your current flow. You actually have to copy everything else in your flow over to the new template.

     

    Or maybe you just want your end result to show a null instead of blanks?

    In that case you would need to use if( ) and empty( ) and null expressions to insert null if the field is empty & insert the value of it is not.

    Message 21 of 63
    681 Views
    0 Kudos
    Reply
    Reinand
    Reinand
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-28-2022 11:16 PM

    Hi @takolota 

     

    I am testing out the flow with a dataset that onlt hast the headers + 1line, but it has 34 columns.

    The issue I'm having is that for some reason, most of the time it is duplicating the delimiter:

    Reinand_0-1651212349662.png

    Now, if it was doing this consistantly I can just adjust and skip every other field, however the order changes using the same layout but with different data entered into the fields.

    In one data set I get the "Tercera" result in one field, and in the other it moves down one:

    Reinand_3-1651212910076.png

     

    Reinand_2-1651212764921.png

     

    Any ideas?

     

     

    Message 22 of 63
    659 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to Reinand
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-29-2022 05:34 AM

    @Reinand 

     

    Can you send a picture of your original CSV data please?

    Message 23 of 63
    649 Views
    0 Kudos
    Reply
    Reinand
    Reinand
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-29-2022 05:52 AM

    @takolota 

     

    This is a snippet of the first few columns, let me know if you need more:

    Reinand_0-1651236698535.png

     

    Message 24 of 63
    646 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to Reinand
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎04-29-2022 03:35 PM

    @Reinand 

    I tried recreating the data from the picture exactly and these were my results...
    TestError1.pngTestError2.pngTestError3.pngTestError4.pngTestError5.pngTestError6.pngTestError7.pngTestError8.png

    Other than the special Spanish characters, I'm not getting any errors. And I'm not getting any double delimiters. Could you share a screenshot of the Get file content or Set-up section so I can make sure there isn't anything weird happening with your CSV file or file type. Like is there already a second delimiter for each column in the data passed to the flow?
    Because none of the column headers I saw even have commas in them, so most of the expression logic in the flow isn't even used or applied.

    And it shouldn't matter for the error described, but are you working with the latest version 3 of the template?

    Thanks,

    Message 25 of 63
    632 Views
    0 Kudos
    Reply
    Reinand
    Reinand
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-01-2022 11:11 PM

    @takolota 

     

    I am indeed using the lastest version, here is a screen shot of the Get file content:

    Reinand_0-1651471655436.png

    And here is the set-up:

    Reinand_1-1651471716693.png

    The reason I am using the flow is because there is a lof data being autofilled that is generated by a GPS ping and the addresses in this case are generated with multiple ","

    Message 26 of 63
    608 Views
    0 Kudos
    Reply
    Reinand
    Reinand
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-02-2022 04:25 AM

    @takolota 

    Did a bit more digging and the issue seems to be with specificlly colmn AC.

    If I change anything at all in that column, for example remove or add a letter to the header or data in the second row, the flow runs as expected.

    I will remove and recreate the field that auto generates that Column and hope that fixes it (The CSV is generated by a WorkMobile form).

    Thanks for investing to time to help me look into it and I will let you know once I have the results.

     

    Cheers

     

    P.S.: The issues seems to remain, making changes to the data and saving the CSV seems fix the issue, still needs a bit more looking into.

    Message 27 of 63
    602 Views
    0 Kudos
    Reply
    takolota
    Super User takolota
    Super User
    In response to Reinand
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-02-2022 08:51 AM

    @Reinand 

    Thanks so much for looking into it further. I found a segment of expressions in the "Select Reformat file data, replace in-data commas" starting at the 31st or 32nd column that had not been updated with the correct expression. And thanks for sharing the Get file & Set-up, it looks like it is putting quotes around all your columns, not just the ones with in-data commas. That helps explain why it was using so many of the expressions & splits in the "Select Reformat file data, replace in-data commas" action.

    I've updated the file in any of the post or comment attachments & links. I'll also attach the updated file here.

    CSVtoJSONV3.3.zip
    Message 28 of 63
    592 Views
    0 Kudos
    Reply
    Reinand
    Reinand
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-02-2022 11:39 PM

    @takolota 

    Great, I'm glad we were able to identify the issue!

    But it does seem like all of the links still download the previous version with the different expression in the "Select Reformat file data, replace in-data commas" starting from the 31st column.

     

    Edit:

    The issue on my end was that the ',' symbol to seperate 2 columns was duplicating to the end of one field and the beginning of the next (starting fro the second field).

    Reinand_0-1651570321498.png

     

    I managed to fix this by simply adding a condition that if a field starts with a ',' to remove it, since it already shows up at the end of the previous field.

    Message 29 of 63
    584 Views
    0 Kudos
    Reply
    chico
    chico Advocate V
    Advocate V
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-03-2022 06:42 AM

    Just want to say that, this is the best thread here!!!

    loving the evolution of this solution.

    Message 30 of 63
    573 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • 2
      • 3
      • 4
      • …
      • 7
    • Next »

    Power Platform

    • Overview
    • Power BI
    • Power Apps
    • Power Pages
    • Power Automate
    • Power Virtual Agents

    • Sign up free
    • Sign in

    Browse

    • Templates
    • Connectors
    • Partners

    Downloads

    • Mobile
    • Gateway

    Learn

    • Documentation
    • Learn
    • Support
    • Community
    • Give feedback
    • Blog
    • Pricing

    • © 2023 Microsoft
    • Contact us
    • Trademarks
    • Privacy & cookies
    • Manage cookies
    • Terms of use
    • Terms & conditions
    California Consumer Privacy Act (CCPA) Opt-Out Icon Your California Privacy Choices