article QLIKVIEW AUTOMATION WITH PYTHON in Member Articles

QLIKVIEW AUTOMATION WITH PYTHON

matteo_mi — Wed, 21 Sep 2022 17:07:35 GMT

Long time ago I have developed a vbscript application to open qlikview, reduce some data according salesman filter, export the sales pivot table to excel and send an email with the excel sheet attached to the salesman using the blat.exe application. The other day i was wondering if the same job could be executed using python. So after two minutes i was googling in search of some code snippets to easily make the python script. After half an hour I was quite disappointed to not found anything already made so I opened Pycharm and started to make the basic code: a class to make an instance of qlikview, open the document, export the table, close the document, close the qlikview app.

Here the code tested on python 3.7:

from win32com.client import Dispatch
import pythoncom

class QlikView:
def __init__(self):
self.app = Dispatch('QlikTech.QlikView')

def OpenDoc(self, docname):
doc = self.app.OpenDoc(docname)
return doc

def CloseDoc(self, doc):
doc.CloseDoc()

def ManageDocument():
docname = "c:\EXAMPLE.qvw"
q = QlikView()
version = q.app.QvVersion()
print(version)
doc = q.OpenDoc(docname)
chart = doc.GetSheetObject("exampletable")
chart.ExportBiff("c:\exampletable.xls"

q.CloseDoc(doc)
q.app.Quit()

if __name__ == '__main__':
ManageDocument()

This is the code that I've written at today , for sure i will go on and rewrite all the app to send report attached in emails, if someone need more information, just send me a message.

Re: QLIKVIEW AUTOMATION WITH PYTHON

riishabhz — Fri, 22 Nov 2019 05:16:49 GMT

wow very nice explanation , i didnt thought we could use python with qlikview.

will definately try this script.

Re: QLIKVIEW AUTOMATION WITH PYTHON

DavidD — Thu, 09 Jan 2020 21:51:03 GMT

Thanks for sharing the script! I tried it and it worked like a charm!

However, how would you apply one or more filters before exporting the table?

Re: QLIKVIEW AUTOMATION WITH PYTHON

matteo_mi — Fri, 10 Jan 2020 08:22:14 GMT

Hi David, I'm happy that you like it.

To apply filter to data, you should use qlikview API

so for example to reduce data you can do this:

...........

doc = q.OpenDoc(docname)
chart = doc.GetSheetObject("exampletable")

filter = "filter value"

doc.Fields("name of fields to select data").Select filter

chart.ExportBiff("c:\exampletable.xls"

...........

Re: QLIKVIEW AUTOMATION WITH PYTHON

Mouadbenaou — Thu, 16 Jan 2020 08:29:37 GMT

hi Matteo

I am a data engeener, i never worked with qlik sense .

i was asked to see if there is a way to automate a daily process on qlik sense using python. does the fact htat qlik sense is installed in server needs changes in your script ?

Re: QLIKVIEW AUTOMATION WITH PYTHON

matteo_mi — Sun, 26 Jan 2020 22:56:49 GMT

Hi Mouadbenaou, Qlik sense is very different respect qlikview. If you use a server you can automate reload and send email with nprinting.

Re: QLIKVIEW AUTOMATION WITH PYTHON

danosoft — Wed, 26 Feb 2020 11:21:06 GMT

Hi i tried it in my desktop but i have this error when i try to do:

chart.ExportBiff("D:\exampletable.xls")

it do this error if i use in my chart (if i use in a table, not):

Version: 12.20.20700+2018-06-19 08:55:15+e2a0a63
Traceback (most recent call last):
File "C:\Program Files\JetBrains\PyCharm Community Edition 2019.2.4\helpers\pydev\pydevd.py", line 1415, in _exec
pydev_imports.execfile(file, globals, locals) # execute the script
File "C:\Program Files\JetBrains\PyCharm Community Edition 2019.2.4\helpers\pydev\_pydev_imps\_pydev_execfile.py", line 18, in execfile
exec(compile(contents+"\n", file, 'exec'), glob, loc)
File "D:/PythonPrj/QlikPrj/QlikView.py", line 32, in <module>
ManageDocument()
File "D:/PythonPrj/QlikPrj/QlikView.py", line 26, in ManageDocument
chart.ExportBiff("D:\exampletable.xls")
File "C:\Users\d.caprelli\Envs\QlikPrj\lib\site-packages\win32com\client\dynamic.py", line 527, in __getattr__
raise AttributeError("%s.%s" % (self._username_, attr))
AttributeError: GetSheetObject.ExportBiff

and i have 2 question:

1. But if i want to automate a daily process on qlikView using python. does the fact that qlikView is installed in server what i needs to changes in your script ?

2. is possible to open document and doing export in background, without open the qlik gui, from python?

Re: QLIKVIEW AUTOMATION WITH PYTHON

JerryHaHa — Mon, 27 Apr 2020 14:29:24 GMT

Do you have any method to fixed the issues? I have account the same issue. Thanks a lot.

Re: QLIKVIEW AUTOMATION WITH PYTHON

JerryHaHa — Mon, 27 Apr 2020 14:52:11 GMT

Hi, thanks for your methon. It is helpfull. And I want to know how to export the charts like Bar Chart, Pie Chart and etc...

Looking forward to your help, thanks.

Re: QLIKVIEW AUTOMATION WITH PYTHON

danosoft — Mon, 27 Apr 2020 14:57:37 GMT

ExportBiff

is only for a Table, for chart you need to use the methods

ExportHtml

ExportBitmapToFile

or others....

Re: QLIKVIEW AUTOMATION WITH PYTHON

JerryHaHa — Mon, 27 Apr 2020 15:06:40 GMT

Hi.

Thanks for your reply. I have use ExportBitmapToFile. It can use like: chart.ExportBitmapToFile()

But where I can find the excel files? or can I export the Bar charts to excel also?

Re: QLIKVIEW AUTOMATION WITH PYTHON

shubshar — Tue, 21 Jul 2020 18:40:33 GMT

Thanks for sharing nice idea on Qlik - Python integration for automating the data export.

Is it possible to connect Qlikview report (deployed in QV server) to export data instead of referring to physical folder?

@matteo_mi

Re: QLIKVIEW AUTOMATION WITH PYTHON

LuizFernandoSena — Thu, 06 Aug 2020 15:08:12 GMT

Wow. You're the best!

Is that possible to open a Qlikview file (qvw) reload it to get the data updated without use the PyAutogui to send those keys??

Re: QLIKVIEW AUTOMATION WITH PYTHON

danosoft — Thu, 06 Aug 2020 15:12:58 GMT

Sure, you can do that in a batch script like this (in windows):

"C:\Program Files\QlikView\qv.exe" /l /NoSecurity "<dir_qlik_project>\<qlikproject_name>.qvw"

Re: QLIKVIEW AUTOMATION WITH PYTHON

LuizFernandoSena — Thu, 06 Aug 2020 15:27:55 GMT

I'm trying it out but I'm getting some indentation error.

I'm using VSCODE. Is the code will work the same?

Re: QLIKVIEW AUTOMATION WITH PYTHON

danosoft — Thu, 06 Aug 2020 15:32:32 GMT

Perhaps i not explained well, this is a script to execute in a batch file (dos file .bat)

Re: QLIKVIEW AUTOMATION WITH PYTHON

dnagarajan — Tue, 09 Feb 2021 08:02:40 GMT

Great post.

@matteo_mi Is there a way we can do the same from Access point documents with filter. Have you used a use case like this.

@shubshar have you found a way to do from access point ?

Re: QLIKVIEW AUTOMATION WITH PYTHON

danosoft — Mon, 26 Apr 2021 12:47:03 GMT

If i need to put more Table in same XLS , with ExportBiff i see i can choose only one... how can i put in same XLS more Table in different sheet?

Thanks

Re: QLIKVIEW AUTOMATION WITH PYTHON

pmc — Sun, 23 Oct 2022 15:42:22 GMT

Hello

Is it possible to export 2 different charts to the same excel file?

Or in alternative run a macro from Qlikview that makes this process?

Thanks

Re: QLIKVIEW AUTOMATION WITH PYTHON

danosoft — Mon, 24 Oct 2022 08:11:31 GMT

Sure is possible, i did that yet. I use this way, i create different files and after with excel api i unite all

For do that, i use the library:

from openpyxl import Workbook, load_workbook, styles

So i put the different chart object in different object in this way:

chart = doc.GetSheetObject(<element_chart_1>)
chart2 = doc.GetSheetObject(<element_chart_2>)
chart3 = doc.GetSheetObject(<element_chart_3>)

After i punt different chart object in different files .xls

chart.ExportBiff(<excel1.xls>)
chart2.ExportBiff(<excel2.xls>)
chart3.ExportBiff(<excel3.xls>)

i chage the type from .xls in .xlsx

xChart = XLS2XLSX(<excel1.xls>)
xChart2 = XLS2XLSX(<excel2.xls>)
xChart3 = XLS2XLSX(<excel3.xls>)

xChart.to_xlsx(<excel1.xls> + "x")
xChart2.to_xlsx(<excel2.xls> + "x")
xChart3.to_xlsx(<excel3.xls> + "x")

for finish i put all in the same file .xlsx in different workbook

xl = Dispatch("Excel.Application")
xl.Visible = False  # You can remove this line if you don't want the Excel application to be visible
wb1 = xl.Workbooks.Open(Filename=path1)
wb2 = xl.Workbooks.Open(Filename=path2)
wb3 = xl.Workbooks.Open(Filename=path3)

ws1 = wb1.Worksheets(1)
ws2 = wb2.Worksheets(1)
ws3 = wb3.Worksheets(1)

wb1.Close(SaveChanges=True)
xl.Quit()

That's all