cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jakuza90 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
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

6 REPLIES 6
Super User
Super User

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

Super User
Super User

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

Jakuza90 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"
  },
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

Helpful resources

Announcements
Did you attend MBAS? Claim your badge

Attending MBAS? Claim your badge

Whether you’ve streamed the digital event live, or you're watching on-demand, claim your attendee badge to sport on your profile.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

firstImage

New Ranks and Rank Icons released on April 21!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power Automate Community!

firstImage

Now Live: Power Virtual Agents Community!

We are excited to announce the launch of Power Virtual Agents Community. Check it out now!

firstImage

New & Improved Power Automate Community Cookbook

We've updated and improved the layout and uploading format of the Power Automate Cookbook!

thirdimage

Power Automate Community User Group Member Badge

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

sixthImage

Community Summit North America

The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Users online (5,197)