Upload a non-table-formatted Excel file content to Sharepoint

[Luciano_A]

Dears,

I have a rather simple question: Is it possible via PowerAutomate to automate the upload of a non-table-formatted Excel file content to Sharepoint?

Below an example of how the file would look like:

Report owner:	John Doe
Location:	Milan Paris Tokyo
Start Date	01/01/2021
Employee Name	Employee Address	Employee Job Description
John Doe	123 Street, Boston	Random text
John Doe1	123 Street, Boston	Random Text

[DamoBird365]

Hi @Luciano_A 

You want to explore Excel Scripts

I have a demo of populating an excel file from PowerAutomate using scripts but I understand you want to do this in reverse?  Read from excel and pass back to PowerAutomate.  Definitely possible, you just need to explore the functionality and examples above.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Luciano_A]

Hi @DamoBird365 , thank you for your reply!

I want exactly do the same, from Excel to Sharepoint List via PowerAutomate, trying to keep "manual input" to 0 or almost.

( Pratically I'd wish the end-users to just save the "RAW" excel report to a specified folder, and then let PowerAutomate do the magics! )

I went trough the referenced examples, but none do cover this specific need I have 😞 

[DamoBird365]

Hi @Luciano_A 

How would you envisage your SharePoint List?  Would you have 6 rows for the above example and 2 list items are created?

Will your excel file follow a template?

What would be your trigger?  New file created i.e. uploaded to a specified folder already completed?

Excel scripts can identify the data in you sheet and pass it back to the Cloud Flow.  I've not done your sepcific requirement before but I could give it a go if I knew a bit more.  Ideally need sample expectations based on the q's above, specifically the outcome of the data into a SharePoint list.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Luciano_A]

Let me add here an Excel template, it should make it easier to explain 🙂

The structure will always be the same, the only peculiarity I am just thinking about is on the HRBP column, which might contain more than one name and should be treated ( where possible ) as a multiple-person-field.

https://we.tl/t-QQcCdBeAMn 

Based on this template as input, I would like a similar sharepoint as output of PowerAutomate:

Hire Date	Worker Name	Employee ID	Business Title	Location	Manager	HRBP	E-Mail address	Hire Type	Employee type	Address	Private Phone number
01/05/2021	John Doe	58980	Manager	Tokyo	Manager X	Partner X	john.doe@email.com	New Hire	Regular	Street 123, Toronto	+1 617000001
01/05/2021	Doe John	59026	Director	Berlin	Manager X	Partner X Partner Z	doe.john@email.com	New Hire	Regular	Street 123, Abu Dhabi	+1 617000002
01/05/2021	Iron Man	59111	Manager	Paris	Manager Y	Partner X Partner Y	iron.man@email.com	New Hire	Regular	Street 123, Praha	+1 617000003
01/05/2021	Super Man	59137	Director	Tokyo	Manager Y	Partner Y	super.man@email.com	New Hire	Regular	Street 123, Vienna	+1 617000004
10/05/2021	Green Hulk	59265	Employee	Tokyo	Manager Y	Partner X	green.hulk@email.com	New Hire	Regular	Street 123, Milan	+1 617000005

[DamoBird365]

@Luciano_A 

Assuming rows 1 to 8 are surplus to requirements, if you are looking for everything in row 9 down, I would create a table and then use the action get table. 

So, when you trigger your flow (file created in folder?), you can use the action to get tables and then retrieve the rows from that table.  Once you have that data, loop through it and create item for each row.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Luciano_A]

Hi @DamoBird365  That's exactly what I am looking to automate: Create a table on the Excel file - is it possible to have this process automated in any way?

[DamoBird365]

@Luciano_A if you are using a template excel file, why not make the table part of the template?  You don't need the Cloud Flow to create it?  You simply need it to find it and read it?

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Luciano_A]

Ups - sorry, I probably mis-expressed myself.

In the actual process, the Excel file is being automatically generated and distributed via E-Mail without a table.

I would like to skip the manual part of creating a table inside the file, and give as input directly the raw file to PowerAutomate.

[DamoBird365]

@Luciano_A 

What is generating the excel file for your email routine?  Why not make the table part of that template?  Otherwise, back to Excel Scripts 🙂

Damien

[Luciano_A]

Unfortunately, I don't see it possible 😕

Any tips for the Excel script pathway instead?

[DamoBird365 · Accepted solution]

Hi @Luciano_A 

I have created a basic proof of concept for you.

My cloud flow:

My excel sheet

 and the Excel Script Solution:

function main(workbook: ExcelScript.Workbook) {
  
  // Get the first worksheet 
  const sheet = workbook.getFirstWorksheet();
  
  //get active range of WorkSheet
  let range = workbook.getActiveWorksheet().getUsedRange();

  // Get last used row of WorkSheet
  let lastrow = range.getRowCount();

  //get table range  
  const TableRange = `A9:L${lastrow}`;

  // Create a table using the data range.
  let newTable = workbook.addTable(sheet.getRange(TableRange), true);
  newTable.setName("TestTable");
  
  // Get the first (and only) table in the worksheet.
  let table = sheet.getTables()[0];

  // Get the data from the table.
  let tableValues = table.getRangeBetweenHeaderAndTotal().getValues();

  console.log(lastrow);
  return tableValues

}

Not only does it create a table for you, should you want to use the get rows action, it returns the results back to the flow as follows:

Hopefully this is a good starter for you but please let me know how you get on.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Cheers,
Damien

P.S. take a look at my new blog here and like & subscribe to my YouTube Channel thanks 😉

[Luciano_A]

@DamoBird365 This is a perfect starter for me!!! Kudos to you 🙂

The very first step of taking a file, formatting it with a table and uploading it to a sharepoint list is finally done 🙂

I have now to reasearch a bit on how to transform data - I'll get there!