cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

JSON to Excel or CSV to Excel

So I'm working on a flow to convert my JSON to an excel file. I was able to do a CSV file but now I need it to be an excel file. Is there a way to convert the CSV to Excel or do I need to do JSON to Excel?

 

For converting the JSON to Excel, I'm assuming I'll need to Parse the JSON and then somehow pull each property? How do I go about doing that? Every Data Operation I try only lists "Body" and "Item", if i do "Parse JSON" under my existing "Parse JSON" I do see the actual properties though. Not sure where to go from here.

 

{
    "type": "array",
    "properties": {
        "PrimarySmtpAddress": {
            "type": "string"
        },
        "ResourceProvisioningOptions": {
            "type": "array",
            "items": {
                "type": "string"
            }
        },
        "HiddenFromAddressListsEnabled": {
            "type": "boolean"
        },
        "HiddenFromExchangeClientsEnabled": {
            "type": "boolean"
        },
        "ManagedByDetails": {
            "type": "array",
            "items": {
                "type": "string"
            }
        }
    }
}

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: JSON to Excel or CSV to Excel

Hi @Jakuza90,

 

Yes, that's what I want, so there are steps how to convert the JSON into an excel table, you should create an empty table before you create the Flow:

 

I put these data into an array variable:

Annotation 2020-01-16 170546.png
Loop through each member of the array by using apply to each action, and the scheme of the JSON is:

{
    "type": "object",
    "properties": {
        "PrimarySmtpAddress": {
            "type": "string"
        },
        "ManagedByDetails": {
            "type": "array",
            "items": {
                "type": "string"
            }
        },
        "ResourceProvisioningOptions": {
            "type": "array"
        },
        "HiddenFromAddressListsEnabled": {
            "type": "boolean"
        },
        "HiddenFromExchangeClientsEnabled": {
            "type": "boolean"
        },
        "Notes": {
            "type": "string"
        }
    }
}

Annotation 2020-01-16 170242.png

 

After that, add a row into an excel table, since the ManagedByDetails is an array, so you should convert it into the string by using the join() function:

Annotation 2020-01-16 170157.png

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

7 REPLIES 7
Highlighted
Super User II
Super User II

Re: JSON to Excel or CSV to Excel

Hi @Jakuza90 , Generally, CSV files open in Excel with no issue. Is your requirement is such that file extension needed as Excel or any dependency on other applications that need this Excel file?

 

-------------------------------------------------------------------------
If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.

View solution in original post

Highlighted
Super User II
Super User II

Re: JSON to Excel or CSV to Excel

You can use Parse CSV action from Plumsail Documents connector. It allows you to convert CSV into an array and variables for each column. Please read this article demonstrating how it works. The Parserr (Preview) connector may be worth looking at as well. 

If you could provide an expanded screenshot of your Flow and steps, and of any detailed error messages you're receiving we could likely better assist you. Also, for the best results, you may want to review How to write a good forum post.

If this reply has answered your question or solved your issue, please mark this question as answered. Answered questions helps users in the future who may have the same issue or question quickly find a resolution via search. If you liked my response, please consider giving it a thumbs up. THANKS!

Highlighted
Helper I
Helper I

Re: JSON to Excel or CSV to Excel

Here's a bit more context, I am trying to Create CSV Table > Create File in SharePoint.

 

The issue is that when I Create File and give it the file name "Test.xlsx" or "Test.xls", It won't open in SharePoint. I'll get an error: There is something in your workbook that we don't understand. Try opening and saving the workbook in a newer version of Excel or "We're sorry, we couldn't open your workbook. It's possibly corrupt or using a file format that's not supported"

Community Support
Community Support

Re: JSON to Excel or CSV to Excel

Hi @Jakuza90,

 

The Code you provided is the JSON schema, and the original data should have an array, so you can use the Apply to each action to add these data into an Excel table by using Add a row into a table action. If you could provide an instance of the JSON, I can make the implementation details more specific, thanks.

