Convert JSON Output to CSV

(0) Share

Report

Posted on by BlessedCobba

459 Super User 2024 Season 1

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

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

Categories:

Building flows

I have the same question (0)

All responses (7)

Answers (2)

Mira Ghaly 11,413 Moderator on at

Like (0)

Report

@BlessedCobba

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

Was this reply helpful? Yes No
Paulie78 8,422 Moderator on at

Like (0)

Report

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).

Was this reply helpful? Yes No
BlessedCobba 459 Super User 2024 Season 1 on at

Like (0)

Report
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'."

Was this reply helpful? Yes No
Verified answer

Mira Ghaly 11,413 Moderator on at

Like (2)

Report
@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

outputs('Compose')['selection10']

But in your case you need to change to:

body('Parse_JSON')['selection10']

Hope this works for you!

Was this reply helpful? Yes No
Verified answer

BlessedCobba 459 Super User 2024 Season 1 on at

Like (0)

Report

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

Was this reply helpful? Yes No
BlessedCobba 459 Super User 2024 Season 1 on at

Like (1)

Report

thanks @Mira_Ghaly

i had just got there myself!

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

How can we select multiple number of columns

Was this reply helpful? Yes No