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.