Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Partner
Partner

Loading attachments from multiple emails via QVSource

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?

1 Solution

Accepted Solutions
chrisbrain
Valued Contributor

Re: Loading attachments from multiple emails via QVSource

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!

View solution in original post

11 Replies
chrisbrain
Valued Contributor

Re: Loading attachments from multiple emails via QVSource

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?

Partner
Partner

Re: Loading attachments from multiple emails via QVSource

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

chrisbrain
Valued Contributor

Re: Loading attachments from multiple emails via QVSource

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.

Partner
Partner

Re: Loading attachments from multiple emails via QVSource

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.office...]

(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=outlo...]

(qvx);

NEXT

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

[http://localhost:/QVSource/MailBoxConnector/?table=ImapMessageDownloadAttachment&appID=&Server=outlo...]

(qvx);

Highlighted
chrisbrain
Valued Contributor

Re: Loading attachments from multiple emails via QVSource

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.

Partner
Partner

Re: Loading attachments from multiple emails via QVSource

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:

[http://localhost:/QVSource/MailBoxConnector/?table=ImapMessageDownloadAttachment&appID=&Server=outlo...)]

chrisbrain
Valued Contributor

Re: Loading attachments from multiple emails via QVSource

‌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.

Partner
Partner

Re: Loading attachments from multiple emails via QVSource

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.


Email.PNG

Capture22.PNG


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.office...]

(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=$(vSe...)]

(qvx);

NEXT

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=$(vSe...)]

(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.

chrisbrain
Valued Contributor

Re: Loading attachments from multiple emails via QVSource

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!

View solution in original post