Annotation 2020-01-14 145525.png

 

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper I
Helper I

Re: JSON to Excel or CSV to Excel

Do you mean the output of the JSON?

  {
    "PrimarySmtpAddress": "MyTeamm@email.com",
    "ManagedByDetails": [
      "Bauer, Jack"
    ],
    "ResourceProvisioningOptions": [],
    "HiddenFromAddressListsEnabled": true,
    "HiddenFromExchangeClientsEnabled": false,
    "Notes": "Jack Bauer's Group"
  },
Highlighted
Community Support
Community Support

Re: JSON to Excel or CSV to Excel

Hi @Jakuza90,

 

Yes, that's what I want, so there are steps how to convert the JSON into an excel table, you should create an empty table before you create the Flow:

 

I put these data into an array variable:

Annotation 2020-01-16 170546.png
Loop through each member of the array by using apply to each action, and the scheme of the JSON is:

{
    "type": "object",
    "properties": {
        "PrimarySmtpAddress": {
            "type": "string"
        },
        "ManagedByDetails": {
            "type": "array",
            "items": {
                "type": "string"
            }
        },
        "ResourceProvisioningOptions": {
            "type": "array"
        },
        "HiddenFromAddressListsEnabled": {
            "type": "boolean"
        },
        "HiddenFromExchangeClientsEnabled": {
            "type": "boolean"
        },
        "Notes": {
            "type": "string"
        }
    }
}

Annotation 2020-01-16 170242.png

 

After that, add a row into an excel table, since the ManagedByDetails is an array, so you should convert it into the string by using the join() function:

Annotation 2020-01-16 170157.png

Best Regards,
Community Support Team _ Lin Tu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Highlighted
Regular Visitor

Re: JSON to Excel or CSV to Excel

Hi @v-litu-msft ,

I really appreciate your accurate response. I think it will help me a lot, thanks : )

 

I would like to apply the workflow that you have showed us, to my case (convert a json from 123 survey to an excel file), and I want to learn too.

I am not really used to the json format, indeed I do not understand what will give the json scheme you mentioned to format the empty array in the first step.

 

When I speak about the json scheme I mean, the following :

 

 

 

 

{
    "type": "object",
    "properties": {
        "PrimarySmtpAddress": {
            "type": "string"
        },
        "ManagedByDetails": {
            "type": "array",
            "items": {
                "type": "string"
            }
        },
        "ResourceProvisioningOptions": {
            "type": "array"
        },
        "HiddenFromAddressListsEnabled": {
            "type": "boolean"
        },
        "HiddenFromExchangeClientsEnabled": {
            "type": "boolean"
        },
        "Notes": {
            "type": "string"
        }
    }
}

 

 

 

 

will it give the following array ?

 

 

PrimarySmtpAddress

 

 

ManagedByDetails

 

 

ResourceProvisioningOptions

 

 

HiddenFromAddressListsEnabled

 

 

HiddenFromExchangeClientsEnabled

 

 

Note

 

 

And if we add the data you have given (Initialize Variable), will it fill the table with the following var ?

 

PrimarySmtpAddressManagedByDetailsResourceProvisioningOptionsHiddenFromAddressListsEnabledHiddenFromExchangeClientsEnabledNote
Email1@email.comBauer, Jack truefalseJack Bauer's Group
Email2@email.comBob, John truefalseBob John's Group


More over I do not understand, how a column like ResourceProvisioningOptions, could contain an array in its cells ?

 

Do you have any website or learning ressource to advise me ?

 

I will really appreciate any help,

Have a good day

 

Adrien

Helpful resources

Announcements
secondImage

August 2020 CYST Winners!

Check out the winners of the recent 'Can You Solve These?' community challenge!

thirdImage

Experience what's new for Power Automate

Join us for an in-depth look at the new Power Automate features and capabilities at the free Microsoft Business Applications Launch Event.

firstImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

Top Solution Authors
Top Kudoed Authors
Users online (3,303)