Have you ever been in a situation where you needed to run a batch file after running a task in publisher? Most of the time you would create a supporting task running an external program to take care of your situation using a batch file.
For example, maybe after creating a QVD in production you need to copy that QVD to your development server so developers have recent data to work with. Normally, I would have created a batch file to take care of this for me. That is until I realized that you could do this within a QlikView document using the Execute statement.
So instead of using a batch file to run through command strings you could do the same thing in a QlikView document.
You can accomplish this by using the Execute statement as follows:
// Copy QVD file to archive folder
EXECUTE cmd.exe /C xcopy “\\Prod\Source\Cal.qvd” “\\Dev\Target” /Y;
So let’s break down what we are doing with the Execute statement.
First of all we are calling the command interpreter using the cmd.exe executable. To use the command I’m using the /C switch which allows you to carry out the command specified by your string, then terminates.
Next, I pass the command I want the interpreter to carry out for me. In this case I’m using the XCOPY command. Setting the source as the QVD that I want to copy from and the destination as my development server. I also pass the /Y switch because it will suppress prompting to confirm you want to overwrite an existing destination file.
Another example might be cleaning up log files. In the following example I want to copy my log files to an archive folder and clean up my logs so I don’t have to search through days of logs in order to find the most recent log file. You can accomplish this with a batch file that is running a string of commands like this:
rem **
@echo off
xcopy “C:\MyFolder\*.log” “C:\MyFolder\Archive” /Y
del “C:\MyFolder\*.log”
exit
You can accomplish the same thing by using the Execute statement as follows:
// 1st step copy log files to archive folder
EXECUTE cmd.exe /C xcopy “C:\MyFolder\*.log” “C:\MyFolder\Archive” /Y;
// 2nd step Delete log files
EXECUTE cmd.exe /C del “C:\MyFolder\*.log”;
Again, let’s break down what we are doing with the Execute statement.
First of all, we are calling the command interpreter using the cmd.exe executable. To use the command I’m using the /C switch which allows you to carry out the command specified by your string then terminates.
Next, I pass the command I want the interpreter to carry out for me. In this case I’m using the XCOPY command. Setting the source as the location of where my log files are that I want to copy from and the destination as my archive location. I also pass the /Y switch because it will suppress prompting to confirm you want to overwrite an existing destination file.
Note:
If you receive a security alert you may need to change your user preferences. Under the security tab check the box for Script (Allow Database Write and Execute Statements)
5 Comments
Hi Oleg,
Excellent tip. I often use this method when my data is supplied via (incremental) data dumps. When a file is processed and stored as a QVD, I move the original file to an archive folder so that it isn’t reprocessed every time. I use the “move” command for this by the way. That might be a little safer than “(x)copy” and “del”, as it only removes the files when they’ve actually been moved.
Cheers,
Barry
Good point, thanks Barry!
I have a question.
I am using the EXECUTE in the Qlikview Script to create my folder structure.
It’s like this
EXECUTE cmd.exe /C mkdir $(qf.path.root)ETL;
EXECUTE cmd.exe /C mkdir $(qf.path.root)LAB;
EXECUTE cmd.exe /C mkdir $(qf.path.root)LEGACY;
EXECUTE cmd.exe /C mkdir $(qf.path.root)SCRIPT;
EXECUTE cmd.exe /C mkdir $(qf.path.root)SCRIPT\BATCHFILES;
EXECUTE cmd.exe /C mkdir $(qf.path.root)SCRIPT\EXTRACTION;
EXECUTE cmd.exe /C mkdir $(qf.path.root)SCRIPT\EXTRACTION\EXTRACT;
EXECUTE cmd.exe /C mkdir $(qf.path.root)SCRIPT\EXTRACTION\TRANSFORM;
EXECUTE cmd.exe /C mkdir $(qf.path.root)SCRIPT\EXTRACTION\LOAD;
how to do I turn @echo off using the above??? or is there a simpler way of doing this?
Thanks
Regards
Alan
Alan,
The simplest way of taking care of it would be by placing the “@” sign before the command you are going to run, see below:
EXECUTE cmd.exe /C @mkdir $(qf.path.root)ETL;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)LAB;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)LEGACY;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)SCRIPT;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)SCRIPT\BATCHFILES;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)SCRIPT\EXTRACTION;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)SCRIPT\EXTRACTION\EXTRACT;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)SCRIPT\EXTRACTION\TRANSFORM;
EXECUTE cmd.exe /C @mkdir $(qf.path.root)SCRIPT\EXTRACTION\LOAD;
Another option would be to create a batch file to do the same. First line of the batch file would be ECHO OFF.
Good luck,
Oscar
great post.
this examples could be helpful too (in spanish):
http://qlikviewapuntes.blogspot.com/2014/01/execute-para-copiar-archivos-y-otras.html