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

13 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. Hi Stephen,

    What if my text files contain spaces?

    Any ideas?

    I have tried the following

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

    For Each vFile in FileList('C:\TEMP\Folder\*.txt')

    BatFile:
    Load
    'MOVE' & chr(32) & CHR(34) & '$(vFile)' & CHR(34) & chr(32)& 'C:\TEMP\NewFolder' As [@echo off]
    AutoGenerate(1);

    Next

    BatFile:
    Load
    'pause' As [@echo off]
    AutoGenerate(1);

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

    Drop Table BatFile;

    EXECUTE c:\TEMP\test.bat;

    ReplyDelete
  5. I have finally created a video for this.

    https://youtu.be/aDagrS-EQrs

    I mention your books and blog Stephen, I hope you don't mind!!

    I might add, when I mentioned Barry's book, he promised me a FREE signed copy of his book when the video passed a 1000 views. #justsaying

    Thanks

    Alan

    ReplyDelete
  6. If the files have spaces, you need to add double-quotes.

    ReplyDelete
    Replies
    1. Yeah, the export doesn't like the double-quotes! It is quoting them.

      Can you use the 8.3 format of any filenames and folder? For example Program Files is progra~1, Program Files (x86) is progra~2, etc.

      Delete
  7. Perhaps use a different character - like | - and pass to a vbscript file to parse and execute.

    ReplyDelete
  8. Almost one year later... I have the same problem as Jerome... And I've noticed that even if I export the table, from Qlik interface into Excel and afterwards save it as a CSV file, the CSV file has the same problem with doulbe quotes ! Any ideas how to solve it, since then ?

    ReplyDelete
    Replies
    1. It seems this behaviour is an intrinsec property of CSV files...

      http://superuser.com/questions/867101/excel-adds-double-quotes-on-csv-export

      Delete
  9. is it possible for someone to post an example qvw. This is exactly what i need

    ReplyDelete
  10. is it possible for someone to post an example qvw. This is exactly what i need

    ReplyDelete

Note: only a member of this blog may post a comment.