MetaServer > Help > Export to Database

030-030 MetaServer Export – Export to Database

With the MetaServer Export to Database action, you can directly update any database that can be connected through ODBC. ODBC (Open DataBase Connectivity) is a standard to connect to databases. Windows comes with pre-installed ODBC drivers for MS SQL, Excel, MS-Access and more. If the ODBC driver for your database in not installed, it’s a matter of searching the internet to find and install the ODBC driver.

Some example use-cases:
- A small business or an office’s department stores their customer or supplier data in a Microsoft Access database or Excel table.
- A hospital stores patient information in a Microsoft SQL database.
- A lawyer’s office stores client data in an Oracle database.

If a record is not already present in the database, it will automatically enter a new record. Optionally you can also choose to always add new records and never overwrite existing ones.

In our example, we will make use of the “CB - STATISTICS” workflow. This workflow is automatically installed with CaptureBites MetaServer.

01 Export to Database - Adding the Action

To add the Export to Database action, select the action after which you want to insert the Export to Database action and press Add -> Export -> to Database. The Setup window will automatically open.

You can also open the setup window of an existing Export to Database action by double-clicking the action or by pressing the setup button on the right side of the action or in the ribbon, as shown below.

02 Export to Database – Setup - Database

When the setup window opens, the Database tab is selected by default. Here, you connect with your database and select the required database table. You can also choose a database lookup field if you want to update existing records. Any changes you make in a database table are automatically applied.

TIP: The thumbnail on the right will follow you, so you can easily refer to the Setup window. Click on the thumbnail to make the image larger.

Don’t forget to add a fitting description of your action.

01 - Type: the default database type is ODBC. ODBC is a standard to connect to a wide range of databases.

A quick overview of the ODBC settings:

1) Data source: select the data source you want to use. An ODBC data source needs to be defined first using the ODBC Data Source Administrator tool in Windows. For a step by step instructions on how to define an ODBC Data Source in Windows, have a look at this guide.

Select data source from field: you can use this option to switch databases dynamically using a field value.

To access this setup window, press the “…” button next to Data Source. You can select the field containing your database name by pressing the drop-down arrow.

Note: Selecting a data source from a field is an advanced function often used by BPO (Business Process Outsourcing) companies to dynamically switch database in the same workflow depending on the customer they are processing documents for.

2) User name: some databases require to login. If so, enter the User name in this field.

3) Password: some databases require to login. If so, enter the Password in this field.

4) Timeout: when the database does not respond in the specified time, the action will fail. A time-out is often an indication that the database server is down. Involve your IT staff to resolve such issues.

02 - Table: a database typically stores data in one or more tables, such as a document types table, a suppliers table, a products table, etc. Specify the table that you want to update.

03 - Lookup field: press the drop-down arrow to choose between 2 options:

1) You can update existing records by selecting a specific database lookup field and MetaServer field holding the lookup value

2) If you don’t want to update existing records and only want to keep adding new records, you can choose to do no lookup, always add records.

TIP: If you want to update existing records and add new records, define a Lookup field. MetaServer will automatically add a new record if the Lookup does not match any existing records.

03 Export to Database - Setup - Field Mapping

Press the Field Mapping tab to open its setup window. Here, you can map your database fields.

01 - Database field: this column shows all existing database fields in the currently connected database table.

02 - Type: this column shows what the database field type is. Strings can almost contain any value.  But DateTime, Integer and Decimal fields can only be updated with very specific data. For example, trying to update a Decimal field with a value containing letters will return an error.

03 - Value: in the value column, you can enter  fixed values or use the drop-down arrow to compose the value using MetaServer fields and metadata for each database field.

04 - Date format: the “Date format” is relevant when you map a DateTime field type.

DateTime type fields will only accept valid dates and times in a consistent format. It’s very important to enter the correct input DateTime format as it was extracted by MetaServer. This way, the Export to Database action can automatically convert the DateTime values and correctly update the database.

For example:
You want to export the extracted Inspection Date that has a “MM/DD/YYYY” format to a Date type field in a Microsoft SQL database.
You specify the following input format in the Date format input field:
{ Date, MM }/{ Date, DD }/{ Date, YYYY }

Result: knowing the input format, the database export action will automatically convert a date like 07/04/2018 to a database compliant DateTime format like: “2018-07-04 00:00:00”.

05 - How to update a field with the location of the PDF File: if you also want to update a field with the path to the exported PDF file, use an Export to Folder action followed by an Export to Database.

Map the export path with any of your fields in the DB. Then, use the { Export File Full Path and Name } variable as the value to update your field with the path to the exported PDF file.

You can have multiple Export to Folder actions in your workflow that each will update the export paths, and every one of them can be followed by their own Export to Database action.

TIP: MS-Access features a Hyperlink field type.  We use it for the “Exported File Link” in our sample MS-Access Statistics table. With this type of field, you can generate hyperlinks that directly open the exported PDF file.

The value you need to pass to the field has a simple syntax: LINK_NAME#PATH

In our case, we pass it the following value: Open PDF#{ Export File Full Path and Name }

TIP: you can copy the current settings and paste them in another setup window of the same type. Do this by pressing the Settings button in the bottom left of the Setup window and by selecting Copy. Then open another setup window of the same type and select Paste.

04 Export to Database - Result Example

After export in our CB - STATISTICS workflow, the Microsoft Access database located in C:\CaptureBites\DB\CaptureBitesDatabase.mdb has been updated with new records:

Before export

After export