DOS command to Export a Table from QlikView QVF

[Qliksol]

Howdy,

I have a chart in a QVF that is updated each week.  I would like to run a DOS command to export the data to a CSV file.

I've read quite a few posts, but I'm not seeing anything like this.  Is it possible?

Thanks!

[JandreKillianRIC]

Hi @Qliksol 

What is the need for this? I assume its for automating the export. 

You can use a Macro in QlikView. 

But I think the easiest way would be to just do it in the script itself. 

Store [Table] into [C:\QlikApp\Data\Test.csv] (delimiter is ',');

This will store the table in a comma delimited file. 

You might need to do some data modeling work to get the result in the back end that replicates the chart (Expressions & Dimensions)  

[Qliksol]

Thanks for the response Jandre. 

I'm looking for automation, so manually opening files in Excel or Qlikview won't really work.  

Purpose of DOS is that it can be executed from just about any automation software imaginable.

So, I am imagining something like:

C:\Qlikview.exe  myqvf.qvf  -store  ('Table1', somefile.txt)

Hoping this is possible.

[JandreKillianRIC]

Hi @Qliksol 

Can you build this table in the backend? If so, Then I have a solution for you. If not, I will continue diving into my "old" files to see what I can get. 

Regards

[JandreKillianRIC]

Hi @Qliksol 

On the application reload make sure that "Closed when finished" is ticked. You must do a manual reload so that you can tick this box. 

Now go to the Module or "macro's" (Ctrl + M or Tools > Edit Module)

Add this script - Just change the CH01 to the table object id that you want to export to csv, also change the path and the filename. 

Sub ExportToCSV

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set ExObj = ActiveDocument.GetSheetObject("CH01")

ExObj.Export "C:\Users\Jandr\OneDrive\Desktop\Test.csv", ",", , TRUE

End Sub

Also ensure that the left bottom matches this (In the Module) System Access

Now go to Document Properties 

Under Triggers > OnPostReload > Edit Actions > External > Run Macro > Enter the macro name (Step 1 to 5)

Save and close the application. 

Now create a .bat file that has this content. Just change the path to the location of your qvf. 

"c:\Program Files\QlikView\QV.exe" /r C:\Users\Jandr\OneDrive\Desktop\Test.qvf

If you run the .bat file it will export your chart to csv in the desired location.

Please let me know if this solution works. 

[Qliksol]

Thanks for the very detailed instructions.  On a small test QVD, I have the file exporting on manual reload, but when I try to run the external batch file, it does not throw an error, but it does not produce an export.

The reload is going to be a major issue.  It takes well over 3 hours to reload our production QVD.  Is there any possibility of exporting a table without reloading?

Thanks again!

[JandreKillianRIC]

Hi @Qliksol 

If the reload is going to be an issue I might need to understand the complete picture. 

If the chart is only updated once a week then a export on reload should be sufficient? 

Each time there is a reload the object should be exported. Retrieving this csv file from a location )based on the last reload) would be the same as a DOS command that gets "the last reloaded object as a csv"

Please correct me if I am wrong? 

Regards -

Jandre

[Qliksol]

Hi Jandre,

I think exporting on reload might be OK. I'm very concerned about the long load time (3 hours) and all the other things that can go wrong.  I hate to add another straw to the camel's back.  The load crashes occasionally and I don't want to be the person that causes the next crash.

Testing will be problematic since I don't want to reload (3 hours) for each new test.

I have 25 years of SQL experience, but only a few weeks of Qlik experience (new job).  I like Qlik, it does a lot of cool stuff.  However, I'm a bit disappointed in it's lack of interaction with other tools.

Thanks again for your help.

[JandreKillianRIC]

@Qliksol 

That great news. Please dont forget to mark the solution as solved. Otherwise the question will stay open forever. 

Regarding Qlik. I have 16 years experience in Qlik, its truly an amazing tool. QlikView is the "legacy" tool, Qlik Sense is newer and Qlik Cloud is the latest. The underlying structure is the same (like QIX etc) but there are major improvements in the newer tech. In terms of integrating with other tools Qlik is actually not bad. They have a open API, not to mention Qlik Talent and Qlik Application Automation etc etc. However QlikView is lacking some of these as this is a "older" version. The first version of QlikView was released in 1994. 

There are "add on" tools like Qlik NPrinting or Qalyptus that sits on top of a Qlik solution to provide robust reporting and on automated exports / distribution of charts / tables and reports - however for this use case it might be too expensive for this solution. 

As I mentioned, if you can do some data modelling you wont need the macro (vba script and the triggers) as this will sit in the back end and automatically export upon reload. More than happy to help if this might be a requirement. 

Have a great day further and dont hesitate to reach out. 

Regards - Jandre

[eddyvargas]

Or use NPrinting, or Server (Store in csv) or RPA (Python)