cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jakuza90
Level: Powered On

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 Team
Community Support Team

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
DeepakS
Level 8

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
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!

Jakuza90
Level: Powered On

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 Team
Community Support Team

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
Level: Powered On

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 Team
Community Support Team

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
firstImage

Incoming: New and improved badges!

Look out for new contribution recognition badges coming SOON!

firstImage

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

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!

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

sixthImage

Power Platform World Tour

Find out where you can attend!

seventhimage

Webinars & Video Gallery

Watch & learn from the Power Automate Community Video Gallery!

Top Solution Authors
Top Kudoed Authors
Users online (5,131)