- 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 https:///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