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

[lukascaplikas]

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?

[Serbay]

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,

[lukascaplikas]

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

[Serbay]

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) ;

}

[lukascaplikas · Accepted solution]

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.