DPA - Automate the conversion of report from CSV to Excel and upload to Sharepoint

Data Protection Advisor is a great application providing alerts and reports.

I created a scheduled custom report to show daily backups from Avamar.  The report job however only saves to CSV and cannot export file to a share.  You can publish to Sharepoint but this only compatible with Sharepoint Server 2013 so this was not an options for us.

I create a batch file which copies the CSV file from the website using wget.
I then run a powershell script to convert the CSV file to XLSX.
Thanks to Boe Prox - https://gallery.technet.microsoft.com/office/7c56c444-2476-4625-b1d9-821f30280e44
After conversion is completed the file is copied to a Sharepoint document library.
I then setup a windows task scheduler to run the batch file.

A couple of problems I ran into:

On a windows 2008 R2 server when trying to open the Sharepoint document library in windows explorer gives you the following error message:

“your client does not support opening this list with windows explorer”

To resolve this:

Install Desktop Experience on Windows 2008 R2

Go to features -> Add features -> Select “Desktop experience”

When i ran the batch file from windows task scheduler I got the following error "Microsoft Excel cannot access the file". 

To resolve this:

You have to create a folder (or two on a 64bit-windows):

(32Bit, always)

C:\Windows\System32\config\systemprofile\Desktop

(64Bit)

C:\Windows\SysWOW64\config\systemprofile\Desktop

Task scheduler failed to copy the file to mapped network drive

To resolve this:

Changed the copy to UNC path

Solution:  (can copy and paste into batch file)

"C:\Program Files (x86)\GnuWin32\bin\wget" -N --http-user=<username> --http-password=<password> --no-check-certificate --secure-protocol=sslv3 https://<servername>:9002/dpa-api/scheduledreport/results/DailyBackups.csv

sleep 5

powershell -NoProfile -command ". .\ConvertCSV-ToExcel.ps1"; "& {&'ConvertCSV-ToExcel' -inputfile DailyBackups.csv -output 'Daily Backup Report.xlsx'}" > E:\DPAreports\convert.log

sleep 5

copy /Y "E:\DPAreports\Daily Backup Report.xlsx" "\\<sharepoint server>@SSL\DavWWWRoot\DPA Reports" > E:\DPAreports\copy.log