SharePoint: Extract data from an email attached CSV and add list items

Article
1/17/2024

Description

SharePoint has an out-of-box feature to save both emails and email attachments to a document library, however, it does not extract the data from the email attached. This article will show you how. This article walks you through the process of extracting data from the CSV attachment and adding items to a list using PowerShell scripts. This process is compatible for both SharePoint 2010 and 2013 on-premises environment. In this article, a SharePoint 2013 on-premises environment in used as an example.

Setting the scene

Consider a third-party tool that can send out daily reports in the form of CSV files. Also, this tool does not have any web service connection options. Instead of manually extracting the data from this CSV file, add the new data to a spreadsheet and build a report, you want to automate the process. Hence we are going to use SharePoint's inbound email option to save the CSV to a document library and use a PowerShell script to extract the data from the CSV to another list. A control column is added to the document library such that data from that CSV file is NOT pulled twice.

Requirements

Following are the requirements that need to be met in order for this process to be setup and configured successfully

Have access to the SharePoint server to store the script and add it to the Task Manager
Have inbound email setup and configured on the SharePoint 2013 environment
Have a document library setup with inbound email and a list with columns to match the data you want to record.
The CSV files format should be consistent i.e. it should not change. Change in the CSV file's format will cause errors in the script especially if it involves adding more list columns.

The workflow

Below is the workflow of how the data is extracted

The PowerShell Script

The PowerShell (PS) script consists of two main sections which are described below

Section 1

Download the CSV file to a temporary location

01.##############This is the download part of the script ##############
02.######################## Start Variables ########################
03.Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue 
04.$destination = "C:\Temp\scripts\CSVextract\temp\"
05.$webUrl = "http://sp13test.domain.com"
06.$listUrl = "http://sp13test.domain.com/harvestdropin"
07.##############################################################
08.$web = Get-SPWeb -Identity $webUrl
09.$list = $web.GetList($listUrl)
10.write-host "Destination location is "$destination
11.Write-host "Source Library is "$list
12. 
13.function ProcessFolder {
14.    param($folderUrl)
15.    $folder = $web.GetFolder($folderUrl)
16.     foreach ($file in $folder.Files) {
17.    $item = $file.item
18.        #Ensure destination directory
19.        $destinationfolder = $destination
20.    if ($item["dataextracted"] -eq "No")
21.         {
22.            write-host "dataextracted value is "$item["dataextracted"]
23.                $binary = $file.OpenBinary()
24.                $stream = New-Object System.IO.FileStream($destinationfolder + $file.Name), Create
25.            #write-host $stream
26.                $writer = New-Object System.IO.BinaryWriter($stream)
27.                $writer.write($binary)
28.                $writer.Close()
29.            $item["dataextracted"] = "Yes"
30.            write-host "dataextracted value is "$item["dataextracted"]
31.            $item.Update();
32.            }
33.        }
34.}
35.#Download root files
36.ProcessFolder($list.RootFolder.Url)

Section 2

Extract data from the CSV file

01.##############This is the extract data part of the script###############
02.$csvVariable= Import-CSV -path "C:\Temp\scripts\CSVextract\temp\*.csv" -Header("Name","Packaged","Shipped","Recycled")
03. 
04.#site name
05.$spWeb = Get-SPWeb "http://sp13test.domain.com"
06.#Get the SPList object to retrieve the list
07.$splist = $spWeb.Lists["Harvest"]
08.#Get all items in this list and save them to a variable 
09.#$items = $list.items 
10.      #loop through csv file
11.foreach($row in $csvVariable)
12.{
13.   
14.        $spListItem = $splist.Items.Add()
16.        if ($row.Name -ne $null) {$spListItem["Name"] = $row.Name}
17.
if ($row.Packaged -ne $null) {$spListItem["Packaged"] = $row.Packaged}
20.        if ($row.Shipped -ne $null) {$spListItem["Shipped"] = $row.Shipped}
22.        if ($row.Recycled -ne $null) {$spListItem["Recycled"] = $row.Recycled}
23.        $spListItem.Update()
24.}
25.Remove-Item -Path "C:\Temp\scripts\CSVextract\temp\*.*" -recurse
26.######################Extract data completed#################################

Demo

This video walks through the requirements mentioned above, the PowerShell script and ends with a demo

View

(Here is the link to a high resolution video)

Conclusion

Click Here to download a copy of the script with the demo CSV files. Once you have tested the script manually, you can add the script as a task schedule. As mentioned previously, this works both in SharePoint 2010 and 2013 on-premises environment. Now that you have items in your list, you can either use Report Builder or a third-party tool to build your report.

Share via