One of the things that motivates us here at TekDog to get robust data relationships out of our SharePoint lists is reporting. We have all of these great Nintex Forms that we’ve designed that enter data into SharePoint lists, and then what? Of course you wouldn’t be bothering to have users input all of this data if you weren’t going to eventually do something with your data.
This is why we’ve created the TekDog GUID columns that are behind our subform, aggregate function, and Add New Child Item controls. We use these controls to help us tie our SharePoint list items together with one-to-many and many-to-many relationships, like in a database, so that we can create reports. Or even just export the data to another system.
So inevitably we will need a way to extract the SharePoint lists to a format that can be imported by a legacy system or a reporting system. Comma-separated values (CSV) is a pretty universal format for exporting, so often we will choose that format.
PowerShell provides a cmdlet called Export-CSV that will handle sending an array of values to a CSV file, but first we need to construct that array from the SharePoint lists. And furthermore we will want to tie two related lists together in the array to create a de-normalized “flat” CSV file for ease of use by the reporting or system that will use it.
HOW WE DO IT
So first we will open up the SharePoint site in PowerShell and create variables for two lists as well as a variable to contain the array to output.
In my example, I have an ExpenseReports list which uses the TekDog Productivity Controls’ subform to list individual list items from the Expenses list.
In my PowerShell script I continue by iterating through each ExpenseReports list item to get the field values from SharePoint into variables.
Then, while still inside the loop through the ExpenseReports list items, the PowerShell script needs to add the field values to an object representing each ExpenseReports list item as well as its associated Expenses list item. So a nested loop through the child list will create an object with values for each list item.
The child list items are also added to the object, and each completed object is added to the array we created at the start of the script.
Finally, the completed array is written to the CSV file.
You will of course have to modify the script with your list names and field names. Be sure to use the internal names when dealing with SharePoint lists and fields.
Once you’ve tested your completed script, you can put it in Task Scheduler to run on a regular basis as needed for you reporting system or other systems that will use this data. Isn’t it great to be able to use your SharePoint data?