The following script connects to the FTP server and maps a drive to the spreadsheet, (currently stored on SharePoint). I used Drive A as no one really has a floppy drive anymore.
Once connected, it collects the CSV with yesterdays date, (while also moving the previous days CSV to an archive), & injects it into the spreadsheet. This works very well, but it does require the tabs for each day to already be created and for the active field to be A1 or it will fail to import. The spreadsheet then has a Report tab with pre-populated vLookups.
It also builds in an email alert. This checks the SharePoint location to ensure the file was downloaded, rather than checking the FTP, therefore enabling an element of debugging. If it is not on SharePoint, it probably didn’t get created.
==============================================================
#FTP Server Information – SET VARIABLES
$net = $(New-Object -ComObject WScript.Network)
$ftp = “ftp://FTPServer/”
$user = ‘UserName’
$pass = ‘Password’
$folder = ‘/’
$target = “A:\DestinationPath”
$RootFolder = “A:\RootDestination”
$DestinationFile = “$RootFolder\Daily_Backup_Report.xlsx”
$yesterday = [DateTime]::Today.AddDays(-1).ToString(“ddMMyyyy”)
$archive = [DateTime]::Today.AddDays(-2).ToString(“ddMMyyyy”)
$date = get-date -format d
$SourceFile = “$yesterday.csv”
$SourcePath = “$RootFolder\$SourceFile”
$WorkSheet1 = “$Yesterday”
$Range1 = “A1:B1”
$Range2 = “A1”
$smtpServer = “OutboundMailServer”
$fromadd = “FromAddress”
$email1 = “DestinationEmailAddress”
#SET CREDENTIALS
$credentials = new-object System.Net.NetworkCredential($user, $pass)
function Get-FtpDir ($url,$credentials) {
$request = [Net.WebRequest]::Create($url)
$request.Method = [System.Net.WebRequestMethods+FTP]::ListDirectory
if ($credentials) { $request.Credentials = $credentials }
$response = $request.GetResponse()
$reader = New-Object IO.StreamReader $response.GetResponseStream()
$reader.ReadToEnd()
$reader.Close()
$response.Close()
}
$Net.MapNetworkDrive(“A:”, “\\ComputerName\Share”)
Remove-Item $SourcePath
#SET FOLDER PATH
$folderPath= $ftp + “/” + $folder + “/”
$Allfiles=Get-FTPDir -url $folderPath -credentials $credentials
$files = ($Allfiles -split “`r`n”)
$files
$webclient = New-Object System.Net.WebClient
$webclient.Credentials = New-Object System.Net.NetworkCredential($user,$pass)
$counter = 0
foreach ($file in ($files | where {$_ -like “$yesterday.csv”})){
$source=$folderPath + $file
$destination = $target + $file
$webclient.DownloadFile($source, $target+$file)
}
#PRINT FILE NAME AND COUNTER
If (Test-Path “$target\$yesterday.csv”)
{
# Merge CSV With Report
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($SourcePath)
$Worksheet = $Workbook.WorkSheets.item($WorkSheet1)
$worksheet.activate()
$Range = $WorkSheet.Range($Range1).EntireColumn
$Range.Copy()
$Workbook = $excel.Workbooks.open($DestinationFile)
$Worksheet = $Workbook.WorkSheets.item($WorkSheet1)
$worksheet.activate()
$NewRange = $Worksheet.Range($Range2)
$Worksheet.Paste()
$workbook.Save()
$Excel.Quit()
Remove-Variable -Name excel
[gc]::collect()
[gc]::WaitForPendingFinalizers()
# Write-host “File for Today found on FTP Site” -ForegroundColor green
Move-Item “$target\$archive.csv” “$target\Archive 2017\Core Files\”
}
else
{
# write-host “vBackup file not uploaded Today, Pls Check” -ForegroundColor yellow
$msg = new-object Net.Mail.MailMessage
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
#Mail sender
$msg.From = $fromadd
#mail recipient
$msg.To.Add($email1)
$msg.Subject = “Alert !!! – Daily Backup File Could Not Be Located !!!”
$msg.Body = “Please check BackupServerName. Daily Backup CSV was not downloaded from FTP Server.”
$smtp.Send($msg)
}
$Net.RemoveNetworkDrive(“A:”)