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?

What SQL query do you use? – Guido Leenders Oct 13 '16 at 07:43 — Guido Leenders, Oct 13 '16 at 07:43

score 1 · Accepted Answer · edited Jan 11 '18 at 15:56

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)

Thank you! Is there also a way to apply the server-side filters on the combined Exact Online SQL provider? It now only seems to work with the Exact Online XML only SQL provider. — Joery, Oct 13 '16 at 08:06
Several server-side filters on exact online xml have been included as additional views, such as BalancLlinesprocessed. The more filter-like server-side fields are not yet available with the combined provider. They are harder to add because the combined provider has a strict sql implementation. — Guido Leenders, Oct 13 '16 at 11:38

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

1 Answers1