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

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

New Ideas Forum MPA.jpg

A new place to submit your Ideas for Power Automate

Announcing a new way to share your feedback with the Power Automate Team.

Carousel_PP_768x460_Wave2 (1).png

2022 Release Wave 2 Plan

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

MPA Virtual Workshop Carousel 768x460.png

Register for a Free Workshop

Learn to digitize and optimize business processes and connect all your applications to share data in real time.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (3,217)