JSON to Excel or CSV to Excel

[Anonymous]

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

[DeepakS]

Hi @Anonymous , 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.

[Brad_Groux]

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!

[Anonymous]

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"

[v-litu-msft]

Hi @Anonymous,

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.

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.

[Anonymous]

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

[v-litu-msft · Accepted solution]

Hi @Anonymous,

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:

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

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:

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.

[Anonymous]

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 ?

PrimarySmtpAddress	ManagedByDetails	ResourceProvisioningOptions	HiddenFromAddressListsEnabled	HiddenFromExchangeClientsEnabled	Note
Email1@email.com	Bauer, Jack		true	false	Jack Bauer's Group
Email2@email.com	Bob, John		true	false	Bob 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

[Anonymous]

Dear Sir, I am dealing with similar issue and a problem occurs:

The variable 'Data' of type 'Array' cannot be initialized or updated with value of type 'Object'. The variable 'Data' only supports values of types 'Array'.

Could you please help? Thanks in advance.

[can_do]

Power Automate Desktop:

I can't find proper information how to read JSON (Invoked by API) and write to excel. Here above I see webinterfase solution, can't replicate it on desktop APP.

Best, Alex

[ripsnorter1]

A second apply to each get added when I try adding dynamic content for different attributes in a table. Could you explain why?