This post is about a quite simple task to achieve – archiving SharePoint List data into a single Excel file that is stored in a Document Library.
I think it is important to go through this process to show the basics and have it as a prerequisite for a more complicated process – archiving data into multiple excel files.
There are several reasons why one would archive data into Excel:
- excel file is easy to move or share;
- it is very fast to work with (no paging, no 5000 item limit view threshold).
Let’s create a SharePoint List with different types of columns (single line text, date, number, choice):
Then we add a few test items:
After that we create a Document Library and put an Excel file into it:
That Excel file should have a table in it and the same columns as our SharePoint List. The formatting of each column can differ (e.g. date format can be its own) than what’s in the SharePoint List:
Then we create a flow as follows. A few key things about it:
- a trigger can be anything depending on your business process – it can be manually initiated or can run on a schedule;
- a scope of the items archived can vary as well – all items or just old items or matching other criteria;
- the array variable and Create HTML table steps are for reporting purposes (e.g. to send an email stating what items have been archived).