Welcome Guest, you are in: Login

QVSource: The QlikView & Qlik Sense API Connector (Docs)

MailBox Connector For QlikView & Qlik Sense (POP3/IMAP)

RSS
Modified on 2016/04/07 12:29 by chrisb Categorized as connector

This connector allows you to:
  • Download a list of your emails (with html/plain text contents) from POP3 or IMAP into QlikView/Qlik Sense.
  • Download a list of attachments for emails with attachments (version 1.0.4 and later) into QlikView/Qlik Sense.
  • Download the actual file attachments (for subsequent loading into QlikView) (version 1.0.4 and later) into QlikView/Qlik Sense.
  • Stream the contents of IMAP email attachments directly into QlikView / Qlik Sense (version 1.0.7 and later) using ImapMessageGetAttachmentAsText and ImapMessageGetAttachmentAsBinary tables.
  • Download the list of email headers for an email (IMAP only at time of writing - contact us if you need POP3 email headers).

For example you can download your Gmail (including Chat history) or even access a Microsoft Exchange Mailbox using IMAP over SSL.

Usage

IMAP MailBoxes

Listing Folders

Use the ImapFolders table to list the folders in your Imap inbox.

Image

Listing The Contents Of A Folder

On the ImapMessagesInFolder table, you can enter one of these folder names in order to list the contents.

Image

Note that you can also specify a subject query parameter and since date to further refine the results.

Listing The Attachments On An Email

Scrolling along the above table, we can see that some of the emails have attachments:

Image

To list the attachments of a single email, note it's UID value and enter this and the folder name into the inputs for the ImapMessageAttachments table:

Image

Downloading An Attachment

To download an attachment, enter its filename into the attachment file name input on the ImapMessageDownloadAttachment table along with the folder name and message ID. You will also need to enter a target filename to save the file to - this should be in a location which QVSource has write access to. We usually recommend a folder named \Temp underneath where QVSource is running from (typically c:\QVSource\Temp) as a standard working folder.

Once you have entered these, running the table should yield an 'OK' result:

Image

And you should find the file on your local machine:

Image

At this point, if the file is an Excel file, CSV or other file type which QlikView can read natively you can simply load it into your QlikView application (note that the above steps would also have been run in your QlikView load script using the QlikView load script to run each of the above table requests).

Downloading All Attachments

The sample script here shows how you could download all attachments from all emails in a given folder to your local machine from your QlikView or Qlik Sense load script.

Unzipping A File

In this example we have a zip file which we first need to extract before QlikView can read it.

To do this we can now use QlikView's Execute command to run an external program to unzip this file. In this example we use 7zip, so you should ensure this is installed on your machine.

We can then simply use the following QlikView load script statement to unzip the file downloaded in the previous step:


execute "c:\Program Files\7-Zip\7z.exe" e "C:\QVSource\Temp\temp.zip" -y -oc:\QVSource\Temp

After this has run, the extracted .csv file should be available on your local disk for loading into QlikView using the standard procedures.

POP3 MailBoxes

Accessing POP3 mailbox is very similar to the IMAP process outlined above. The main difference is that there are no Folders.

Downloading Gmail Into QlikView

The following section shows the configuration settings you can use to download Gmail into QlikView.

IMAP Method (Recommended)

To download your Gmail you must first ensure IMAP access is enabled in your Gmail settings as illustrated below:

Image

It is then simply a case of entering imap.gmail.com for the server, checking the SSL option and changing the port number to 993 then entering your gmail username and password as illustrated below:

Image

Troubleshooting

Note - If you are having problems accessing your Gmail from QVSource please see this page which links to https://accounts.google.com/b/0/DisplayUnlockCaptcha which you may need to go to periodically to to confirm that the access from the connector is authorised, particularly if you are doing bulk downloads. We would recommend doing daily downloads using the 'since' parameter and accumulating the data in a QVD file.

If you are still having problem, you might need to visit this page.

POP3 Method

To download your Gmail you must first ensure POP3 access is enabled in your Gmail settings as illustrated below:

It is then simply a case of entering pop.gmail.com for the server, checking the SSL option and changing the port number to 995 then entering your gmail username and password as illustrated below:

Image

Troubleshooting

Note - If you are having problems accessing your Gmail from QVSource please see the troubleshooting section for the Gmail IMAP method on this page.

Downloading Email From Exchange

Note for hosted/online Exchange and Office 365 see next section.

For privately administered Exchange servers

Exchange is a regular IMAP server, so the connector should work - just remember to turn on the IMAP protocol on in Exchange.

There are also some helpful POP3 and IMAP exchange links here (Exchange 2010) and here (Exchange 2013).

Downloading Email From Office 365 or Microsoft (Hosted) Exchange Accounts

It is also possible to download email from Office 365 or Microsoft Exchange Accounts into QlikView. We have had one do this successfully after reviewing this page and using the IMAP settings.

