QLIKVIEW AUTOMATION WITH PYTHON

[matteo_mi]

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.

[shubshar]

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 

[LuizFernandoSena]

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??

[danosoft]

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"

[LuizFernandoSena]

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

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

[danosoft]

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

[dnagarajan]

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 ?

[danosoft]

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

[pmc]

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

[danosoft]

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

[pmc]

Thanks a lot

By the way in your original script on top of this article your have a function to quit qlikview but that doens' save the file if there was any update.

Is there any option to Save or SaveAs running by python script?