Summary

This article covers automating and scheduling eClinical exports.

Table of Contents

• Requirements
• Description of the solution

  • Example of scheduled string to execute

  • Script content with comments in italic

This guide is a suggested way of automating and scheduling eClinical exports, should there be a need. A script is provided as a starting point for tuning and adoption to the specific external to eClinical customer environment.

Requirements

1. Identify an eClinical user account with the proper role (Data Manager for instance) and access rights to run needed exports
2. Identify Instance ID number (shown at the end of URL at the “Export Data” page for the particular site
3. Identify Export ID number (shown at the bottom of the webpage at the “Export Data” page when cursor is over “Run” icon for a specific export such as “Text Export”, “Excel Export”, “SAS Export”, etc.)
4. The outlined below solution was setup for MS Windows environment.

Description of the solution

The visual basic script exportScript.vbs has been developed to initiate the needed export and pull the data to a designated local or network location. This script than can be scheduled to be executed with certain parameters passed through the command line interface. Those parameters are Export ID, Instance ID, the name of output export file (usually carries the name or ID of a particular trial) and the path where to save an output file.

Example of scheduled string to execute

cmd /c c:\Automated_Exports\exportScript.vbs export1 123456 ABCD1234 "C:\Automated_Exports\"

Script content with comments in italic

' Function to get count of occurrences in the page 

Function getcount(txt,findtxt)
  getCount = (len(txt) - len(Replace(txt,findtxt,"")))/len(findtxt)
End Function

Set ArgObj = WScript.Arguments 

ExportID = ArgObj(0)
roleID = ArgObj(1)
studyOutputName = ArgObj(2)
exportPath = ArgObj(3)

logPath = "C:\Automated_Exports\"

' number of retries to logon to eClinical due to possible unavailability of the system due to possible system maintenance   

connectiontries = 10

' number of retries to check for a completion of initiated export and availability of export file for download  

reporttries = 120

' Logon process with valid User Name and password where User123 are 123qwerty are examples of User Name and password

Set objFileSystem = CreateObject("Scripting.fileSystemObject")
Set objOutputFile = objFileSystem.CreateTextFile(logPath & roleID & exportID & "log.txt", TRUE)

Set obHTTP = CreateObject("MSXML2.XMLHTTP")   

tries = 0 
 
do

  Call obHttp.open("POST", "https://secure.datatrak.net/clickFind/LoginPage?textUserName=User123&textPassword=123qwerty&loginButton=Sign In", False)
  Call obHttp.Send

  Call obHttp.open("POST", "https://secure.datatrak.net/clickFind/DataCollectorServlet?action=exportData&export=true&exportID=" & exportID & "&roleInstanceID=" & roleID, False)
  Call obHttp.Send
  txt = obHTTP.ResponseText
  nmatch = getcount(txt,"Running")
  tries = tries + 1
  objOutputFile.WriteLine(now() & " - Start Export Loop: Try #" & tries & " Running found " & nmatch & " times")  
  WScript.Sleep(60000)

loop while nmatch = 0 and tries < connectiontries     ' Try till you get to the export page.

if tries < connectiontries  then   ' If export page appears
  tries = 0
  do 

    Call obHttp.open("GET", "https://secure.datatrak.net/clickFind/DataCollectorServlet?action=exportData&roleInstanceID=" & roleID & "&date=" & now(), False)
    Call obHttp.Send
    WScript.Sleep(60000)
    tries = tries + 1
    txt = obHTTP.ResponseText
    nmatch = getcount(txt,"Running") 
    objOutputFile.WriteLine(now() & " - Run Export Loop: Try #" & tries & " Running found " & nmatch & " times")  

  loop while nmatch <> 1 and tries < reporttries   ' Loop till Export is done, ie only one running is there or it times out
 
  if tries < reporttries then   ' If it does not time out then download the export file
   
    Call obHttp.open("POST", "https://secure.datatrak.net/clickFind/DataCollectorServlet?action=exportData&exportID=" & exportID & "&archiveID=latest&roleInstanceID=" & roleID, False)
    Call obHttp.Send
    Set mStream = CreateObject("ADODB.Stream") 
    If mStream.State = adStateClosed Then
      mStream.Open
    End If   
    mStream.Type = 1
    mStream.position = 0
    mStream.SetEOS
    mStream.Write (obHttp.responseBody)
    Call mStream.SaveToFile(exportPath & studyOutputName & ".zip", 2)
    mStream.Close

    else   ' If times out then write the error message in the log

    objOutputFile.WriteLine(Now() & " - Running of Export Timed Out or Could not check if the Export finished")   
    objOutputFile.close

    end if 

else   ' If the run export page was not opened then write the error in the log
   
  objOutputFile.WriteLine(Now() & " - Could not open the run export page")   
  objOutputFile.close
   
end if

Set objOutputFile = objFileSystem.CreateTextFile(logPath & roleID & exportID & "log.html", TRUE)
objOutputFile.WriteLine(txt)   
objOutputFile.close

Please note:

• Check for presence of “Running” on a page indicating running or completed status of an export
• Sleep(60000) statement setting time gap in ms between retries. Set to 1 minute in the example
• In the example above, file extension .zip is added assuming export in ZIP format

Script was developed by Mehul Kenia
KB was compiled by Arkadiy Kheyfets

Need more help?

Please visit the Fountayn Contact Information page.