Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW

QLIKVIEW AUTOMATION WITH PYTHON IS BACK!

cancel
Showing results for 
Search instead for 
Did you mean: 
matteo_mi
Partner - Contributor III
Partner - Contributor III

QLIKVIEW AUTOMATION WITH PYTHON IS BACK!

Last Update:

Sep 21, 2022 1:07:35 PM

Updated By:

Sue_Macaluso

Created date:

May 8, 2021 8:18:38 AM

Over a year and half ago, I published instructions for creating a class to communicate with Qlikview api using the Python language. Here is the link for your convenience: QLIKVIEW AUTOMATION WITH PYTHON

Many of you have written and sent me messages requesting to make a new post containing the instructions to extract Qlikview pivot tables in excel and send them via email.

So after more than a year and a pandemic in action, here is an example of extracting Qlikview tables and sending as an email attachment. All tested with Python 3.9

You can found the full files and code at  GITHUB 

Here I post only the Python script.

Basically, there is a configuration file of the email parameters and the path of the qlikview file. The qlikview script defines the table  containing the people who must receive the emails with their addresses and the table fields to be filtered with the filter value to be used to reduce the data.

Hoping you like it,  for question or issues write me on GitHub.

Matteo Minischetti

 

PyQlikSender.py

from win32com.client import Dispatch  # pywin32 module
import smtplib
from email.message import EmailMessage
import os.path
from datetime import datetime
import configparser
import logging

current_path = os.getcwd()
config = configparser.ConfigParser()
config.read_file(open(r'PyQlikSenderConf.txt'))
QLIKVIEW_DOCUMENT = config.get('configuration','QLIKVIEW_DOCUMENT')
SMTP_ADDRESS = config.get('configuration','SMTP_ADDRESS')
SMTP_PORT = config.get('configuration','SMTP_PORT')
EMAIL_FROM = config.get('configuration', 'EMAIL_FROM')
EMAIL_LOGIN = config.get('configuration', 'EMAIL_LOGIN')
EMAIL_PASSWORD = config.get('configuration', 'EMAIL_PASSWORD')
LOGFILE = config.get('configuration', 'logfile')
QVRELOAD = config.get('configuration', 'QVRELOAD')


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

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

    def reload(self,doc):
        doc.Reload()

    def clearall(self,doc):
        doc.ClearAll()

    def apply_field_filter(self, doc, filter_field, filter_value):
        doc.Fields(filter_field).Select(filter_value)

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



def sendmail(email, subject,  name, surname, filename):
    msg = EmailMessage()
    msg['Subject'] = subject
    msg['From'] = EMAIL_FROM
    msg['To'] = email
    content = 'Dear ' + name + ' ' + surname + '  your report is attached here'
   
msg.set_content(content)
    try:
        with open(filename, 'rb') as xls:
            msg.add_attachment(xls.read(), maintype='application', subtype='octet-stream', filename=xls.name)
    except Exception as e:
        print('cannot open attachment to send, error :' + str(e))
    try:
        with smtplib.SMTP_SSL(SMTP_ADDRESS, SMTP_PORT) as smtp:
            smtp.login(EMAIL_LOGIN, EMAIL_PASSWORD)
            smtp.send_message(msg)
    except Exception as e:
        logging.info('cannot send email to : ' + email + ' , error :' + str(e))


def manage_document(docname, current_path, tb_email):
    username = None
   
password = None
   
q = QlikView()
    version = q.app.QvVersion()
    print(version)
    doc = q.opendoc(docname, username, password)
    if QVRELOAD == 'Y':
        q.reload(docname)
    email_table = doc.GetSheetObject(tb_email)  # the object table containing email to send and filter to apply
   
today_date = str(datetime.today().date()).replace('-', '_')
    rowiter = 0
   
rows = email_table.GetRowCount()

    while rowiter < rows:
        title = email_table.GetCell(rowiter, 0).Text
        name = email_table.GetCell(rowiter, 1).Text
        surname = email_table.GetCell(rowiter, 2).Text
        email = email_table.GetCell(rowiter, 3).Text
        company = email_table.GetCell(rowiter, 4).Text
        filter_field = email_table.GetCell(rowiter, 5).Text
        filter_value = email_table.GetCell(rowiter, 6).Text
        tb1 = email_table.GetCell(rowiter, 7).Text
        print(title, name, surname, email, company)
        rowiter = rowiter + 1
       
