Skip to main content
Announcements
Qlik Introduces a New Era of Visualization! READ ALL ABOUT IT
cancel
Showing results for 
Search instead for 
Did you mean: 
terezagr
Partner - Creator III
Partner - Creator III

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
Partner - Specialist II
Partner - Specialist II

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!

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense

View solution in original post

11 Replies
chrisbrain
Partner - Specialist II
Partner - Specialist II

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?

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
terezagr
Partner - Creator III
Partner - Creator III
Author

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
Partner - Specialist II
Partner - Specialist II

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.

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
terezagr
Partner - Creator III
Partner - Creator III
Author

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);

chrisbrain
Partner - Specialist II
Partner - Specialist II

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.

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
terezagr
Partner - Creator III
Partner - Creator III
Author

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
Partner - Specialist II
Partner - Specialist II

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

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense
terezagr
Partner - Creator III
Partner - Creator III
Author

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
Partner - Specialist II
Partner - Specialist II

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!

beeido.com - BI | Software | Qlik Integration Services
GitFirst - A CI/CD solution for Qlik Sense