Excel/CSV Automation of a Report in QlikSense

[rishikeshtiwari]

Hi All,

I have a requirement that I need to export a table on daily basis in Qlik Sense Server. I have a report (Table) in Qlik Sense and I need the excel/CSV of this table when the application loads. So we need an automation that Qlik Sense should export the CSV/excel of this report after reloads of the application.

AND

Is there any way to store the tables in csv/excel sheet how we use for Qvd in Qlik Sense script. Can we store Excel/Csv like Qvd.

Thanks

Rishi

[petter · Accepted solution]

In the load script of your application you can have at the end STORE-statements that can store an in-memory table in a text (CSV)-file or a QVD-file. This in-memory table will not necessarily be like tables you create in the UI. That depends on what kind of calculations you do in your Qlik charts. For normal purposes you could do calculations and aggregations in the tables in the load script so you will get the desired results in the in-memory tables that you want to store in text or QVD-files.

STORE <table-name> INTO <a-file-name>.txt (txt);

STORE <table-name> INTO <another-file-name>.qvd (QVD);

The best solution would probably be to use Qlik NPrinting where you can store charts and data directly from Qlik applications not only in-memory tables to various Microsoft Office formats including of course Excel.

[rishikeshtiwari]

Hi Pitter,

Thanks for your response. But when I am using

STORE <table-name> INTO <a-file-name>.txt (txt);

or

STORE <table-name> INTO <a-file-name>.csv (csv);

I am getting these files in non readable formate. We can't read these files.

Is there any other way to store CSV/txt or excel without using Nprinting.

Is there any API or extension that can fulfill our requirement.

[petter]

The STORE into a text file works perfectly fine whenever I have used it. I can very easly read the produces text file into Microsoft Excel for example by simple using the extension CSV instead of TXT.

On requirement though it that the software that is going to read it is able to read a UTF-8 with BOM (Byte Order Mark). If it can't it might deem the file as unreadable or corrupt. That shouldn't be a problem with newer software versions.

Other options could be:

DISCLAIMER:

Caution: none of the listed options underneath are endorsed or recommended by Qlik as a company - but any of them might work. I have only personally tested the first one but I know that the others definitely should work if you are willing to put in the effort to "develop" and support them yourself or by the support of third-parties.

1) DATABASE write-back

Use the SQL statement of the load script to send INSERT statements supported by the ODBC-driver/SQL-database. You do have to be sure that the user/account that you are authenticated as has the rights to do inserts into the database.

Could look like this that is an actual example that works for me:

LIB CONNECT TO 'SQLite3 Datasource';
SQL INSERT INTO categories (CategoryID,
    CategoryName,
    Description
    )
    values( 100, 'Cat1' , 'Category one')
;

I have used SQLite that is free, open-source and extremely light-weight. It is purely single-user but from a load script viewpoint this should be entirely ok. The great thing seen from a Qlik perspective is that SQLite is very flexible about mixed datatypes in a column mirroring Qlik's similar flexibility when it comes to fields.

With this approach you would have to get the data out of the SQLite database. Which is a single file and very easy to connect to from most programming languages and scripts or even just export it into text-files/CSV-files with existing utilities.

If you need to write extensive QlikView load scripts to emulate what you create in the UI of QlikView then it almost defeats the purpose of using QlikView in the first place. Well at least when it comes to development productivity.

2) REST API write-back

You could write back to a website/web-service that you develop yourself. The snag is that you must use the GET-verb/GET-request which use a query-string to send the data. This is security-wise very risky - but if you have the service running on the same computer as the QlikView server and not expose it to anything but localhost it could maybe accepted. QlikView does not currently support POST-requests natively from the load script.

LOAD
      @1
FROM
    [http://<my-web-site-REST-API>/<correct-api-topic>?value1=$(v1)&value1];

3) Get an ODBC-driver that supports ODATA

Write to an existing ODATA system that you have and get the table data from there - that ODATA system could be Microsoft SharePoint for example.

Something like this: http://www.cdata.com/drivers/odata/odbc/

4) Use an ETL-tool / Data wrangling

An ETL-tool that supports reading/writing QVDs and possibly QVXs. There are at least two commercial products maybe even a handful of tools as of today that can do this. Two of these are: EasyMorph, Advanced ETL-processor (ETL-tools.com) I haven't had time to test any of them for real - so I can't really say much about them. They seems to be serious offerings though.

[rishikeshtiwari]

Thank you so much, Petter.