Automatic Import of HealthStream Survey Data

If your healthcare institution is subject to Pay for Performance rules (probably Value Based Purchasing), chances are you are a HealthStream customer. In which case, someone on your staff probably has the unenviable job of using the Insights Online portal to get at your Patient Experience survey data.

The following PowerShell script can be scheduled, to download (via SFTP) your Raw_Extract data and import it into your SQL Server.

For scheduling, you’ll need to use the Windows Task Scheduler rather than the SQL Server Agent Job. For some reason the PowerShell array handler @(..) it does not play nicely with the SQL Server Agent Job.

You’ll also need to install two dependencies (PSFTP and 7-Zip) to make this work.

Also, modify the $fromdate and $todate depending how HealthStream admins setup your exract schedule.

## Colin A. White August 2013
## PowerShell Script to get weekly Healthstream
## compressed, password protectd archive

$psftpPath = "C:\Program Files (x86)\PuTTY\psftp.exe"
$7zpath = "C:\Program Files\7-Zip\7z.exe"

$sftpHost = ""
$userName = "HealthStreamUserName"
$userPassword = "HealthStreamPassword"

# Used to guess the zip file names
$fromdate = (Get-Date).AddDays(-15).ToString("yyyMMdd");
$todate = (Get-Date).AddDays(-9).ToString("yyyMMdd");

$filename = "RawData_Extract_$($fromdate)_$($todate)"
# Testing stub
# $filename = "RawData_Extract_20130720_20130726"

# Download via SFTP
$cmd = @("lcd D:\Healthstream", "get $($filename).zip", "bye")
$cmd | & $psftpPath -pw $userPassword "$userName@$sftpHost"

# Extract via 7-Zip
& $7zPath e -oD:\Healthstream -y -p"HealthStreamPassword" "D:\Healthstream\$($filename).zip"

# Bulk INSERT via SQL
Import-Module "sqlps" -DisableNameChecking
Invoke-Sqlcmd "BULK INSERT [Healthstream].[dbo].[HealthstreamData] FROM 'D:\Healthstream\$($filename).csv' WITH (FIRSTROW=2, CODEPAGE='Raw', FIELDTERMINATOR=',', ROWTERMINATOR='\n')"

# Remove left over text qualifiers
Invoke-Sqlcmd " UPDATE [Healthstream].[dbo].[HealthstreamData] SET [RespondentID] = REPLACE([RespondentID], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [ClientName] = REPLACE([ClientName],`'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [SurveyType] = REPLACE ([SurveyType], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [UnitDescription] = REPLACE([UnitDescription], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [SutdyStartDate] = REPLACE([SutdyStartDate], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [StudyEndDate] = REPLACE ([SutdyStartDate], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [StudyEndDate] = REPLACE([StudyEndDate], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [DischargeDate] = REPLACE([DischargeDate], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [CollectionDate] = REPLACE ([CollectionDate], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [Question] = REPLACE([Question], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [QuestionDesc] = REPLACE ([QuestionDesc], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [AnswerValue] = REPLACE([AnswerValue], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [AnswerDesc] = REPLACE([AnswerDesc], `'`"`', `'`');
UPDATE [Healthstream].[dbo].[HealthstreamData] SET [AnswerIncludeInAdjN] = REPLACE ([AnswerIncludeInAdjN], `'`"`', `'`');

# Post-processing Clean Up
If ((Test-Path "D:\Healthstream\$($filename).csv") -eq $True) { Remove-Item "D:\Healthstream\$($filename).csv" }
If ((Test-Path "D:\Healthstream\$($filename).zip") -eq $True) { Move-Item -Path "D:\Healthstream\$($filename).zip" -Destination "Microsoft.PowerShell.Core\Filesystem::\\aFileServer\YourFileArchive\Healthstream\$($filename).zip" -Force}


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s