topic Re: Macro to export straight table to csv after reload on server. in QlikView

Macro to export straight table to csv after reload on server.

pgriffiths — Thu, 10 Dec 2015 11:40:21 GMT

I have been trying to do this for some time, but I can't seem to find an answer.
I need to export a straight table to a CSV file.
Below is a test setup I am using to simpify the process while investigating the problem.

I first create some test data in a load script as follows;

//Example Data
QUALIFY *;
Example_Data:
LOAD * Inline 
[Asset,User,OS
UK01,sparnaby,WinVista
UK02,tbrown,Win7
UK03,mrichards,Win8
];

To test the output folder has access, I output this table using the Store command as follows;

STORE Example_Data into c:\TestCSV\Table_Example_Data.csv (txt);

I then create a straight table to output the example data and add a count field with a total. I call this stright table "Table_Data".

I create a Macro to output the straight table as follows;

Sub ExportTest
 Set obj = ActiveDocument.GetSheetObject("Table_Data")
 obj.ExportBiff "c:\TestCSV\Data.csv"
End Sub

In the Edit Module section this macro is in, I set the following fileds;
Requested Module Security: System Access
Current Local Security: Allow System Access

In the main Qlikview document I also go into Settings->Document Properties->[Triggers] tab.
I select "OnPostReload" and click [Add Action(s)] button.
I add "Run Macro" and set the Macro Name to "ExportTest".

I then setup security Settings on the Qlikview Management Server.
[System] tab->[Setup] subtab->Select Server from the list.
[Security] tab on the server.
[Tick] Allow macro execution on server
[Tick] Allow unsafe macro execution on server
Clicked [Apply]

I then setup the output folder c:\TestCSV and added full permissions for the AD accounts the qlikview services run on.

I open and run the report fine from the qlikview client and c:\TestCSV folder contains both the output files
Data.csv
Table_Example_Data.csv

The problems is if I create a task on the Qlikview Management Server, it only outputs the one file.
Table_Example_Data.csv

Anyone got any idea how to do this?.

Many thanks in advance.

EDIT - I have added the example QVW file.

Re: Macro to export straight table to csv after reload on server.

pgriffiths — Fri, 11 Dec 2015 10:45:01 GMT

I have been reading the APIGuide and as a result tried the following macro that I believe should of worked.

Sub ExportTest
Set obj = ActiveDocument.GetSheetObject("Table_Data")
obj.ServerSideExport "c:\TestCSV\Data.csv", ";", 0 '0=ANSI(Default)
End Sub

This still doesn't output.

I even tried setting the folder permissions to allow everyone read write access, just to make sure.

This ServerSideExport seems to be a function to do exactly what I need, but it doesn't work?. Anyone know why?

Many thanks.

Re: Macro to export straight table to csv after reload on server.

pgriffiths — Fri, 11 Dec 2015 12:21:59 GMT

From testing it would seem the "OnPostReload" event is not being triggered when the Qlikview Management Console reloads the QVW via a task and this might be why the Macro isn't being run.

Re: Macro to export straight table to csv after reload on server.

pgriffiths — Fri, 11 Dec 2015 15:03:41 GMT

Does anyone know how to trigger a macro when a task is run in Qlikview Management Console?.

Re: Macro to export straight table to csv after reload on server.

marcus_sommer — Fri, 11 Dec 2015 15:12:40 GMT

Macros won't executed per qmc reload-task. You need to run such macros per Fat-Client either per command-batch or vbs, see here for examples:

Re: Run Macro on Reload (From Server)

Re: QlikView Batch Jobs execution using CONTROL-M

Re: Sending mails macro vbs

- Marcus

Re: Macro to export straight table to csv after reload on server.

pgriffiths — Fri, 11 Dec 2015 16:59:28 GMT

Thank you Marcus, I can see why it was not working now.

Done some testing and can see how the batch file works.

I should be able to trigger the batch file from the load script of a QVW, but wondered if it was possible to instead detect if the load script was running on a Qlikview Management Console from within the load script?

Something like

if(Application.Parent.Name = 'GMC', {Do-Something-To-Launch-Batch-File})

Re: Macro to export straight table to csv after reload on server.

marcus_sommer — Sat, 12 Dec 2015 00:52:04 GMT

I use for such detecting in front tasks which writes variable-values (stop/run) in txt-files which are not only used directly in qlikview also to control various batches, vbs-routines and excels with OnOpen-routines. But in your case might (I haven't tested it yet) a check on systemvariables like QvRoot or WinRoot easier.

- Marcus

Re: Macro to export straight table to csv after reload on server.

pgriffiths — Mon, 14 Dec 2015 09:17:51 GMT

I like the idea. Many thanks.

I could look for a custom made file that exists only on the server to determine if the script if being run on the server.

Maybe there is already a file that is unique to the server.

I will post what I find.

Re: Macro to export straight table to csv after reload on server.

gandalfgray — Mon, 14 Dec 2015 13:53:03 GMT

What I don't understand is why you want to export the table using macros, when you could do it in the load script with the Store command?

Re: Macro to export straight table to csv after reload on server.

pgriffiths — Mon, 14 Dec 2015 15:12:03 GMT

I thought the Store command only worked with Tables, not Straight Tables.

Re: Macro to export straight table to csv after reload on server.

gandalfgray — Tue, 15 Dec 2015 12:27:09 GMT

Store is a load script command and works with the tables you have in your QlikView application/load script, not with the charts/tables etc you have in the QlikView gui, so no it does not work with Straight Table objects.

But you can create a table in your load script which has the same data as your Straight Table object, and then Store it as a txt.

Re: Macro to export straight table to csv after reload on server.

pgriffiths — Tue, 15 Dec 2015 12:30:03 GMT

Thanks for confirmation. Thought for a moment I had missed something and gone down the wrong route.

Re: Macro to export straight table to csv after reload on server.

cmccafferty — Tue, 20 Nov 2018 16:35:53 GMT

This is amazing... but I was wondering if there is a workaround if the export csv file is more than 65000 rows?

At the minute my output is putting the remaining rows onto a new tab Sheet2, ie it's like old school Excel with the limited number of rows available.

My very simple script is this:

sub Export_Trust

set trust1 = ActiveDocument.GetSheetObject("CH205")
set trust2 = ActiveDocument.GetSheetObject("CH210")
set trust3 = ActiveDocument.GetSheetObject("CH207")

trust1.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVATC.csv"
trust2.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust1.csv"
trust3.ExportBiff "X:\Business Performance\Finance\Cash\Download\QVTrust2.csv"

end sub