Answered

Unable to add rows into table from dynamic file to another dynamic file

(0) Share

Report

Posted on by ZsoltDaBeast

Hi,

I have a case where I receiving an excel files (always the same format) into a sharepoint folder and I want to extract the rows from table in the anther excel file which gets generated by using "formatDateTime(utcNow(),'yyyy-MM-dd') Report.xlsx" file.

Source file name is dynamic, table "Inventory" is static.

Destination file is dynamic and table "Template" is static.

My flow:

Ideally, I want to use the output of Get files into the Add into tables. In the Get files, I'm checking if the "formatDateTime(utcNow(),'yyyy-MM-dd') Report.xlsx" file exist. If yes than Add the rows into it.

The problem is that in the "Add rows into table" function I cannot see a way to map the column names to destination table from the source table.
==================================================================================
It works if I assign the static file into it but this is not what I want.

I have checked this case Adding rows into table from dynamic file, unable to map columns but this is using static destination file.

Thanks for your help.

Categories:

General topics

All responses (5)

Answers (2)

Sort by

v-wenjuan-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @ZsoltDaBeast ,

Refer to my demo below:
You can manually edit your record in a compose action.
Result:

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.
ZsoltDaBeast 30 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @v-wenjuan-msft

Ok, so I have modified my flow by using "Compose" for my Source File to get the Rows (In your case Compose). and to my Destination file. (In your case Compose_2).

However, my destination file compose does not as it becomes an "Apply to Each" function if I add the "Identifier" or "value" or "body" or "file name with extension" of "Get Files (properties only)".

The destination file is not a static file but the table is (Template). The file name changes everyday using formula: "formatDateTime(utcNow(),'yyyy-MM-dd') Report.xlsx" file ".

RESULT:
Action 'Add_a_row_into_a_table' failed. A value must be provided for item. clientRequestId:.....

It sounds to me I need to GET the file path and use that value in the Compose_2 which would be than linked into "file" in the add row into table.

Any idea?

Thanks
ZsoltDaBeast 30 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi,

Is anyone out there can help in this, please?
Verified answer

v-wenjuan-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @ZsoltDaBeast ,

Sorry for not explain more clearly...
Actually in my demo, content in Compose action is destination file path. Content in Compose 2 is body of the record I want to add into the Excel.

I understand that your issue is if you add the file name by dynamic content rather than a static file name, columns will not list in action "Add a row into a table". There will be only a "Row" field display like this:

So in my Compose 2, I wrote the record manually like this:

{"A":"1","B":"2"}

Here is my excel structure:
A is column name, 1 is the content I want to add.

Hope I made myself clear this time 🙂

Community Support Team _ Wenjuan Zou
If this post helps, then please consider Accept it as the solution to help the other members find it.
Verified answer

ZsoltDaBeast 30 on at

Like
a
(1)

Report
Copy link

Link copied!

Hi @v-wenjuan-msft,

I done it but not with "Compose" as you suggested.

I have figured it out by using "Initialize Variables" for Source file and table & Destination file and table and I have used these values in the "Create table", "List Rows present in a table" and "Add row into a table". I had a lack of knowledge about how the variables works but after a bit of studying I have found them useful in my case.

By storing the value in variables made my life easier and the flow not get confused because sometimes it confuses itself and I had to build up from scratch.

In the value fields my expectation of files where the date changes on every day but the tables are statics. These name of values I used in my flow later down the line.
...than Create table" and "List Rows present in a table"
File: /Folder name/ Sub folder/ Name of sourcefile variable
Table: /Folder name/ Sub folder/ Name of sourcetable variable

Once the Source file and table exist than copy the content rows into a different dynamic excel file.
File: /Folder name/ Sub folder/ Name of destinationfile variable
Table: /Folder name/ Sub folder/ Name of destiantiontable variable

I hope this helps for others.
Thanks for looking at it.