cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BlessedCobba
Resolver I
Resolver I

Convert JSON Output to CSV

Hi All,

 

I have been searching this for a few hours and cant quite get it to work, this is my first crack at power automate

 

i am getting some data via API using a HTTP get,

i am then parsing the JSON data using this schema

 

 

{
    "type": "object",
    "properties": {
        "selection10": {
            "type": "array",
            "items": {
                "type": "object",
                "properties": {
                    "Title": {
                        "type": "string"
                    },
                    "url": {
                        "type": "string"
                    },
                    "Tender_ID": {
                        "type": "string"
                    },
                    "Published_Date": {
                        "type": "string"
                    },
                    "Closing_Date": {
                        "type": "string"
                    },
                    "Agency": {
                        "type": "string"
                    },
                    "UNSPSC": {
                        "type": "string"
                    },
                    "Description": {
                        "type": "string"
                    }
                },
                "required": [
                    "Title",
                    "url",
                    "Tender_ID",
                    "Agency",
                    "Published_Date",
                    "Closing_Date",
                    "UNSPSC",
                    "Description"
                ]
            }
        }
    }
}

 

 


Once i get all the data in, i am appending it to an array variable and then using the create CSV table with auto columns

but when i look at the output it has just created all the data in a single row

BlessedCobba_0-1614568609309.png

 

BlessedCobba_1-1614568705732.png


is there something i need to do to the data or the schema to get it to create the table i'm after?

When i save it as a .csv from the source all the column headers look like this

BlessedCobba_0-1614569529208.png

 



2 ACCEPTED SOLUTIONS

Accepted Solutions
Mira_Ghaly
Dual Super User II
Dual Super User II

@BlessedCobba 

I made a test on my side you do not need the append array variable step, so you need to pass the below expression to the Create CSV table.. So basically you are passing the selection10 part from your json output

Mira_Ghaly_0-1614641111732.png

outputs('Compose')['selection10']

But in your case you need to change to:

body('Parse_JSON')['selection10']

Hope this works for you!

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

BlessedCobba
Resolver I
Resolver I

Update:

I have solved it, in the dynamic content i was continuously selecting the json body as the output to convert to csv, what i should have been selecting is the selection10 content, which just gave me the array i was after to create the csv
there is 4 hours of my life i wont get back

View solution in original post

6 REPLIES 6
Mira_Ghaly
Dual Super User II
Dual Super User II

@BlessedCobba 

Can you please paste the output from the array variable step?

 

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here
Paulie78
Super User III
Super User III

You don't need the append to array variable step. You can just put the Value of the Parse JSON step into the Create CSV action (you could even probably put the HTTP body directly into the create CSV as you are receiving it in JSON format anyway).

BlessedCobba
Resolver I
Resolver I

Here is the output

 

 

{
  "selection10": [
    {
      "Title": "Design, Supply and Install PLC Renewal at St Arnaud Water Treatment Plant",
      "url": "https://www.tenders.vic.gov.au/tender/view?id=230792",
      "Tender_ID": "C01026",
      "Agency": "Issued by Grampians Wimmera Mallee Water Corporation",
      "Published_Date": "Wed, 24 February 2021 6:00 AM",
      "Closing_Date": "Thu, 25 March 2021 2:00 PM",
      "UNSPSC": "Engineering and Research and Technology Based Services - (25%)\nSystems integration design - (25%)\nComputer programmers - (25%)\nWater treatment services - (25%)",
      "Description": "Tenders are invited for the design, supply and installation of PLC hardware and software integration and associated works for the St Arnaud Water Treatment Plant.\nThe works for this contract include:\n> Supply and installation of GE PACSystems Rx3i PLC\n> GE Proficy Software integration for water treatment process.\n> Associated electrical upgrades and instrumentation for water treatment systems.\nPlease note the successful contractor will be required to produce a COVIDSafe Plan prior to commencing works."
    }
  ]
}

 

 


@Paulie78 

if i just put the json body in the create csv operation i get this error "BadRequest. The 'from' property value in the 'table' action inputs is of type 'Object'. The value must be of type 'Array'."

Mira_Ghaly
Dual Super User II
Dual Super User II

@BlessedCobba 

I made a test on my side you do not need the append array variable step, so you need to pass the below expression to the Create CSV table.. So basically you are passing the selection10 part from your json output

Mira_Ghaly_0-1614641111732.png

outputs('Compose')['selection10']

But in your case you need to change to:

body('Parse_JSON')['selection10']

Hope this works for you!

If this post helps you with your problem, please mark your as Accepted solution.If you like my response, please give it a Thumbs Up.

Blog: here

View solution in original post

BlessedCobba
Resolver I
Resolver I

Update:

I have solved it, in the dynamic content i was continuously selecting the json body as the output to convert to csv, what i should have been selecting is the selection10 content, which just gave me the array i was after to create the csv
there is 4 hours of my life i wont get back

View solution in original post

BlessedCobba
Resolver I
Resolver I

thanks @Mira_Ghaly 

i had just got there myself!

Helpful resources

Announcements
MPA_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

V3_PVA CAmpaign Carousel.png

Community Challenge - Giveaways!

Participate in the Power Virtual Agents Community Challenge

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Users online (1,354)