Skip to main content

We have some processes developed which import Excel-files, for example for Sales Order creation.

This process looks like:

  1. Excel-file will be placed on a network-location
  2. 1 time per minute a Microsoft SQL Agent job will run and determine if there is a file in the network folder
  3. if there is a Excel-file in the folder, some T-SQL code will open this file via an OPENROWSET action (using ACE OLEDB 16.0, and modified registry setting TypeGuessRows from 8 to 1 to support more rows to be analyzed for data type determination) 
  4. the data of the excel-file will be stored in a general Import-table
  5. a procedure will then analyze the new imported data based on predefined rules per customer

So we have several templates to import for the Sales Orders as several customers want to use their own Excel-file format.

Then the issue we have:

Now and then (say once per year), the ACE OLEDBD-drive in MS SQL Server will stop working out of a sudden by also providing the following error-message: Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.16.0" for linked server "(null)".

The only way to ‘solve’ the issue then is to reboot the full MS SQL Server.

Looking at the information Microsoft is proving regarding the usage of the ACE component (https://www.microsoft.com/en-us/download/details.aspx?id=54920), it is stating that the component shouldn't be used by system-services or service-side programs.

So my question is if there are some best-practices in automated importing Excel-files into a Thinkwise end-application which we can consider to make our process more stable.

Thanks.

Hi C. Lousberg,

I think what you want is this idea: 

It is not part of the platform yet, so I’d advise you to vote for it, and add your use case as a comment.

For now, the only other option I see is a custom solution that interacts with Indicium directly.

You can perform an import in Universal and look at the network requests that Universal is making.

You’d be able to recreate those requests in an automation where you perform those requests in an interval when there are new items to import.

Here is the link to the import API documentation: https://docs.thinkwisesoftware.com/docs/indicium/importapi. The documentation for the import API is not complete, it is missing options like the manual mapping of columns. You could get those requests by performing those actions in Universal and looking at the network requests that Universal does.

Kind regards,

Tim de Lang

Reply


Terms & ConditionsCookie settings