cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
lukascaplikas
Helper I
Helper I

How to get excel file, create table, list table content and export to sharepoint list?

Hello,

 

I have browsed this forum for searching an answer and I always were stuck on part where you have to use list item table since excel does not contain any table.

 

Solution I'm looking for:

 

1. When a file is created in a folder (it will be always an .xls file)

2. Get excel file.

3. Create Table (I don't usually don't know what last row is. It's between 4000 and 6000, so I also need solution how to determine last row of excel OR how to remove empty/blank rows).

4. List Table

5. For each row create a new record in sharepoint list.

 

Also as addition, whenever new file is created I need to delete all records from list, but I know how to do it so this is not main point.

 

Also, maybe it's better to use .csv than .xls?

1 ACCEPTED SOLUTION

Accepted Solutions

So there is no workaround for this? .xsl generated automatically by system I can't just go there and manually create tables. I'm working now with .csv whitch is much more easier to control than .xls. I guess this resolves the main problem. .xls is not data report you want use .csv instead.

View solution in original post

4 REPLIES 4
Serbay
Resolver I
Resolver I

Hi, 
You can use "apply to each" to list rows and create new items
It's important to set pagination while using list rows action, otherwise it will capture only the first 256 rows as I remember.

If your file doesn't contain any table, you can convert it into a table by using an office script.

 

Regards,

An office script? Is it seperate or in power automate?

Serbay
Resolver I
Resolver I

Hi,

Power Automate recognizes only excel tables. So you need to convert your worksheet data into a table format. 

To do this, you can create a script. Then use the action ``Run script`` in your flow.

 

Here is the link to how to create a script ; 
https://docs.microsoft.com/en-us/office/dev/scripts/overview/excel

 

Code for table conversion

 

function main(workbook: ExcelScript.Workbook) {

let Sh = workbook.getWorksheet("EnterSheetNameHere");

let Tbl = workbook.addTable(Sh.getUsedRange(), true) ;

}

So there is no workaround for this? .xsl generated automatically by system I can't just go there and manually create tables. I'm working now with .csv whitch is much more easier to control than .xls. I guess this resolves the main problem. .xls is not data report you want use .csv instead.

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.

May UG Leader Call Carousel 768x460.png

June User Group Leader Call

Join us on June 28 for our monthly User Group leader call!

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.

Power Automate Designer Feedback_carousel.jpg

Help make Flow Design easier

Are you new to designing flows? What is your biggest struggle with Power Automate Designer? Help us make it more user friendly!

Top Solution Authors
Top Kudoed Authors
Users online (2,606)