cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BlessedCobba
Responsive Resident
Responsive Resident

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
Dual Super User

@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
Responsive Resident
Responsive Resident

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

7 REPLIES 7
Mira_Ghaly
Dual Super User
Dual Super User

@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
Super User

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
Responsive Resident
Responsive Resident

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
Dual Super User

@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
BlessedCobba
Responsive Resident
Responsive Resident

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

BlessedCobba
Responsive Resident
Responsive Resident

thanks @Mira_Ghaly 

i had just got there myself!

Anonymous
Not applicable

How can we select multiple number of columns

Helpful resources

Announcements
Power Automate News & Announcements

Power Automate News & Announcements

Keep up to date with current events and community announcements in the Power Automate community.

Power Automate Community Blog

Power Automate Community Blog

Check out the latest Community Blog from the community!

Users online (5,567)