PortiBlog

Azure Runbook: Export-CSV directly to FTP

28 augustus 2017

Our client just migrated to O365 from SharePoint 2010 where they had a scheduled console application feeding data to an external application, from SharePoint lists to an file server using FTP. The console application first created a local CSV file before uploading it to the server. Having migrated to the cloud they, of course, wanted to keep the same functionality in order to feed the external application.

So now we that no longer had a server to run an application or script on, we turned to Azure. The first part was creating a PowerShell script on our local environment to retrieve the data we needed.

Requirements: SharePoint 2016 Client SDK or SharePoint Online Client SDK
Note: If you are using the SharePoint Online Client SDK, be sure to change the “Add-Type -Path” to the 15 hive


Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" 
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" 

#set credentials | For the PRD environment the credentials should be stored with the Automation account > Shared resources > Credentials 
$Username = "*****" 
$PlainPassword = "*****" 
$Password = $PlainPassword | ConvertTo-SecureString -AsPlainText -Force 
$Creds = New-Object Microsoft.SharePoint.Client.SharePointOnlineCredentials($Username,$Password) 

#Specify logfiles 
$outPutFileName = [string]::format("filename.{0:yyyyMMddhhmmss}.csv", [datetime]::Now.AddDays(-1)) 
$outputDir = "d:\output\" 
$logfile = $outputDir + $outPutFileName 

#Array to store our data rows in 
$results = @() #Create the context 

$Site = "*****" 
$Context = New-Object Microsoft.SharePoint.Client.ClientContext($Site) 
$Context.Credentials = $Creds 

#Get the list items 
$web = $Context.Web 
$list = $web.Lists.GetByTitle("ListName") 
$qry = [Microsoft.SharePoint.Client.CamlQuery]::CreateAllItemsQuery() 
$items = $list.GetItems($qry) 
$Context.Load($items) 
$Context.ExecuteQuery() 

#Loop through the items 
foreach($item in $items) { 
#Create a object to use as datarow for the CSV 
$webObj = @{ 
Field1 = $item["FieldInternalName1"] 
Field2 = $item["FieldInternalName2"] 
Field3 = $item["FieldInternalName3"] 
Field4 = $item["FieldInternalName4"] 
} 
#Add the datarow to the array 
$results += New-Object PSObject -Property $webObj 
} 
#Export the results array to a localpath 
$results | Export-Csv $logfile -NoClobber -NoTypeInformation -Delimiter ";" -Encoding UTF8 

We retrieved the data and could export it to a local path. Now we had to upload the file to the file server.


#Get the local file 
$file = Get-Item $logfile 

#Get and upload file with ftp server params 
$ftp = 'ftp://IP-ADRESS/csv/' 
$user = 'USERNAME' 
$pass = 'PASSWORD' 

#Connect to ftp webclient 
$webclient = New-Object System.Net.WebClient 
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass) 

#Write-Verbose -message $file.Name 
try { 
$uri = New-Object System.Uri($ftp+$file.Name) 
$webclient.UploadFileASync($uri, $file.FullName) 
} catch [Exception] { 
write-output $_.Exception.Message; 
} 

So far so good, we had a working PowerShell script running successfully on our local environment. Now we just had to create a runbook and copy our code into it. However, by default the SharePoint Client SDK is not present with the Azure runbooks, so in order to get our code to run we had to add it.  For this we had to add a module which contains the cmd’s required to talk to SharePoint, there are some module packages available for download but we found the easiest way is to add your own module.

  1. Create a zip file of the following directory: “C:\Program Files\SharePoint Online Management Shell” make sure you copy the folder itself, not just the items within the folder
  2. Go to the Azure Automation account and go to modules:
  3. In the right screen, click “Add module”
  4. Browse to your .zip file and press Ok
  5. Wait till the “Ok” button lights up again and press it once more
  6. You should now see the module in the module list , wait for it to be “Available”.
  7. You can now use the SPO Client SDK’s in Azure

To use the SDK in to your Azure PowerShell session, simply replace:


Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.dll" 
Add-Type -Path "C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\16\ISAPI\Microsoft.SharePoint.Client.Runtime.dll" 

With


Import-Module Microsoft.Online.SharePoint.Powershell 

All done… or so we thought because how do we use “Export-CSV” within Azure. There is no folder to write the export to in order to retrieve it again right(?) and there is no way you can Export-CSV without doing so. Wrong! It seems that when the PowerShell script is being executed there actually is some sort of local storage available, which is being wiped once the code is done. But once we started trying this we found that after uploading our file by FTP it was empty, the title was correct but it had no content. So, we started logging every step of our code but could not find anything wrong. The file was exported correctly, it had content when we retrieved it back from the Azure Local storage but then during the upload process, *POOF* empty. After trying various potential solutions, even trying to upload the file to AzureBlobStorage first, we rolled back to our original code and… It worked! The file was uploaded with the correct name and content. It’s a journey…

Note:If you need the "Microsoft.SharePoint.Client.UserProfiles.dll" or the "Microsoft.SharePoint.Client.Taxanomy.dll" import the module below. Open the .zip and overwrite the existing DLL's with the latest ones from your /16/ Hive. You could even add additional Client DLL's by adding them to the folder and updating the SharePointOnline.psm1  file.


$arrDLLs += 'Microsoft.SharePoint.Client.dll'
$arrDLLs += 'Microsoft.SharePoint.Client.Runtime.dll'
$arrDLLs += 'Microsoft.SharePoint.Client.UserProfiles.dll'
$arrDLLs += 'Microsoft.SharePoint.Client.Taxonomy.dll'
$arrDLLs += 'NEW DLL NAME GOES HERE'
$AssemblyVersion = "16.0.0.0"
$AssemblyPublicKey = "71e9bce111e9429c"

After updating, re-zip the SharePointOnline folder (be sure to zip the folder itself, not the files) and import the entire .zip as a module as described earlier.

SharePointOnline

Call it in your runbook with:


Import-SharePointClientSDK

Submit a comment