cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Maylo
Frequent Visitor

Nested Array after reading a XLS worksheet with MS Graph

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:

image.png

 

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?

1 ACCEPTED SOLUTION

Accepted Solutions

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

 

View solution in original post

4 REPLIES 4
v-litu-msft
Community Support
Community Support

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:

 

Annotation 2020-03-09 110335.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.

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

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.

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

 

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Welcome Super Users.jpg

Super User Season 2

Congratulations, the new Super User Season 2 for 2021 has started!

Carousel 2021 Release Wave 2 Plan 768x460.jpg

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Top Solution Authors
Users online (1,791)