If you have a table of data that you would like to import into your Paperflow batches without having to hand-key the index values, then you can utilize Paperflow's Match and Merge to connect to the external table and pull the data in. Note that you will need at least 1 column of unique values that Paperflow can use to determine which row of data to extract. Use this article to learn how to set up and configure Match and Merge to be used in your Paperflow job.

Types of data that can be used

When initially setting up the Match and Merge, Paperflow will ask for a database connection. You would simply input your connection information and connect to the desired table to pull the data, but Paperflow is not restricted to just a database connection. There is also a custom code box that allows users to input a string that can connect to an excel spreadsheet, for example. I will further explain once we come to that in Paperflow on how to utilize the custom code entry.

Launch Paperflow

With Paperflow opened, create a new batch using the job that you would like to add the match and merge to.

For this example, we will be connecting to an excel spreadsheet to pull in the following information for our documents: First Name, Last Name, SSN, Title, Status, and Employee ID #.

With the batch opened, navigate to the Edit tab, and select Advanced Configuration. Under Custom Code, you will notice that there are 2 different types of Match and Merge: Document Match and Merge and Batch Match and Merge.

Match and Merge Wizard

For this example, we will be configuring a Batch Match and Merge. Click on the '+' symbol to add a new configuration next to Batch Match and Merge.

For Datasource Type, we will select 'OLEDB' and click Next

Under Connection Properties, we will use the custom connection string, as mentioned earlier, to use our excel spreadsheet. Click the Custom Connection String checkbox to enable the message box.

Using connectionstrings.com, we will use the following string in the Custom Connection String box:

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;
Extended Properties="Excel 12.0 Xml;HDR=YES;IMEX=1";

We can simply replace the Data Source with the location of the spreadsheet. Once the custom connection is inserted, click the Connect button to establish the connection to the excel spreadsheet.

Select the Lookup Table you wish to use on the spreadsheet, if there are multiple tables within the spreadsheet, then click Next.

In the Field Mapping window, select the Column Name that matches the Index Field in Paperflow. In the Match column, select the field that Paperflow will use to identify which row to pull the data from. For this example, we will use the Employee ID # field and click Next.

In the Match and Merge Options, you may specify which settings to use when match and merging the data. For this example, we will simply leave as default and click Next.

You are now finished configuring Match and Merge! Click Finish to complete the Match and Merge Wizard.

In the Job Configuration window, click OK to return to your batch.

Run Batch Match and Merge

Now that we are back to our batch in Paperflow, be sure you have the field you marked as Match in the previous steps populated. The Match and Merge is going to use this index data to determine what data it will pull in.

When ready, click the Batch Match and Merge button that is located in the Edit tab of Paperflow.

The Batch Match and Merge will run and once complete, will display the results.

All your Paperflow fields that were selected in the Match and Merge Wizard should now be populated with the data from the spreadsheet! You have now successfully set up and configured Match and Merge in Paperflow!