From time to time I run across a customer with a system that does not allow ODBC connections for Look-ups. We need the data accessible by Workflow (or Quick Fields), so we need to get it into a data source that can handle the ODBC connections. If the 3rd party software will export to CSV, then our customer can export the metadata for their batch and now they have data they can look-up against. While CSV does allow for ODBC connections, it is not the best for production and you must always make sure the name is the same and it is in the same location for the ODBC configuration, so I like to have them import (drag and drop) the CSV into Laserfiche and have Workflow import it into a SQL table.
You must have a Text ODBC driver that matches the architecture of your Workflow services (32 bit Workflow requires 32 bit driver and 64 bit Workflow requires 64 bit drive). I like to use the Microsoft Access Database Engine 2010 Redistributable.
Also, you must connect to SQL with a user that has permissions to run the "OPENROWSET" command and your SQL must be configured to allow the Ad-Hoc Distributed Queries by running the below SQL code:
EXEC sp_configure 'show advanced options', 1 RECONFIGURE GO EXEC sp_configure 'ad hoc distributed queries', 1 RECONFIGURE GO
Then here is an example of the workflow I set up
I start with a conditional decision to only process documents that have csv extension. If it is a CSV file, I use a SDK Script to export the CSV file. (make sure to add reference and Imports statement for Laserfiche.DocumentServices)
Imports System Imports System.Collections.Generic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Text Imports Laserfiche.RepositoryAccess Imports Laserfiche.DocumentServices Namespace WorkflowActivity.Scripting.ExportCSVSDKScript '''<summary> '''Provides one or more methods that can be run when the workflow scripting activity is performed. '''</summary> Public Class Script1 Inherits RAScriptClass102 '''<summary> '''This method is run when the activity is performed. '''</summary> Protected Overrides Sub Execute() 'Write your code here. The BoundEntryInfo property will access the entry, RASession will get the Repository Access session Try Using doc As DocumentInfo = DirectCast(BoundEntryInfo, DocumentInfo) Dim docExporter As DocumentExporter = New DocumentExporter() Dim fi As System.IO.FileInfo = New System.IO.FileInfo( "E:\DATA\Work\temp\Check Run.csv") If fi.Exists Then fi.Delete End If docExporter.ExportElecDoc(doc, fi.FullName) End Using Catch ex As Exception End Try End Sub End Class End Namespace
After that, I run a custom Query to import the CSV into a SQL Table. If your CSV has a header row, you use code like this:
TRUNCATE TABLE [ExternalTables].[dbo].[CheckData]; INSERT INTO [ExternalTables].[dbo].[CheckData] ( [Document Type], [Check Number], [Check Date], [Period], [Vendor], [Check Amount]) SELECT A.[Document Type], A.[Check Number], A.[Check Date], A.[Period], A.[Vendor], A.[Check Amount] FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Text;IMEX=1;Database=E:\DATA\Work\temp\', 'select * from [Check Run.csv]') AS A;
And if your CSV does not have a header row, you run it like this:
TRUNCATE TABLE [ExternalTables].[dbo].[CheckData]; INSERT INTO [ExternalTables].[dbo].[CheckData] ( [Document Type], [Check Number], [Check Date], [Period], [Vendor], [Check Amount]) SELECT A.F1, A.F2, A.F3, A.F4, A.F5, A.F6 FROM OPENROWSET ( 'Microsoft.ACE.OLEDB.12.0', 'Text;HDR=No;IMEX=1;Database=E:\DATA\Work\temp\', 'select * from [Check Run.csv]') AS A;
After the CSV is imported into SQL, we need to remove the CSV file from Windows and I use a Script activity for that:
Imports System Imports System.Collections.Generic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Text Namespace WorkflowActivity.Scripting.RemoveTempCSVScript '''<summary> '''Provides one or more methods that can be run when the workflow scripting activity is performed. '''</summary> Public Class Script1 Inherits ScriptClass90 '''<summary> '''This method is run when the activity is performed. '''</summary> Protected Overrides Sub Execute() 'Write your code here. Try Dim fi As System.IO.FileInfo = New System.IO.FileInfo( "E:\DATA\Work\temp\Check Run.csv") If fi.Exists Then fi.Delete End If Catch ex As Exception WorkflowApi.TrackError(ex.Message) End Try End Sub End Class End Namespace
Finally, I delete the CSV file from the repository.
Once the workflow is built, I create starting rules to trigger if a document with csv extension is created in or moved into a specific folder. Now anyone in the organization can export batch data, import it into the folder in Laserfiche, and have updated data for Workflow or Quick Fields to Look-up against.