Friday, 21 March 2014

Generate and execute a batch file in QlikView

My good friend and fellow Irishman, Alan Farrell, pinged me on Twitter this evening with this question: "can you create a .bat file in qlikview and save the file to a specific folder"

There is a pretty easy way of generating a file in QlikView and that is to load the rows of text that you want in that file into a table and then use the Store command to write that table to a text file.  If there is only one column in the table, then no separators will be written.

One "gotcha" about writing text files like this is that the field name will always be written.  This is easy to get around in the case of a batch file because you can name the field to some valid batch syntax - like "@echo off" or "REM Start of Batch File" - and that will be fine.

Here is an example to generate a batch file that will move all of the text files from one location to another (pretty lame example!):

BatFile:
Load
'REM This is a batch file test' As [@echo off]
AutoGenerate(1);

For Each vFile in FileList('c:\temp\Folder1\*.txt')

BatFile:
Load 
'MOVE $(vFile) C:\Temp\Folder2' As [@echo off]
AutoGenerate(1);

Next

Store BatFile into c:\temp\test.bat (txt);

Drop Table BatFile;

EXECUTE c:\temp\test.bat;

Now, the final execute will only work in QlikView if you have the "Can Execute External Programs" turned on in the Settings tab inside the Script Editor.  If you have publisher, you can have the QlikView task generate the .bat file and then have Publisher execute it.


Stephen Redmond is author of QlikView Server and Publisher and the QlikView for Developer's Cookbook
He is CTO of CapricornVentis a QlikView Elite Partner. We are always looking for the right people to join our team.
Follow me on Twitter: @stephencredmond

5 comments:

  1. Hi Stephen,

    This worked perfectly,

    I made a few changes to suit what I needed, I can see some huge benefits in this, in my place of work we implement WMS systems and Qlikview as the add on BI tool, the WMS implementations contain lots of folders and bat files, what you have shown has me thinking of automating the set up of new implementations through Qlikview.

    Thanks so much

    Regards

    Alan

    ReplyDelete
  2. Nice. I use a similar technique to generate a HTML file within a load script. One app I have writes some aggregated numbers out to a HTML file for consumption in a browser by users without a QV licence.

    ReplyDelete
    Replies
    1. Hi steve,
      Where can i get the video of "create batch file"?

      Delete
  3. Sound fantastic, can you share a simple version of it :-) or maybe a video?

    I must do a video on Stephen's post too, if ya don't mind that is!!

    Regards

    Alan

    ReplyDelete
  4. This comment has been removed by a blog administrator.

    ReplyDelete