How to read an attached excel with Qlik Mailbox Imap connector

[Joaquin_Lazaro]

Hello:

I have to read an excel file attached to a mail in a QlikView document.

I have well configurated de connector, and I'm able to read all the tables included on the conector but when I try to use the tables MessageGetAttachmentAsText (or Binary) I get the data in an unreadable format.

I have been trying some differents formats on script, getting unreadable data or this error:

Error: File extdata.cpp, Line 2348

MailboxIMAPConnector_GetAttachmentAsText:

LOAD

    *

FROM

[http://qlikpublisher:5555/data?connectorID=MailboxIMAPConnector&table=MessageGetAttachmentAsText&Ser...]

(qvx)

Could you help me, please ?

I have attached some pictures to show you that the connector is working

Thank you

Joaquín

[stevedark · Accepted solution]

Hi Joaquín,

I would always do this as a two step thing. Write the attachment to a file first and then read from that file.

This also then persists the files you are looking at.

I guess the attachment is currently writing somewhere, but you want to add &TargetPath= and specify a location for it.

You may be able to do it in one go, but you will need to change the format specifier (qvx) to be the format specifier for Excel. Save the file locally and open it with the wizard to find this, it will be something like (ooxml, embedded labels, table is Sheet1).

Hope that helps,

Steve

[Joaquin_Lazaro]

Hi Steve:

I recieve the xlsx file by email at 06:00 every day, I need to read this email an extract the excel data in the same script; I have been able to read the mail message and its content but I get unreadable values in the xlsx.

Of course, If is necessary some LOAD sentences it won't be a problem

No manual action is posible, because there are a lot of mails every day.

Is it possible change the values in the image by readable value ?

Thanks

Joaquín

[stevedark]

Hi,

You will need to get the file as Binary rather than Text. And then I suspect you will need to persist it with TargetPath as described above.

Steve

[chrisbrain]

Joaquín - Have you tried using the link created from the ..AsBinary table and using it as the 'Internet File' parameter after clicking the 'Web Files...' button in the script editor?

[Joaquin_Lazaro]

Hi Steve:

There are no target path in the connector as you can see in these images. Neither as Text and as Binary

Thanks

[Joaquin_Lazaro]

Hi Cris:

Yes, I have tried it.

These are the results:

Thanks

Joaquín

[chrisbrain]

It's the MessageDownloadAttachment table should have a parameter to specify where to download it to disk. Perhaps you could try that and confirm you can load it into QlikView directly.

[Joaquin_Lazaro]

Hi Cris:

The MessageDownloadAttachment table has not a parameter to download xls into disk, it only allows to perform a LOAD with the parms specified in the first message.

Thanks for helping me, and you too Steve

Joaquín

[stevedark]

That is very strange. I can see (from my QWC set up as well as your screengrab) that the TargetPath parameter is not showing in the config.

However, I have script that I run, using the latest QWC that has a TargetPath parameter which works as expected.

It could be that there is a bug in the UI which is hiding that parameter.

Chris will be the much better person to respond on whether it is a bug or not than me though.

I would suggest trying to add the TargetPath parameter manually to your script.

Steve