Unanswered

Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

(0) Share

Report

Posted on by PeggyLang

381

I have numerous excel expense files to approve and process weekly.
I have an excel template so each file is formatted exactly the same.
I need to create a 'running list' of all line items from all of the excel files.

My thought was to create a flow to add each row of each expense report to a Sharepoint List.

I have created a Sharepoint list with columns that correspond to each column in the excel file.

Screenshot of Sharepoint List

Screenshot of excel file

Screenshot of the beginnings of my flow.

I have already run into a couple of issues;

I am unable to access 'Name' data from excel file in 'dynamic content' so that I can populate into 'Title' column of Sharepoint list

There is no dynamic content (I am looking for 'Total' from the excel file) available to populate 'Amount' column of Sharepoint list - I don't understand why not

Currently flow is referencing a specific file. I need flow to act on each new file.

Greatly appreciate any help.

Thanks!

Categories:

Building flows

I have the same question (0)

All responses (16)

Answers (1)

DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

Hey there @PeggyLang

I like your documentation and explanation. What application did you use for that?

Regarding your flow, Get Row will return 1 row only and relies on a key column with unique value to return a row. So if you had a column ID running incrementally from 1, you could supply 4 as key value for ID and return that row. It looks like you have misunderstood the action and are supplying file content?

Most likely you want to use "list rows present in a table" if this is based on a new file being created. When it comes to supplying dynamic content to the action, you can select "enter custom value" and choose your dynamic loaction/ library/file/table. You might need to build some of these strings in a compose first and the use the compose in the list rows action.

When I comes to why is name not available as dynamic content, I am not sure on that one. Maybe trying the above action will correct this?

Do you want to give it a go and let me know.

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

Was this reply helpful? Yes No
PeggyLang 381 on at

Like (0)

Report
Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

@DamoBird365

Hello Damian,

Thank you for responding - and on Easter Weekend to boot.

I use 'Snagit' for my screenshots and embellishments.

So I learned A LOT yesterday. You are correct, I needed to go to 'List rows present in table'. I ran into a BIG snag when I discovered that dates from Excel are not represented in SP list as I had expected, so had to segway into figuring out how to deal with that.

Currently my flow is WORKING - sort of.

Below is a screenshot of my simple flow.

Below are breakouts of the numbered sections.

My flow 'kind of' works.
There are empty rows in my excel table. It is a protected worksheet with a table and x# of blank rows for entry. So if you have less than x# of rows to enter there ends up being blank rows. (Perhaps I need to investigate building a 'fillable' form in Excel??) Flow seems to run smoothly until it comes across a blank row.
I'm hoping there is a simple fix to that? i.e., check to see if the row is blank first? but not sure as the action is 'list rows present in table'. The below is screentshot of error message.

The other issue is I will have upwards of 40 expense reports per week. I can make sure they are all filed in the same folder and make sure the naming protocol of each file is consistent, but I need flow to act on each new file in the folder as opposed to a specifically named file. I have not yet figured out how to accommodate that.
Below is a snapshot of my SP list. There are 38 items in it.
The first 19 'Title' was 'Last, First'.
The second 19 'Title' is 'Lang, Peggy'.
To test the flow I did point the flow to the very specifically named different and unique files, but as mentioned going forward I need flow to act on each now file in the folder.

So that's where I currently sit. I'm not very learned with flows and I have an intermediate working knowledge of excel. I rebuilt the expense report file to include numerous conditional formats, drop down cells, pivots and such and then I built the SP list (from the excel file as opposed to blank - how sweet was that!) and then I built the flow. The entire process probably took me 6 hours yesterday - someone much more learned than I would have taken much less time - but it is an accomplishment for me and I hope 3/4 of the way complete now.

Again thanks for your help.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

Hi @PeggyLang

Thanks for the SnagIt tip - will take a look.

For your blank or empty lines you could perform a filter on the data during the list rows, expand the advanced settings and create a formula like columnname ne '' and that should remove your blank rows.

The alternative is to use the filter array action and perform something similar empty(columnname) is not equal to true.

You mention problems handling all files? You were previously using when a new file is created trigger but now a reoccurrence? What made you change this? The new file trigger would run for all files as they are dropped into the folder.

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

Was this reply helpful? Yes No
PeggyLang 381 on at

Like (0)

Report
Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

@DamoBird365
Damien,
This 'project/need' has just come up via a client request and I am just building and in testing phase at the moment. This has not previously been working and suddenly now not working.
My concern is that the action 'List rows present in a table' needs a very specific file with name
'expense template wip formatted as table protected with full name' (this is named such just for testing purposes).
The 40 or so files I will be putting in the folder which I need to run through the flow will be named;
Doe, John 210301
Smith, Frank 210308
Young, Joe 210315
etc.

So I need flow to look at all new files instead of needing to be told which specific file to look at. Does this make sense?

I will work on the solution for blank row that you suggested. Thank you.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

Hi @PeggyLang

If I do this:

It fails

but if I try this:

If runs OK.

So, the important thing is realising you can do this with dynamic file names but you will need to get the file ID and not he file name. I notice the that table ID is not a name either but an ID.

You can get this using get tables and assuming it's the first and only table in each file, this would work (compose is file ID, the compose 3 is the table ID from get tables).

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

Was this reply helpful? Yes No
PeggyLang 381 on at

Like (0)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

@DamoBird365 this is awesome. Thank you so much. I'm tied up at the moment and can't try it, but certainly will later today and let you know.

Was this reply helpful? Yes No
PeggyLang 381 on at

Like (0)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

Wow! The learning here is priceless.
So I have reworked the flow such that when triggered the 'filename' is dynamic. So this works when a new file is created in the specific folder.

My only hiccup remains when the flow runs into an empty row in the table. Have yet to solve for that.

Was this reply helpful? Yes No
DamoBird365 8,942 Microsoft Employee on at

Like (0)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

Hi @PeggyLang

Good to hear this has been helpful, don't forget those kudos and solution marking 🙂

I tried the following for empty rows and it works for me:

Length of Value array is 5

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

Was this reply helpful? Yes No
PeggyLang 381 on at

Like (0)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

@DamoBird365 I must be getting the syntax wrong.
The first column in my table is 'Full Name'

The flow fails - see error message below.

I need flow to stop when it gets to a blank row.

Was this reply helpful? Yes No
Verified answer

DamoBird365 8,942 Microsoft Employee on at

Like (1)

Report

Re: Update Sharepoint list with rows from excel file when new file is uploaded to Sharepoint Document library

Hi @PeggyLang

It looks like spaces are not supported in Excel Field names https://powerusers.microsoft.com/t5/Power-Automate-Ideas/Have-Consistency-in-on-Filter-Query-and-Order-By-on-Excel/idi-p/297122
https://powerusers.microsoft.com/t5/Building-Flows/Simple-Question-Filter-Query-Where-Excel-Column-Name-Has-a-Space/td-p/382231

I've tried the same and couldn't get it to work. Are you able to adjust your table so that the columns do not have spaces in the names?

Alternatively you do the filter using filter array, you would get all rows and them use the filter array action with the column is not empty.

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

Was this reply helpful? Yes No