Hoe dump ik alle data van een administratie in Exact Online

Vragen
2

Het ophalen van alle data (voor zover beschikbaar via een REST API) kun je doen met het volgende script. Dit haalt alle tabellen op uit Exact Online en schrijft die weg naar de map met documenten onder Windows. Bovendien maakt ie een subfolder aan waarin alle Exact Online documenten gedumpt worden.

Je kunt dit uitvoeren met Query Tool of Data Hub of Invantive Control for Excel:

local remark
local remark Script to dump all data and documents available through the REST API
local remark to CSV and XLSX files (documents in a separate folder).
local remark

local define DIVISION_CODE "764929"

use ${DIVISION_CODE}

local remark Define a limit such as " limit 100" when you want to restrict the number of rows. Otherwise set to empty string.

local define LIMIT ""

local define OUTPUT_PATH "${system:userdocumentsdirectory}\out"

local define OUTPUT_DOC_PATH "${OUTPUT_PATH}\doc"

local define GENERATED_SQL "${OUTPUT_PATH}\export-generated.sql"

local create directory "${OUTPUT_PATH}"

local create directory "${OUTPUT_DOC_PATH}"

select 'use ${DIVISION_CODE}' 
       stmts
,      '-' full_qualified_name
union
select 'select * from ' 
       || tbe.full_qualified_name
       || '${LIMIT}'
       || chr(13)
       || chr(13)
       || 'local export results as "${OUTPUT_PATH}\' 
       || tbe.full_qualified_name 
       || '.xlsx" format xlsx include technical headers'
       || chr(13)
       || chr(13)
       || 'local export results as "${OUTPUT_PATH}\'
       || tbe.full_qualified_name 
       || '.csv" format csv include technical headers fieldseparator "|"'
       || chr(13)
       || chr(13)
       stmts
,      tbe.full_qualified_name
from   SYSTEMTABLES@DataDictionary tbe
where  tbe.provider_name = 'ExactOnlineAll'
and    tbe.can_select    = true
and    tbe.TYPE          = 'table'
and    tbe.catalog       = 'ExactOnlineREST'
and    tbe.schema
       not in 
       ( 'Unsupported'
       , 'DataDictionary'
       , 'Accountancy'
       , 'ContinuousMonitoring'
       , 'License'
       , 'Sync'
       , 'Views'
       , 'Webhooks'
       )
and    tbe.full_qualified_name 
       not in
       ( 'ExactOnlineREST.CRM.Accounts'
       , 'ExactOnlineREST.CRM.Addresses'
       , 'ExactOnlineREST.CRM.AddressesRead'
       , 'ExactOnlineREST.CRM.BankAccountsRead'
       , 'ExactOnlineREST.CRM.Contacts'
       , 'ExactOnlineREST.CRM.ContactsRead'
       , 'ExactOnlineREST.CRM.Quotations'
       , 'ExactOnlineREST.Documents.DocumentAttachmentFiles'
       , 'ExactOnlineREST.Documents.DocumentAttachments'
       , 'ExactOnlineREST.Documents.Documents'
       , 'ExactOnlineREST.Financial.GLAccounts'
       , 'ExactOnlineREST.Financial.GLClassifications'
       , 'ExactOnlineREST.FinancialTransaction.TransactionLines2'
       , 'ExactOnlineREST.FinancialTransaction.TransactionLines'
       , 'ExactOnlineREST.Logistics.Items'
       , 'ExactOnlineREST.Logistics.ItemsRead'
       , 'ExactOnlineREST.Logistics.SalesItemPrices'
       , 'ExactOnlineREST.SalesInvoice.SalesInvoiceLines'
       , 'ExactOnlineREST.SalesInvoice.SalesInvoices'
       , 'ExactOnlineREST.SalesOrder.GoodsDeliveries'
       , 'ExactOnlineREST.SalesOrder.GoodsDeliveryLines'
       , 'ExactOnlineREST.SalesOrder.SalesOrderLines'
       , 'ExactOnlineREST.SalesOrder.SalesOrders'
       , 'ExactOnlineREST.SalesOrder.SalesOrderStockBatchNumbers'
       , 'ExactOnlineREST.SalesOrder.SalesOrderStockSerialNumbers'
       , 'ExactOnlineREST.CRM.DossierDocumentFolderContent'
       , 'ExactOnlineREST.Mailbox.MailMessagesReceivedByMailbox'
       , 'ExactOnlineREST.Documents.DocumentAttachmentFilesBulk'
       )
order
by     full_qualified_name

local export results as "${GENERATED_SQL}" format txt columns stmts exclude headers

local remark Needed on releases upto 20.0 since for instance BankLinks raises a 403 instead of returning no data.

set ignore-http-403-errors true

local remark Needed when some of the documents can no (longer) be retrieved.

set ignore-document-download-errors true

use ${DIVISION_CODE}

--
-- Dump all documents to a folder.
--
select AttachmentFromUrl
,      ID || '-' || FileName
       OutputFilename
from   ExactOnlineREST.Documents.DocumentAttachmentFilesBulk${LIMIT}

local export documents in AttachmentFromUrl to "${OUTPUT_DOC_PATH}" filename column OutputFilename

local remark Run contents of @@${GENERATED_SQL}.