topic Re: DOS command to Export a Table from QlikView QVF in Qlik Automate

DOS command to Export a Table from QlikView QVF

Qliksol — Mon, 13 Jan 2025 23:27:09 GMT

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!

Re: DOS command to Export a Table from QlikView QVF

JandreKillianRIC — Tue, 14 Jan 2025 07:58:13 GMT

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)

Re: DOS command to Export a Table from QlikView QVF

Qliksol — Tue, 14 Jan 2025 14:25:33 GMT

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.

Re: DOS command to Export a Table from QlikView QVF

JandreKillianRIC — Tue, 14 Jan 2025 16:01:53 GMT

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

Re: DOS command to Export a Table from QlikView QVF

JandreKillianRIC — Tue, 14 Jan 2025 18:01:21 GMT

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.

Re: DOS command to Export a Table from QlikView QVF

Qliksol — Wed, 15 Jan 2025 15:20:22 GMT

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!

Re: DOS command to Export a Table from QlikView QVF

JandreKillianRIC — Wed, 15 Jan 2025 17:01:18 GMT

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

Re: DOS command to Export a Table from QlikView QVF

Qliksol — Wed, 15 Jan 2025 19:05:13 GMT

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.

Re: DOS command to Export a Table from QlikView QVF

JandreKillianRIC — Wed, 15 Jan 2025 19:25:20 GMT

@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

Re: DOS command to Export a Table from QlikView QVF

eddyvargas — Wed, 15 Jan 2025 20:41:27 GMT

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

Re: DOS command to Export a Table from QlikView QVF

JandreKillianRIC — Thu, 16 Jan 2025 07:25:23 GMT

Hi @Qliksol

I got the solution for you without doing a reload.

The Document Tigger that you added for post reload - Just change this to OnOpen. See below

Here is the new .bat file - You will see there is a 30 second wait time - This will allow for the application to open and the macro to run. Adjust this if needed.

@echo off REM Start the QlikView application and get its Process ID (PID) start "" "c:\Program Files\QlikView\QV.exe" "C:\Users\Jandr\OneDrive\Desktop\Test.qvf" for /f "tokens=2" %%a in ('tasklist /fi "imagename eq QV.exe" /fo list ^| findstr "PID:"') do set PID=%%a REM Wait for 30 seconds timeout /t 10 /nobreak >nul REM Close the specific instance of QV.exe using its PID taskkill /pid %PID% /f

This .bat file will open the QVF - Run the macro using the trigger and then close the application.

Please let me know if this is the solution you where looking for.

Regards - Jandre