Unanswered

Flow problem copying data from a newly created Excel file in sharepoint

(0) Share

Report

Posted on by Community Power Pla...

Posted this earlier in the wrong section. Hopefully this is the correct one.

I've created a flow which collects an Excel file from a Sharepoint documents domain (after the file has been uploaded on Sharepoint), copies it in another Sharepoint documents domain, converts the Excel file a table and then is supposed to copy the data from the Excel file/table in another Excel file (also a table).

Unfortunately it cannot execute the last step (copying the data in another Excel file/table) because I assume it cannot recognise the column headers because I created a new Excel file and converted into a table in the same flow.

Is there a way to copy the Excel data in another Excel file after the first Excel file/table has been created?

Capture flow.PNG

Categories:

Building flows

I have the same question (0)

All responses (15)

Answers (1)

Pstork1 68,678 Most Valuable Professional on at

Like (1)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

I've done something similar to this with a file received via email. In my case the Excel file doesn't have a table when its received. I create the Table and then List the Rows in the table to get the values. That process does work, but I do have to add a 15s delay after creating the table before listing the rows to work with the values. I would try adding some delays between the Excel actions to make sure the Table work has committed before moving on.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

I've added a delay item of 15 sec, but still get the same error. It still cannot recognise the value of list rows. Also cannot add values in the item "Add row into a table". Probably because I still have the old values of the test where the list rows values where from a test Excel file.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

Got a little bit further, I've moved the item "Get Tables" after the item "Create Table", but now I get the following error:

InvalidTemplate. Unable to process template language expressions for action 'Apply_to_each' at line '0' and column '0': 'The template language expression 'outputs('List_rows_present_in_a_table')?['body/value']' cannot be evaluated because property 'body/value' cannot be selected. Array elements can only be selected using an integer index. Please see https://aka.ms/logicexpressions for usage details.'.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

Tried to change the fields in item "Add a row into a table" into expression but it changes it back in dynamic content? Strangely that same dynamic content is not findable in the search dynamic content bar. Need to find another solution again.

Was this reply helpful? Yes No
Pstork1 68,678 Most Valuable Professional on at

Like (1)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

You can't just add a row to a table, since a row is a complex object record. You need to access the individual values from the source row and add them to the destination cells in the destination row. Inside the Test Loop you should be able to create a loop through the rows you retrieve and add the values to a new row in the destination table. Your screenshot shows you doing it outside the Test Loop rather than inside.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

I've have tried to chance the value in the item ""Add a row into a table" from @{items('Apply_to_each')?['####']} to @{items('test')?['Bankcode organisatie']} but I get an error then. Otherwise I wouldn't know how to include these in the loop.

Was this reply helpful? Yes No
Pstork1 68,678 Most Valuable Professional on at

Like (1)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

When you use the Add a row into a table it picks up the column definitions from the spreadsheet and table. Each column will then have a field to be filled in with a value. See Name, City, and Age in the screenshot below. You can't add a whole row into a table, you need to specify each columns value in the row. Can you show a screenshot of your add a row action?

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

This is the current model. The dynamic content/expression in the fields are actually copied over from an older flow model in order to "fool" the flow in thinking the correct column headers are available when the copied Excel file is made into a Table. That was me thinking that would be the correct solution.
Example dynamic content: @{items('Apply_to_each')?['Aangemaakt op']}

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

One step further, one step back. I changed the value from "list rows" step to "get tables" value. The flow succeeds now, but it does not add rows to the Excel file.

Was this reply helpful? Yes No
Pstork1 68,678 Most Valuable Professional on at

Like (1)

Report

Re: Flow problem copying data from a newly created Excel file in sharepoint

If the tables have varying headers you will need to do a get table or list rows in table just before you do the add row for each table so it can read the table schema before you try to use it.

Was this reply helpful? Yes No