Exporting documents from Exact Online for one division gives a spreadsheet instead of separate files

Question

Using the following SQL I get a list of documents and small thumbnails in Exact Online:

select document_account_name
       || document_references_yourref
       || year(document_date) || '-' || month(document_date) || '-' || day(document_date)
       || '.pdf'
       pdffilename
,      binarydata
from   exactonlinexml..documentattachments
where  document_documenttype_number_attr = 20
and    lower(name) like '%pdf'

When I try to export document from ExactOnlineXML..Documents using Invantive Control, I get a spreadsheet or text file with the file names and the contents when I execute:

local export results as "c:\export\dump.csv" format csv

However, I want to dump the actual file contents. Is there a way to do so?

Answer 1

There are several steps you have to follow.

First, you have to specify where to save the documents. You might also need to create a folder to store them in:

local define dctoutpath "${system:userdesktopdirectory}\invantive-control-for-exact-online\downloads"

local create directory "${dctoutpath}\HID\${dctdescription}"

Then you get the document attachments from Exact Online. You can do that through the DocumentAttachments table available in the Exact Online XML web service. (If you are using the combined provider you need the prefix ExactOnlineXML.. here)

select binarydata
,      name
from   ExactOnlineXML..DocumentAttachments 

Eventually you export the files through:

local export documents in binarydata to "${dctoutpath}\HID\${dctdescription}" filename column name

Note that binarydata and name have to match the fields you saved from your previous query. (According to your edit: instead of name you need pdffilename here)