Copy Row from New Excel File, Concat data and Upload to Sharepoint List

[omi18]

Hello, 

I have a process flow as below.

1) Every day a new excel file is stored on SharePoint. 

2) We have a master excel file with 3 different tabs, (Raw Data, Concatenate tab, Final Data)

a) In the Raw datasheet we copy-paste data from the newly created files. 

b) Concatenate sheet have a predefined formula

3) Copy and Paster data from Final datasheet to SharePoint. 

a) We have 2 SharePoint lists. if the data is more than 100 rows then we paste the remaining rows to the 2nd SharePoint list. 

Is there any way we can automate this process with the flow?

Thanks,

Omi

[jedunn]

Hello, I certainly think something like this is possible with flow. A few questions:

• Is the new uploaded workbook file always stored in the same place, with the same name?
• Does the new uploaded workbook have tables or a consistent range?
• Are you updating existing data or adding new rows to the final datasheet and the sharepoint list?

[omi18]

Hi @jedunn ,

1) Yes the workbook is always stored in the same folder. But with a different name. 

2) No, the uploaded notebook does not have a table. Just plain column with headers. 

3) There are scheduled flow runs every day to clear these lists. Every day we just paste these rows as new data. 

Let me know if you need more details.

Thanks,

Omi

[omi18 · Accepted solution]

Hi @jedunn ,

I built a flow to accomplish this business process requirement. Maybe not a correct way but it works as expected.

I wonder if there is any better / shortest way. 😅😅

1. Trigger when a file is created

2. Getting Current Time

3. Converting time to local Time Zone

4. Composing blank excel file

5. Creating an Excel File

6. Getting a content

7. Running an Excel Script to convert from CSV to Excel

8. Running an Excel Script to Create a table within a newly created excel file.

9. Calling a new table created.

10. Composing a table data

11. Calling rows from the table

12. Initialize variable

13. Parse JSON

14. Adding each row to the Master File from the JSON step

15. Calling rows from Master File Table (Divided into Parallel branches)

16. Adding each row to the SharePoint list

17. Calling all rows from the Master file table to delete

18. Deleting each row from the Master file table.

[jedunn]

Hello @omi18, this is an impressive flow! I think there may be ways to optimize like consolidating some scripts to one or using office scripts to return values like the array or rows you want to manipulate in future steps. I'm a product designer at Microsoft. I'd like to learn more about your scenario. If you are interested in sharing you can find me on LinkedIn.