Skip to main content
Power Automate
    • Connectors
    • Templates
    • 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
    • Re: [Guide] Better Import CSV + Convert to JSON (w...

    Re: [Guide] Better Import CSV + Convert to JSON (without Plusmail or any additional apps)

    06-10-2020 08:53 AM

    ewchris_alaska
    Helper I
    4374 Views
    LinkedIn LinkedIn Facebook Facebook Twitter Twitter
    Yuhas
    Yuhas
    Frequent Visitor
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    [Guide] Better Import CSV + Convert to JSON (without Plusmail or any additional apps)

    ‎05-21-2020 03:35 AM

    Disclaimer, I did not fully write this guide. I found this elsewhere on the internet with intense Google searching. However, the guide there was rather poorly written. I was unable to find the original author, I would like to credit them for such exploration into this black hole.

     

    I played with it for about 5 hours and finally figured it out. It was truly a pain but this is going to help my team process a report that we do weekly from ServiceNow. I hope this solves a lot of headache for some of you.

     

    Please note that the zip file is empty. Follow the guide. I apologize about the formatting as well but the forums did not like how my ordered/unordered lists were setup with code-snippits in the HTML.

     

    Order of Actions

    • Manually Trigger Flow
    • SharePoint Get file content
    • Compose (just a general compose), press Enter/Return on your keyboard ONCE. This will count as valid
    • Filter Array
    • Select

     

    1. SharePoint - Get File Content
      1. Pick the Site Address
      2. Pick the File Identifier of your CSV
    2. Add a New Action - "Compose" (Data Operation)
      1. In the INPUT, just hit Enter/Return ONCE, it should just make the box bigger. Nothing else should be here. I was unable to get "\n" or "\r" to work here.
        newline.PNG
      2. Click the three dots on the right of the header for this action, rename it to "Newline" --- without quotes and CASE SENSITIVE.
    3. Add a New Action - "Filter array" (Data Operation)
      1. In the From section, click in the box
      2. The dynamic content/expression pop-up window should appear.
      3. Click on Expression within the pop-up window
      4. Copy and Paste this into the Expression input (where it says fx) - it might look like it only pasted the last line. Don't worry, it got all of it.

     

     

    json(
      uriComponentToString(
        replace(
          replace(
            uriComponent(
              split(
                replace(
                  base64ToString(
                    body('Get_file_content')?['$content']
                  ),
                  ',',';'),
               outputs('Newline')
             )
           ),
           '%5Cr',
           ''),
        '%EF%BB%BF',
        '')
      )
    )

     

     

    • Press OK. If you are unsure if it all pasted, just hover your mouse over the expression inside the "From" field, it should show a little pop-up of the code. Alternatively, you can click the three dots on the upper right hand of the action and "peak code" it should show it all there.
    • Click where it says Choose Value
    • The Dynamic Content window should pop up again.
    • Click on Expression inside the pop up window.
    • Type "item()" in the field where it says fx 

     

     

    item()

     

     

    • Click OK
    • Set the logic to "is not equal to"
    • Keep the third value completely blank! This will filter out any lines that are blank. If you have blank lines, it will break the flow in the Select action which is next.
    • Add A New Action - "Select" (Data Operation)
      1. Click into the From field
      2. The Dynamics and Expression pop-up window should appear.
      3. Click Expression
      4. Copy and Paste this in the fx field. This is so that it skips your header rows.

     

     

    skip(body('Filter_array'),1)​

     

     

    • Click OK
    • In the Mapping section, this is where you will set your JSON values. In my case, I uploaded a simple CSV file that has two columns: Firstname and Lastname. So if I wanted the first column:

     

     

    split(item(),';')[0]​

     

     

    If I wanted to change columns, I would change the 0 to 1... or 2... or 3... etc.

    • Each column is represented via the code below:

     

     

    split(item(),';')[0]  #COMMENT: This would be column 1 (remember we start from 0 in programming)
    split(item(),';')[1]  #COMMENT: This would be column 2​

     

     

    • As for your "Key," you can define that however you like. I like to stick to the column names for clarity.
      capture3.PNG

    • The End Result should look something similar to this when testing:

      end_result.PNG
    • The Full Picture:
      Capture1.PNG
      capture2.PNG
    Preview file
    9 KB
    no_files.zip
    Labels:
    • Labels:
    • Solutions
    Message 1 of 21
    6,609 Views
    5 Kudos
    Reply
    • All forum topics
    • Previous Topic
    • Next Topic
    • « Previous
      • 1
      • 2
      • 3
    • Next »
    DimaDima
    Microsoft DimaDima
    Microsoft
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎05-22-2020 02:16 AM

    Hi!

     

    Thank you for the guide, it really helped. How can we enter values back to the same csv file?

     

    Regards,

    Dima Kabakov

    Message 2 of 21
    4,478 Views
    0 Kudos
    Reply
    prodaptiv-c
    prodaptiv-c
    New Member
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-09-2020 02:33 AM

    Hi, this is really handy, thank you. I have just one issue to get my solution working and hoping you can help.

     

    I need to somehow replace the ; between the quotes otherwise the columns shift when splitting using 

    split(item(),';')[0] 

    "abc;xyz";gfgf;21;"abc;xyz";gfgf

     

    I can't change delimiter on export. 

    Does anyone have any thoughts?

    Message 3 of 21
    4,399 Views
    0 Kudos
    Reply
    Yuhas
    Yuhas
    Frequent Visitor
    In response to prodaptiv-c
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-09-2020 08:18 AM

    I would give just the comma ',' a try. @prodaptiv-c 

     

    @DimaDima - I am unsure of how to do that at the moment. To insert values into any spreadsheet it has to be XLSX, with a Table inserted. 

    Message 4 of 21
    4,392 Views
    0 Kudos
    Reply
    ewchris_alaska
    ewchris_alaska Helper I
    Helper I
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-10-2020 08:53 AM

    Hello @Yuhas ,

    I really appreciate the assistance with this.  When I test this i am getting an "Invalid Template" error,

    "Unable to process template language expressions in action 'Filter_array' inputs at line '1' and column '2880': 'The template language function 'uriComponent' was invoked with invalid parameters. The parameter at index '0' cannot be converted to URI component.'."

     

    The only changes I made to your steps was to change the ; to a , in the filter array and the "split" function, because that is what my data uses as a separator.

     

    Here is what my filter array says,

    json(

      uriComponentToString(

        replace(

          replace(

            uriComponent(

              split(

                replace(

                  base64ToString(

                    body('Get_file_content')?['$content']

                  ),

                  ',',','),

               outputs('Newline')

             )

           ),

           '%5Cr',

           ''),

        '%EF%BB%BF',

        '')

      )

    )

     

    Any ideas as to what I may be doing wrong?

    Thanks,

    Eric

    Message 5 of 21
    4,374 Views
    0 Kudos
    Reply
    Yuhas
    Yuhas
    Frequent Visitor
    In response to ewchris_alaska
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-12-2020 08:23 AM

    @ewchris_alaska 

     

    I would have to see which step is failing but give me a moment to let my coffee settle in, I will read this over.

    Message 6 of 21
    4,361 Views
    0 Kudos
    Reply
    Yuhas
    Yuhas
    Frequent Visitor
    In response to ewchris_alaska
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-12-2020 08:28 AM

    @ewchris_alaska, so why again are you changing the ";" to "," ? 

     

    You shouldn't need to change anything if your file is comma delimited already.

    Message 7 of 21
    4,358 Views
    0 Kudos
    Reply
    ewchris_alaska
    ewchris_alaska Helper I
    Helper I
    In response to Yuhas
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-12-2020 08:33 AM

    @Yuhas 

     I changed it because I thought that  your data was separated by a ; based on the inputs shown 

     

    [

      "Joe; Snuffy", (etc)

    ]

     

    I will change it back and try again.  I appreciate your help.

    Message 8 of 21
    4,352 Views
    0 Kudos
    Reply
    ewchris_alaska
    ewchris_alaska Helper I
    Helper I
    In response to ewchris_alaska
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-12-2020 08:43 AM

    @Yuhas 

     

    I am still getting the same error in the filter array step.

    Message 9 of 21
    4,349 Views
    0 Kudos
    Reply
    Yuhas
    Yuhas
    Frequent Visitor
    In response to ewchris_alaska
    • Mark as New
    • Bookmark
    • Subscribe
    • Mute
    • Subscribe to RSS Feed
    • Permalink
    • Print
    • Report Inappropriate Content

    ‎06-12-2020 08:44 AM

    @ewchris_alaska 

     

    Yeah I could see that being slightly confusing. I think what the original author was doing is using a ";" during the conversion to JSON.

     

    He/she probably had issues with going straight from "," -> JSON. So they used the "replace()" method to get around it. I'd be interested to see if it works without. Maybe I'll try it later.

    Message 10 of 21
    4,350 Views
    0 Kudos
    Reply
    • « Previous
      • 1
      • 2
      • 3
    • 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