Image

Troubleshooting

If you having trouble accessing your MailBox, before contacting support please first verify that you are able to access it from another Email application such as Thunderbird using the same settings. This is likely the first thing we will ask to see over a support call.

Change Log

1.1.9 - 07/04/16
  • Added logging to API call logs.
  • Fixed memory consumption issue in Web Edition for this connector.
  • Removed feature to save latest log to cache.
  • Fixed bug where emails could not be retrieved (XML deserialised) from cache if they contained certain invalid characters (e.g. 0x0B, 0x0C, 0x01).

1.1.8 - 31/03/16
  • Removed unnecessary code to get count of emails in ImapMessagesInFolder table.

1.1.7 - 23/03/16
  • Added 'Before' input parameter to ImapMessagesInFolder table.

1.1.6 - 16/02/16
  • Mail IDs used in cache keys are now hashed.

1.1.5 - 29/01/16
  • html columns are now rendered as encoded html when running in Web Edition UI (previously the html was rendered inside the data table preview).
  • Added additional notes to the Username and Password parameters to explain that these can be deleted from the generated URL to have the values stored in settings to be picked up by default instead.

1.1.4 - 04/01/16
  • Added seen and flags columns to the ImapMessagesInFolder table.

1.1.3 - 08/12/15
  • Added a new checkbox input parameter 'Allow Self Signed Client Certificate'. You might wish to check this if you are receiving an 'The remote certificate is invalid according to the validation procedure' error or other certificate related error as long as you are sure you are connecting with the correct server.

1.1.2 - 20/07/15
  • Upgraded code (from obsolete version) for extracting html and plain text body of IMAP messages and now only logs an error if neither of these are found.

1.1.1 - 18/05/15
  • Added inReplyTo column to IMAP and POP messages tables.

1.1.0 - 15/04/15
  • Errors retrieving html or plain text part of IMAP messages should no longer cause processing of whole email to fail.

1.0.9 - 30/03/15
  • Updated target path parameter label to mention that it also should include the filename.

1.0.8 - 06/02/15
  • The Log of the Mail Component used is now written to LatestLog.txt in the Cache folder for the Connector. This is for debugging purposes and might be temporary. This assumes only one request of the connector is being made at a time, otherwise the log file might contain entries from multiple connections.

1.0.7 - 29/01/15
  • Added ImapMessageGetAttachmentAsText and ImapMessageGetAttachmentAsBinary tables for streaming attachments directly into QlikView/Qlik Sense without needing intermediate file.
  • Upgraded to latest version (3.0.15017.1203) of Mail component.
  • Updated label on target file path.

1.0.6 - 01/12/14
  • Imap folders should now be opened as read only and messages should no longer be flagged as read after being downloaded by QVSource.
  • Added additional error checks into ImapMessagesInFolder for debugging specific issue.

1.0.5 - 13/05/14
  • Added ImapMessageHeaders table.
  • Minor performance improvements.

1.0.4 - 28/10/13
  • Upgraded to the new UI style.
  • Added tables to list and download attachments.
  • Date and subject search query parameters are now ANDed together.
  • Fixed bug where max no of messages parameter was always out by 1.
  • Removed radio button options to use credentials from QVSource or embed in URL (they are now always embedded, however if you delete the UserName, Password, Port, SSL or Server parameters it will fall back on the setting saved in QVSource).

1.0.3 - 04/07/13
  • Fixed issue where exceptions requesting table would sometimes crash QVSource due to invalid XML characters in the stack trace.
  • Better checking of nullable values when downloading Imap mails (this could have caused an incomplete download in some instances).
  • Imap download should no longer fail completely if a single email fails.
  • For Imap, now handles case where To field is a MailGroup and shows the group name.

1.0.2 - 26/03/13
  • Fixed since label to correctly show date should be in dd-mm-yyyy format.

1.0.1 - 27/08/12
  • html column is now only html encoded is the format of the request is html (we now recommend using QVX in all connectors in which case this connector would return the 'real' html).

1.0.0 - 25/02/12
  • Added support for Imap inboxes.
  • !BREAKING CHANGES! Some settings and table names have changed, please renenter your settings and regenerate load scripts.
  • Html body is now html encoded to prevent it breaking the QlikView parsing.

0.9.0 - 24/02/12
  • Now attempts to extract all text parts of email (e.g. both html and plain text versions).
  • Now writes data out to QlikView or UI in blocks of 50 (rather than waiting for all emails to download).
  • User can now specify port number and whether to use SSL.
  • Now tagged as thread safe.

0.8.0 - 28/09/11
  • Initial Release


(QVSource works with Qlik Sense as well as QlikView - See this page for notes.)
QVSource - The QlikView & Qlik Sense API Connector | © Copyright 2011 - 2016 Industrial CodeBox Ltd