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.

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,258)