topic Re: Loading attachments from multiple emails via QVSource in QlikView

Loading attachments from multiple emails via QVSource

terezagr — Thu, 18 Jun 2015 22:12:00 GMT

I have been trying to load attachments from multiple emails from an email's Inbox. The attachments are always in this format eg.

Name_of_file_15062015.csv or Name_of_file_08062015.csv

What I would like to achieve is to load all the attachments that have not yet been loaded. I thought I could use a For-Next function...but so far no luck. Could you please help?

Re: Loading attachments from multiple emails via QVSource

chrisbrain — Fri, 19 Jun 2015 07:17:26 GMT

Hi,

First of all - I think you may have pasted some sensitive info into the above so I would recommend deleting/editing it immediately.

Secondly, it's not clear what errors or issues you are having with the above, is it running successfully up to a point? Are you seeing errors in either the QlikView or QVSource logs you can share?

Re: Loading attachments from multiple emails via QVSource

terezagr — Fri, 19 Jun 2015 07:35:25 GMT

I did not see any sensitive information being passed, but I have deleted the script anyway. Thanks.

What happens is: My script is supposed to get all the *.csv files from the email, but instead it get only the attachment that is specified in the FROM statement.

What I would like to do is to create a procedure that would extract all *.csv file that have always the same format:

Name_of_file_ and a date, e.g. Name_of_file_15062015, Name_of_file_22062015

Re: Loading attachments from multiple emails via QVSource

chrisbrain — Fri, 19 Jun 2015 07:59:36 GMT

Could you put the script back but carefully check for any username and password parameters in the URL (I am pretty sure they were there before so I would also just recommend you change your password) so I can see?

Alternatively email support@qvsource.com.

Re: Loading attachments from multiple emails via QVSource

terezagr — Fri, 19 Jun 2015 08:27:44 GMT

I have actually amended the usernames and passwords in the original post. But very nice of you to be cautious about the security. Thanks again

MessagesInFolder:

LOAD

id,

UID,

inReplyTo,

folder,

to,

cc,

bcc,

from,

subject,

sent,

text,

html,

size,

attachments,

gmailMessageId,

gmailThreadId,

gmailLabels

FROM

