Nested Array after reading a XLS worksheet with MS Graph

[Maylo]

Hi,

After successfully getting the content of a XLS worksheet using MS Graph, I get as output a nested array, where each row of the XLS worksheet is an (sub)array . The first (sub)array corresponds to the  XLS worksheet column's headers, and the rest of (sub)arrays to the rows of the worksheet. Below is an example of how it looks the output using MS Graph.

Original XLS worksheet:

MS Graph output:

[
[
"TYPE",
"CALENDARYEAR",
"CALENDARMONTH",
"PRODUCT"
],
[
"Sales",
2020,
02,
"Pizzas"
],
[
"Sales",
2020,
02,
"Ice_Cream"
]

]

I appreciate your guidance:

1- how to transform the output( nested arrays) into a CSV table with MS Flow?

2- if not feasible in Flow , how can the array be passed within a "http response"  connector using JSON?

[v-litu-msft]

Hi @Maylo,

Instead of using MS Graph, you could use the List rows of rows to create a get the content, then create CSV table:

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.

[Maylo]

Hi @v-litu-msft ,

Many thanks fro your prompt response, however I missed to indicate the reasons why the XLS file is required to be read using MS Graph:

1- The "XLS" file was created in a previous step (using Sharepoint/OneDrive with a "Create file" connector), in any of both cases after the file is created its access is blocked, so the Excel connectors cannot access it in further steps . MS Graph can read file without limitation

2- Performance wise, MS Graph can read faster and bigger files

3- MS Graph reads the XLS worksheet content even if the User did not explicitly defined a "table" in the file ( which most probably will always forget to do)

4- MS Graph can use dynamic parameters for: Folder Path , File Name and Worksheet Name

Still, I did tried your proposal (using an XLS with a defined table) and I got an Error:

"status" : 400 ( Failed) - The parameter 'file' has an invalid value '/test/test.xlsx'.

The above error, Im guess it is caused by:

a) the access to the recently created file is blocked and not accessible by XLS connectors

b) Excel connector do not accept dynamic parameters for:  Folder Path , File Name and Table name

Therefore the issue remains as originally posted : 

How to transform a nested array into a table ? 

Many thanks in advanced for your support and best regards, 

[v-litu-msft]

Hi @Maylo,

OK, thanks for your explanation, so process the output of MS Graph is a more possible approach.

Could you please share the screenshot of the MS Graph?

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.

[Maylo · Accepted solution]

Hi,

I defined Custom Connector, with an action for reading the XLS worksheet  (where the file and the worksheet are input parameter )..see below:

https://graph.microsoft.com/v1.0/me/drive/items/{File}/workbook/worksheets/{WorksheetName}/UsedRange...

At the end, I transformed the nested arrays to a string and performed cleaning operations for formatting it.

regards