Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
QlikView/Qlik Sense Script to download email attachments using QVSource MailBox Connector
SET ThousandSep=',';
SET DecimalSep='.';
SET MoneyThousandSep=',';
SET MoneyDecimalSep='.';
SET MoneyFormat='£#,##0.00;-£#,##0.00';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD/MM/YYYY';
SET TimestampFormat='DD/MM/YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
/*******************************************************************
Sample script for the QVSource MailBox Connector (www.qvsource.com).
to download all email attachments to the local machine.
https://gist.github.com/chrisbrain/c29609471b7dcdb9d415
NOTE - In this script the server, username, password etc. have been
stripped from the request URLs as the connector will then
automatically pick out those configured in the connector UI.
*******************************************************************/
//
// QlikView/Qlik Sense must have write access to this location.
//
let vDownloadFolder = 'C:\Users\YOUR_USER_NAME\Desktop\';
//
// Edit this if you running QVSource on a different port.
//
let vQVSourceBase = 'http://localhost:5555/QVSource';
//
// Email folder to process.
//
let vFolderToSearch = 'Inbox';
MessagesInFolder:
LOAD
id,
UID,
inReplyTo,
folder,
to,
cc,
bcc,
from,
subject,
sent,
text,
html,
size,
attachments,
gmailMessageId,
gmailThreadId,
gmailLabels
FROM
[$(vQVSourceBase)/MailBoxConnector/?table=ImapMessagesInFolder&appID=&Folder=$(vFolderToSearch)]
(qvx) where attachments > 0;
Let noRows = NoOfRows('MessagesInFolder');
for i=0 to $(noRows)-1
let vID = peek('id',$(i),'MessagesInFolder');
let vUID = peek('UID', $(i), 'MessagesInFolder');
let vFolder = peek('MessagesInFolder', $(i), 'MessagesInFolder');
Attachments:
LOAD
'$(vID)' as id,
'$(vUID)' as AttachmentUID,
'$(vFolder)' as AttachmentFolder,
FileName as AttachmentFileName
FROM
[$(vQVSourceBase)/MailBoxConnector/?table=ImapMessageAttachments&appID=&Folder=$(vFolder)&MessageID=$(vUID)]
(qvx) where FileName <> '';
NEXT
LET noRows = NoOfRows('Attachments');
for i=0 to $(noRows)-1
let vID = peek('id',$(i),'Attachments');
let vFolder = peek('AttachmentFolder', $(i), 'Attachments');
let vUID = peek('AttachmentUID', $(i), 'Attachments');
let vFilename = peek('AttachmentFileName', $(i), 'Attachments');
//
// Note - we use the UID here as we need something unique to prevent attachments with the same
// name overwriting previous ones.
//
let vTarget = '$(vDownloadFolder)$(vUID)_$(vFilename)';
trace 'About to download $(vFilename) in email $(vUID) to $(vTarget)';
Downloads:
LOAD
'$(vID)' as id,
'$(vUID)' as Downloads_UID,
Status as Downloads_Status,
'$(vFilename)' as Downloads_Filename
FROM
[$(vQVSourceBase)/MailBoxConnector/?table=ImapMessageDownloadAttachment&appID=&MessageID=$(vUID)&AttachmentFileName=$(vFilename)&TargetPath=$(vTarget)&Folder=$(vFolder)]
(qvx);
next i
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment