Attach / combine CSVs to one CSV for data collection

Jul 19

Jul 19 Attach / combine CSVs to one CSV for data collection

Todays task will be:

Download mutliple CSV files from SharePoint
Attach those files to one report
Send the report via e-Mail

The script should also work with on-premise environment, you just have to load a different module.

Here are the three CSVs. The file “a.csv” and “b.csv” will be attached to “final.csv”. In your data collection CSV you will also have to provide a header. And it is important that all CSVs have the same structure!

We start off by declaring our SharePoint Url, connect to SharePoint Online via SharePoint PnP and create a bunch of variables and stuff.

$statFile = "csv"
This is the name of the SharePoint library.

$statLocalPath = "c:\temp\"
This is the path where we will store the files locally.

$localStatFile = "final.csv"
This is our data collection file, stored somewhere locally.

$attachmentPath = $statLocalPath + $localStatFile
This is the path to the local file that we will send via e-Mail.

$items = [System.Collections.ArrayList]@()
Our array list which will store the file names of all the CSVs that we want to append.

We will get all the list items from the SharePoint library and iterate through them. We will store the item name (file name) in the array list $items and download the files to our local destination, in this case it will be “C:\temp\”.

Now we will use the file names that we stored in $item and iterate through all the downloaded files. We are creating the paths for each file and also for the data collection CSV. After building the paths, we import the CSVs and export the content into the data collection CSV with the command -Append.

Finally we are sending the mail with the attached file via Office 365.

And that’s all there is to it. You can find the script below or in this Github repo.