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
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Users online (2,645)