Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
wow very nice explanation , i didnt thought we could use python with qlikview.
will definately try this script.
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?
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"
...........
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 ?
Hi Mouadbenaou, Qlik sense is very different respect qlikview. If you use a server you can automate reload and send email with nprinting.
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?
i
Do you have any method to fixed the issues? I have account the same issue. Thanks a lot.
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.
Hi
ExportBiff
is only for a Table, for chart you need to use the methods
ExportHtml
ExportBitmapToFile
or others....
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?