[http://localhost:/QVSource/MailBoxConnector/?table=ImapMessagesInFolder&appID=&Server=outlook.office365.com&Port=&SSL=true&Username=&Password=]

(qvx) where attachments>0;

Let noRows = NoOfRows('MessagesInFolder');

for i=0 to $(noRows)-1

let vFolder ='Inbox';

let vMessageID = peek ('UID',$(i),'MessagesInFolder');

let vID = peek('id',$(i),'MessagesInFolder');

Attachments:

LOAD

'$(vID)' as id,

'$(vMessageID)' as UID,

'$(vFolder)' as Folder

FROM

[http://localhost:/QVSource/MailBoxConnector/?table=ImapMessageDownloadAttachment&appID=&Server=outlook.office365.com&Port=&SSL=true&Username=&Password=Folder=Inbox&MessageID=$(vMessageId)&AttachmentFileName=Name_of_file_15062015.csv&TargetPath=location:Name_of_file_15062015.csv]

(qvx);

LET noRows = NoOfRows('Attachments');

for i=0 to $(noRows)-1

let vFolder = 'Inbox';

let vMessageID = peek('UID', $(i), 'Attachments');

let vFilename = peek('AttachmentFileName', $(i), 'Attachments');

MailBoxConnector_ImapMessageDownloadAttachment:

LOAD

Status as ImapMessageDownloadAttachment_Status,

'$(vFilename)' as ImapMessageDownloadAttachment_Filename

FROM

(qvx);

Re: Loading attachments from multiple emails via QVSource

chrisbrain — Fri, 19 Jun 2015 16:16:55 GMT

Hi,

A quick look at your script suggests you are never using your last vFilename variable in the last for loop but rather have always hard coded the same filename which I think would explain your issue.

Re: Loading attachments from multiple emails via QVSource

terezagr — Tue, 23 Jun 2015 10:41:21 GMT

Hi Chris,

even when I use the vFileName in the script, this does not actually extracts all the files.

I have used it like this:

Re: Loading attachments from multiple emails via QVSource

chrisbrain — Tue, 23 Jun 2015 18:55:43 GMT

‌when you say it doesnt extract all the files, what do you mean? Are some/more being extracted now?

also, what is the location: in your above script doing? I can't quite understand that.

Re: Loading attachments from multiple emails via QVSource

terezagr — Wed, 24 Jun 2015 09:20:32 GMT

As I mentioned in my original post I need to extract attachments from emails from different people that email me let's say to my email address x@email.com.

So I can get an email from y@email.com with attachment Name_of_file_15062015.csv, next email will be from w@email.com with attachment Name_of_file_22062015.csv, next email from s@email.com with attachment Name_of_file_29062015.csv and so on.

What I want to achieve is to extract all of these attachments and save them on my local PC. I have build For..Next loop. I can now see that it loops through all the attachments, however it does not recognise the FileName.

Script I use

MessagesInFolder:

LOAD

id,

UID,

inReplyTo,

folder,

to,

cc,

bcc,

from,

subject,

sent,

text,

html,

size,

attachments,

gmailMessageId,

gmailThreadId,

gmailLabels

FROM

[http://localhost:/QVSource/MailBoxConnector/?table=ImapMessagesInFolder&appID=&Server=outlook.office365.com&Port=&SSL=true&Username=&Password=]

(qvx) where attachments>0;

Let noRows = NoOfRows('MessagesInFolder');

for i=0 to $(noRows)-1

let vFolder ='Inbox';

let vServer ='outlook.office365.com';

let vMessageID = peek ('UID',$(i),'MessagesInFolder');

let vID = peek('id',$(i),'MessagesInFolder');

let vFileName = peek('FileName', $(i), 'Attachments');

Attachments:

LOAD

'$(vID)' as id,

'$(vMessageID)' as UID,

'$(vFolder)' as Folder,

FileName as AttachmentFileName

FROM

[http://localhost:/QVSource/MailBoxConnector/?table=ImapMessageDownloadAttachment&appID=&Server=$(vServer)&Port=&SSL=true&Username=&Password=&Folder=$(vFolder)&MessageID=$(vMessageID)&AttachmentFileName=$(vFilename)]

(qvx);

LET noRows = NoOfRows('Attachments');

for i=0 to $(noRows)-1

let vFolder = 'Inbox';

let vMessageID = peek('UID', $(i), 'MessagesInFolder');

let vFilename = peek('AttachmentFileName', $(i), 'Attachments');

MailBoxConnector_ImapMessageDownloadAttachment:

LOAD

Status as ImapMessageDownloadAttachment_Status,

'$(vFilename)' as ImapMessageDownloadAttachment_Filename

FROM

[http://localhost:/QVSource/MailBoxConnector/?table=ImapMessageDownloadAttachment&appID=&Server=$(vServer)&Port=&SSL=&Username=&Password=&Folder=$(vFolder)&MessageID=$(vMessageID)&AttachmentFileName=$(vFilename)&TargetPath=C%3a%5cQlikView%5c$(vFilename)]

(qvx);

next i

*RE Location: I know there should be actually in Target Path C:\QlikView instead of the location, and I have it in my script. as follow: TargetPath=C%3a%5cQlikView%5c$v(Filename). So please ignore the location bit.

Re: Loading attachments from multiple emails via QVSource

chrisbrain — Sun, 28 Jun 2015 12:21:29 GMT

Hi,

I think there were quite a few issues with the above script - I have written one for you which should hopefully works - please read the comments for clarification of a couple of things.

QlikView/Qlik Sense Script to download email attachments using QVSource MailBox Connector

And let me know if it helps!

Re: Loading attachments from multiple emails via QVSource

terezagr — Mon, 29 Jun 2015 15:35:37 GMT

Works as expected Chris. Thank you!

Re: Loading attachments from multiple emails via QVSource

chrisbrain — Mon, 29 Jun 2015 16:51:54 GMT

Great!