-->
    Register   |  Login
    Search
    EDT Command Line Options and Task Scheduling
    • Schedule frequent data imports or data processing
    • Receive Email reports on Success or Failure
    • Email Hyperlinks for a user to auto load the EDT task for data correction!

     

    The EDT tool has many uses,  including validating and processing data from many sources.

    Once you have designed a data processing task you may well wish to automate the running of the task by way of scheduling the operation, and only make use of the EDT interface when there are data validation errors that a user needs to correct before the task can be completed.

     

      The great advantage of using EDT over other commercial import tools is the
    data interface can be used to easily correct data problems.


     

     

     

    BATCH FILE OPERATION

     

    EDT tasks can be launched from a Hyperlink or a Batch command file.

    With these abilities we provide a sample script file here to show how this is achieved, making use of the launch from Hyperlink ability within the generated report emails.

    You can copy this batch file, making amendments to the Task ID and and Email parameters as highlighted.

    Download : EDT-Command-Email-Example.bat

    @REM Excel Database Tasks (EDT) @REM Lean Software Ltd @REM For technical support please contact Lean Software via http://LeanSoftware.net @REM EDT Batch File Example with Email task results to user along with Hyperlink to open a failed EDT task @REM Script history : @REM Who When What @REM Lean Software (UK)Ltd 07/07/2016 Initial @REM Notes: @REM This script will call the EDT command line utility program "EDTCmdLine.exe" @REM for all options use the /h command line help switch. @REM You can automate the whole EDT process or just part of the process. @REM If part completed EDT will be left open for the user to complete the task. @REM This example is fully automated (/auto switch) the task will load data validate, send and exit. @REM This task can therefore be scheduled to run on a server or workstation, so long as Micosoft Excel @REM is installed on the machine. @REM The progres/output of the task is saved to a file, this file is then emailed to a user. @REM If the task fails the email also contains a hyper-link the user can click to open the task then @REM correct any data issues and complete the task. @REM CREATE A NEW OUTPUT FILE FOR THE OUTPIUT EMAIL @REM ================================================ @REM Set the Outut file name - this file will form the email body SET EmailBody=EDTOutput.html @REM Try to delete the output file only if it exists IF EXIST %EmailBody% del /F %EmailBody% @REM If the file wasn't deleted for some reason, stop and error IF EXIST %EmailBody% ( SET ErrorMessage=Could not delete file %EmailBody% it maybe in use GOTO OtherError ) @REM Start the email with a run date and time message ECHO Batch file %0 ran at %DATE% %TIME% > %EmailBody% @REM RUNNING THE EDT TASK @REM ============================================================== @REM This example task requires two Task Mandadtory Task parameters: @REM Make sure there are no spaces before or after the equals sign SET TaskID=4374A7D55DDD4FBA9EF53A71A7B209FA SET P1=102 SET P2=ABC SET P3= SET P4= SET P5= SET P6= SET P7= SET P8= SET P9= SET P10= REM Running EDT - writing results to %EmailBody% EDTCmdLine.exe /taskid=%TaskID% /p1=%P1% /p2=%P2% /p3=%P3% /p4=%P4% /p5=%P5% /p6=%P6% /p7=%P7% /p8=%P8% /p9=%P9% /p10=%P10%/auto /html >> %EmailBody% SET EDT_Result= %ERRORLEVEL% @REM Explanation of the above commands : @REM @REM SET TaskID = 4374A7D55DDD4FBA9EF53A71A7B209FA @REM Sets the Task ID to a script variable @REM @REM SET P1 = 102 .. SET P10 = @REM Assign Task parameters to a script variables as needed @REM @REM @REM EDTCmdLine.exe @REM The EDT command line program @REM @REM /taskid=%TaskID% @REM passes the Task ID script variable @REM @REM /p1=%P1% @REM This specifies a value for the Task Parameter script variable @REM @REM /auto @REM This specifies the Task should attempt to run to completion and exit @REM @REM /html @REM This specifies the output to be in HTML format rather than plain text @REM @REM >> SET EmailBody="EDTOutput.html" @REM This specifies the output should be appended to a text file named EDTOutput.html @REM This HTML file will be emailed to a user on script completion. @REM By using HTML format we are able to include hyper links or other useful HTML codes @REM in the email if we wish. @REM @REM SET EDT_Result= %ERRORLEVEL% @REM Captures the EDT program exit code 1 or 0 @REM SENDING TASK RESULTS BY EMAIL @REM ================================= @REM Here we wish to send the Task result to a user. @REM The email of task report uses the BLAT.exe utility @REM BLAT is an open source batch email utility available from SourceForge : @REM https://sourceforge.net/projects/blat/ @REM Make sure there are NO SPACES before or after the equals signs :- SET to=-to to@test.net SET f=-f test@test.net SET server=-server mail.test.net SET password=-pw "your password" SET username=-u from_user@test.net @REM EDT returns 0 on success, 1 on failure REM Sending email report depending on code returned: IF %EDT_Result% EQU 0 ( @REM Set email title SET subject=-subject "EDT Task *Success* Report" @REM Add Success header to email SET Header="The EDT Task Succeeded<br>" (echo.%Header%)>%EmailBody%.new type %EmailBody%>>%EmailBody%.new move /y %EmailBody%.new %EmailBody% @REM the Fart.exe (Find And Replace Text) removes the quote marks from around the Header text Util\Fart.exe --remove %EmailBody% \" @REM Send the email Util\Blat.exe %EmailBody% %to% %f% %subject% %server% %username% %password% -priority 0 ) ELSE ( @REM Set email title SET subject=-subject "EDT Task *Failure* Report" @REM ADD AN EMAIL HYPERLINK TO RUN THE FAILED TASK MANUALLY @REM ======================================================== @REM Add hyperlink HTML codes to the HTML message file that will open the failed EDT Task manually on click SET Header="The EDT Task Failed<br><p><a href='leanedt:/task=%TaskID% /P1=%P1% /P2=%P2% /P3=%P3% /P4=%P4% /P5=%P5% /P6=%P6% /P7=%P7%^

    /P8=%P8% /P9=%P9% /P10=%P10%'>Click this link to run this EDT task again and manually correct the data errors, validate and send the data</a></p>" @REM add the hyper link to the beginning of the email @REM we do this by creating a temp new file, write the hyper link to the file, then append the EDT Output file @REM the Fart.exe (Find And Replace Text) removes the quote marks from around the Header text (echo.%Header%)>%EmailBody%.new type %EmailBody%>>%EmailBody%.new move /y %EmailBody%.new %EmailBody% Util\Fart.exe --remove %EmailBody% \" @REM Send the email Util\Blat.exe %EmailBody% %to% %f% %subject% %server% %username% %password% -priority 1 ) @REM Exit batch file with EDT result code if required by scheduling software (uncomment the following line) @REM EXIT %EDT_Result% GoTo End :OtherError @REM Email other error to user SET subject=-subject "EDT Task *Failure* Report" Util\Blat.exe -body "%ErrorMessage%" %to% %f% %subject% %server% %username% %password% -priority 1 :End


    The above command script generates an Email report after Task execution.

    Below is the sample Failed Task email report - complete with a Hyperlink for the user to reload the task, correct the data problems and complete the task :

    EDT Reports the Data Validation errors that need to be addressed :

    The EDT Task Failed

    Click this link to run this EDT task again and manually correct the data errors, validate and send the data

    Batch file edt-command-email-example.cmd ran at 15/07/2016 17:39:39.43
    Excel Database Tasks (EDT) - Command Line Interface
    Copyright Lean Software Ltd 1998-2016
    Version: 3.3.247


    Parameters validated, loading EDT with commands :
    Argument #0 is -TaskID set to '4374A7D55DDD4FBA9EF53A71A7B209FA'
    Argument #1 is -Task Parameter with optional argument '102'
    Argument #2 is -Task Parameter with optional argument 'ABC'
    Argument #3 is -Task Parameter with optional argument ''
    Argument #4 is -Task Parameter with optional argument ''
    Argument #5 is -Task Parameter with optional argument ''
    Argument #6 is -Task Parameter with optional argument ''
    Argument #7 is -Task Parameter with optional argument ''
    Argument #8 is -Task Parameter with optional argument ''
    Argument #9 is -Task Parameter with optional argument ''
    Argument #10 is -Task Parameter with optional argument '/auto'
    Argument #11 is -HTML output enabled
    Loading Application......
    05:39:47:Loading configuration.
    05:39:47:Active directory.
    05:39:47:Initialise application.
    05:39:47:Load system configuration.
    05:39:47:Load licence.
    05:39:48:Load SQL flavours.
    05:39:48:Load EDT task files..
    05:39:49:Load parameters.
    05:39:49:Parameter validation:
    Parameter 1 : OK
    Parameter 2 : OK.
    05:39:49:Clear data.
    05:39:50:Load task:Excel Import - Command line test.
    05:39:50:Connecting to Database...
    05:39:50:Parameter validation:
    Parameter 1 : OK
    Parameter 2 : OK..
    05:39:51:Connect.
    05:39:51:Load data.
    05:39:51:Load data 26 rows.
    05:39:51:Apply defaults, formula and hyperlinks.
    05:39:51:Apply excel validation.
    05:39:51:Display validation results.
    05:39:51:Client validation failures (top 100):
    Row 14 : Email: Value required

    Row 17 : Email: Value required

    .
    05:39:51:Autofit data.
    05:39:51:Format columns.
    05:39:52:Analyse rows.
    05:39:52:Validate 24 rows with database.
    05:39:52:Display results.
    05:39:52:Database validation failures (top 100):
    Row 3 : Country name does not exist

    Database validation : Procedure failed

    Row 5 : Country name does not exist

    Database validation : Procedure failed

    Row 7 : Country name does not exist

    Database validation : Procedure failed

    .
    05:39:52:.
    05:39:52:Row validation summary:

    Rows selected 24
    Rows submitted 24
    Rows passed 21
    Rows invalid 3.
    05:39:52:This task is configured to prevent Send until all rows are valid.
    05:39:52:QUIT - Task FAILED, process exit code 1

     

    Correcting the Data Problems 

    The user simply clicks the Hyperlink in the email, this automatically Loads the EDT application and loads teh specified task and task parameters.

    Now the invalid data items are highlighted - the user can correct the invalid data and complete the import : 

     

    User clicks the Validate Icon, this tests teh data operation against the database returning any server error messages. This example is sending data to a stored procedure and is showing Error messages returned by teh procedure that is validation teh country name:

    When the user has fixed the problems they can then click Send to complete the data processing task :

     

    Below is the sample Task Succeeded email :


     

    The EDT Task Succeeded
    Batch file edt-command-email-example.cmd ran at 15/07/2016 16:42:04.48
    Excel Database Tasks (EDT) - Command Line Interface
    Copyright Lean Software Ltd 1998-2016
    Version: 3.3.247


    Parameters validated, loading EDT with commands :
    Argument #0 is -TaskID set to '4374A7D55DDD4FBA9EF53A71A7B209FA'
    Argument #1 is -Task Parameter with optional argument '102'
    Argument #2 is -Task Parameter with optional argument 'ABC'
    Argument #3 is -Task Parameter with optional argument ''
    Argument #4 is -Task Parameter with optional argument ''
    Argument #5 is -Task Parameter with optional argument ''
    Argument #6 is -Task Parameter with optional argument ''
    Argument #7 is -Task Parameter with optional argument ''
    Argument #8 is -Task Parameter with optional argument ''
    Argument #9 is -Task Parameter with optional argument ''
    Argument #10 is -Task Parameter with optional argument '/auto'
    Argument #11 is -HTML output enabled
    Loading Application......
    04:42:12:Loading configuration.
    04:42:12:Active directory
    04:42:12:Initialise application..
    04:42:12:Load system configuration.
    04:42:12:Load licence.
    04:42:12:Load SQL flavours.
    04:42:12:Load EDT task files..
    04:42:14:Load parameters.
    04:42:14:Parameter validation:
    Parameter 1 : OK
    Parameter 2 : OK.
    04:42:14:Clear data.
    04:42:14:Load task:Excel Import - Command line test.
    04:42:14:Connecting to Database...
    04:42:14:Parameter validation:
    Parameter 1 : OK
    Parameter 2 : OK..
    04:42:16:Connect.
    04:42:16:Load data.
    04:42:16:Load data 26 rows.
    04:42:16:Apply defaults, formula and hyperlinks.
    04:42:16:Apply excel validation.
    04:42:16:Display validation results.
    04:42:16:Client validation failures (top 100):
    .
    04:42:16:Autofit data.
    04:42:16:Format columns.
    04:42:16:Analyse rows.
    04:42:16:Validate 26 rows with database.
    04:42:16:Display results.
    04:42:17:Database validation failures (top 100):
    .
    04:42:17:.
    04:42:17:Row validation summary:

    Rows selected 26
    Rows submitted 26
    Rows passed 26
    Rows invalid 0.
    04:42:17:Analyse rows.
    04:42:17:Send 26 rows to the database...
    04:42:17:.
    04:42:17:Database Send summary:

    Rows submitted : 26
    Rows sent ok : 26
    Rows failed 0.
    04:42:17:QUIT - Task SUCCEEDED, process exit code 0

     

    Feedback Comments
    Feedback

    Your Contact Information

    Your Feedback