subject = "Qlikview report service for: " + company

        if rowiter > 1:
            filename = tb1 + '_' + name + '_' + surname + '_' + today_date + '.xls'
           
q.clearall(docname)  # clear all filter
           
doc.Fields(filter_field).Select(filter_value)  # apply filter
           
try:
                chart = doc.GetSheetObject(tb1)
            except Exception as e:
                logging.info('error: ' + str(e))
            chart_path = current_path + '/' + filename
            chart.ExportBiff(chart_path)
            sendmail(email, subject, name, surname, filename)

    q.closedoc(doc)
    q.app.Quit()


if __name__ == '__main__':
    doc = current_path + '/' + QLIKVIEW_DOCUMENT
    logging.basicConfig(format='%(asctime)s - %(message)s', filename=LOGFILE, encoding='utf-8',
                       
level=logging.DEBUG)
    tb_email = 'email'   # qlikview table containing email address and info
   
manage_document(doc, current_path, tb_email)

 

Labels (2)
Comments
basav
Creator
Creator

Hi @matteo_mi , I have tried this code, however I am getting an error on Reload function, any idea why this error.

Traceback (most recent call last):
File "D:\PyQlikSender-master\PyQlikSender.py", line 114, in <module>
manage_document(doc, current_path, tb_email)
File "D:\PyQlikSender-master\PyQlikSender.py", line 73, in manage_document
q.reload(docname)
File "D:\PyQlikSender-master\PyQlikSender.py", line 31, in reload
doc.Reload()
AttributeError: 'str' object has no attribute 'Reload'

matteo_mi
Partner - Contributor III
Partner - Contributor III

Hi Basavaraju_S  you are right!

The problem is the variable passed to the reload, should be doc instead of docname

q.reload(docname) should be changed in q.reload(doc)

let me know if this solve your problem.

 

basav
Creator
Creator

Hi @matteo_mi , Thanks for the reply. 

I have changed the name to 'doc', application opened, reloaded, and filter was applied. 

However getting the attribution error again, please find below. 

Traceback (most recent call last):
File "D:\PyQlikSender-master\PyQlikSender.py", line 114, in <module>
manage_document(doc, current_path, tb_email)
File "D:\PyQlikSender-master\PyQlikSender.py", line 102, in manage_document
chart.ExportBiff("D:\PyQlikSender-master\exampletable1.xls")
AttributeError: 'NoneType' object has no attribute 'ExportBiff'

 

I have checked your previous article on the same, where Danosoft had mentioned to use 

ExportBitmapToFile or Exporthtml

 But even these are throwing the attribution error. Am I doing something wrong here?

 

For the tables I have manually mentioned the object ID TB01 is 'email' and TB02 is 'tb1'      perhaps I could be wrong here?

basav
Creator
Creator

@matteo_mi , I figured it out what I was doing wrong. Its working fine now. 

Before the ExportBiff, where we are fetching the second table through ObjectID I had given it wrong. Now its running fine. Thank you.

However the logging package is not working, something to do with the version. So I have commented it out. 

basav
Creator
Creator

@matteo_mi Good Day, I have got a new requirement, where I need to attach table image on the mail body, I am hoping you have an answer for this too, Any help is much appreciated. Thanks. 

I searched online for this, but still no luck.

Gau
Contributor
Contributor

@matteo_mi , Good Day, I have got the same error "AttributeError: 'QlikView' object has no attribute 'reload' " even after changing q.reload(docname) to q.reload(doc) . Please tell me what could be the problem.

Also, if I want to save the file after reload of new data, what will the script look like? Is that right?

class QlikView:

    ...

    def Save(self,doc):

        doc.Save()

 

def ManageDocument():

    ...

    q.save(doc)

Version history
Last update:
‎2022-09-21 01:07 PM
Updated by: