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.

[danosoft]

By Python not, but you can put a macro on the event to save it like that:

ActiveDocument.Save

[pmc]

But how do I run this qlikview event from Python?

[danosoft]

Depend from your version of Qlickview you have.

Btw you can't save from Python like i wrote, you can save from a Macro in qlikview, so, you can start the macro from a event (for example when you click a button or reload or when you close the document)

In you have the version 12 of qlickview you can use python for do script inside qlickview, with a component, look at there: https://community.qlik.com/t5/QlikView-App-Dev/Run-Python-Script-From-QlikView/m-p/1293329

[pmc]

So that means I cannot have a command in python to click a button in Qlikview for instance.

[danosoft]

Sure you can, for example i use some filter with Python in qlik, this is an example:

doc.ActivateSheet(<ActiveSheet>)

or

doc.Variables(<variable>).SetContent(1, True)
doc.Fields(<field>).Select(<value>)

doc is

doc = q.OpenDoc(docname)

[mitru986]

guys, I m trying to run a python script to automate the process of extracting some charts, but receive this error: AttributeError: 'NoneType' object has no attribute 'ExportBitmapToFile'

1) The full error message is:

##Python 3.11.5 (tags/v3.11.5:cce6ba9, Aug 24 2023, 14:38:34) [MSC v.1936 64 bit (AMD64)] on win3

Type "help", "copyright", "credits" or "license()" for more information.

= RESTART: C:\Users\...\Desktop\test2.py

Loading QlikView objects list from V:\Sales\Dealers\...\Targets_split_per channel\qv_extract_config.xlsx

Number of QlikView objects to extract: 1

Sorted by Application: True

Opening: qvp://qvprodapp.main..../OMD_Sales_Analysis_Front.qvw

Extracting: CH02-00_769237902

Traceback (most recent call last):

  File "C:\Users\...\Desktop\test2.py", line 50, in <module>

    ActiveDoc.GetSheetObject(qv_object_id).ExportBitmapToFile(temp_path + 'temp.txt','\t') # Export object's data to temporary file

AttributeError: 'NoneType' object has no attribute 'ExportBitmapToFile'#

2) the script, which is listed below, also refers to a separate excel file located on my PC, in order to know which objects to download, which looks like this:

3) the python script looks like this->

# Import libaries

import win32com.client, os, argparse, pandas as pd

# Initiate and configure command line arguments, will use 'argparse' library to read

parser = argparse.ArgumentParser()

parser.add_argument("--path", "-p", nargs='?', const=1, default=r'V:\Sales\Dealers\...\Targets_split_per channel\qv_extract_config.xlsx', help="Set path to config file, XLSX")

args = parser.parse_args()

print("Loading QlikView objects list from %s" % args.path)

# Load configuration file - a list of QlikView objects that need to be extracted

#qv_extract_config = pd.read_csv(r'O:/3. Marketing Analitical/_Coordination/_Dashboard/datasources/QlikView/Sales/qv_extract_config.conf', delimiter='\t')

qv_extract_config = pd.read_excel(args.path)

# Check number of QlikView objects to extract

print('Number of QlikView objects to extract:',len(qv_extract_config))

# Check if sorted by Application

print('Sorted by Application:',qv_extract_config.Application.is_monotonic_increasing)

# Launch QlikView

qvapp = win32com.client.Dispatch("QlikTech.QlikView")

# Set folder where temporary files will be stored

temp_path = '\\main....md\fs\OMD\O\SD\Sales_Support'

# Iterate over all QlikView objects, extract those where 'Flag' column is not blank

for index, row in qv_extract_config.iterrows():

    """

    Read parameters from configuration file:

    ---------------------------------------

    QV_server_URL - QlikView server URL (ex. 'qvp://qvprodapp.main.orange.md/')

    Application - QlikView application name (ex. 'OMD_Sales_Analysis_Front.qvw')

    QV_object_ID - QlikView object ID (ex. 'Server\CH57-11')

    Path - Destination folder where exported data will be saved (ex. '\\main....md\FS\OMD\O\MD\3. Marketing Analitical\_Coordination\_Dashboard\datasources\QlikView\Sales\')

    Filename - Destination file name (ex. 'CH57_11_Consolidated_daily.xls')

    """

    doc = row['QV_server_URL'] + row['Application']

    qv_object_id = row['QV_object_ID']

    file_path = row['Path'] + row['Filename']

    # Delete temporary file if it exists

    if os.path.exists(temp_path + 'temp.txt'):

        os.remove(temp_path + 'temp.txt')

    print('Opening:', doc)

    ActiveDoc = qvapp.OpenDoc(doc) # Open QlikView application

    print('Extracting:', qv_object_id)

    ActiveDoc.GetSheetObject(qv_object_id).ExportBitmapToFile(temp_path + 'temp.txt','\t') # Export object's data to temporary file

    df = pd.read_csv(temp_path + 'temp.txt', sep='\t' ,parse_dates=[0]) # Read previously exported data into Pandas

    print('Saving:', file_path)

    df.to_excel(file_path,sheet_name='Sheet1', index=False, engine='openpyxl') # Save data to its final destination

print('Extraction finished.')

qvapp.Quit()