Skip to main content
Mitratech Success Center

Internal Database Type

Process Director allows you to specify the database type for you Data Connections. The Internal Database type will connect to the database which Process Director is installed. No other options are needed when using the Internal Database type.

image

Creating a Database Using Microsoft Excel

Process Director can use a Microsoft Excel spreadsheet as a data source, enabling you to check out and edit the spreadsheet whenever needed. Process Director can re-import the data every time you check the spreadsheet back in, to ensure the data always reflects the current values in the spreadsheet.

Creating the Structure

To create the structure of an internal database table, one must use MS Excel, following these steps:

  • The names of each of the cells in the top row of the Excel sheet will become the names for the database fields.
    image
  • Each row below the top is a place for data to be entered. Enter the proper information under the proper columns.
    image
  • Each sheet acts as a different table in the database. Each table can have different field names and different data.

Process Director will not transform the Excel sheet under the following conditions:

  • Process Director will ignore default sheet names, like “Sheet1”, “Sheet2”, or any sheet whose name matches the Sheet# pattern.
  • You cannot use spaces in sheet names.
  • Process Director will not transform any sheets whose A1 cell is empty.
    image
    image

Creating a Data Source

The database created as an Excel file will be stored on the server in an object called a Data Source. You must create a data source before you upload the Excel sheet so that you have something to store the database in.

Once you’re in the folder you want to create the data source in, go to the “Create New” dropdown menu and select “Data Source.”

image

Once you select the data source, name the data source and click “OK.”

image

After clicking “OK”, configuration options will appear for the data source. Make sure the “Database Type” dropdown menu is set to “Internal Database”, and then click “OK.”

image

The data source is now created.

Importing Database from Excel File

Now that the Data Source is created, you need to import data into it from the Excel file you made. Go to the Content List. Under the “Create New” dropdown menu, select “Document / File”

image

On the next page, click the “Browse” button and locate your Excel file. Double click it, and then click “Upload” to upload the file to your partition.

The final step is to take the data from the uploaded Excel file and put it into the data source. After the Excel file is uploaded, you should be presented with another page. Click on the “Import Database” tab. Click the Pick List […] button to select the data source you created earlier. Click “Import Tables Now.”

image

Option

Explanation

Destination Table Prefix

This is the text string that will be pre-pended to your existing Excel sheet names in the database. So if your sheet name was “February_Users” in Excel, when you looked for it in the database, it would be named “Excel_February_Users”

Drop database tables before import

To “DROP” a table is to delete it completely. The difference between “dropping” a table and the “Clear database rows” checkbox is that if you only clear the rows, the format of the information in existing fields (columns) is kept. In other words, in Excel, if you had a date field called “Married” and imported it to the database with date information, Process Director expects date formatted information to be entered in that field. If you opened Excel and altered the “Married” column to be a Boolean (True/False) value, and did not drop the tables when you imported, you would generated an error.

Create database tables if needed

If you have a previously imported Excel spreadsheet and add a new sheet to it in Excel, Process Director will not import the new sheet as a new table unless this checkbox is checked.

Clear database rows in tables before inserting

This is the difference between recreating a table’s information, and appending the information in Excel to the existing table in Process Director. Appending of tables is usually only done by database administrators when building or editing informational databases. If you want the table in PD to look just like what you saw in Excel, select this checkbox.

Automatically import this Excel file after every check-in

After you have added an Excel spreadsheet to your Process Director instance, you will need to “check out and edit” the file in order to open it and make changes. After your changes are made, you select “upload and check in” and this checkbox will automatically recreate or append the information in your PD database without you having to re-import the file.

Click “OK” to create the database.

Transform Excel Sheet to a Database Table

This allows you to use a Microsoft Excel sheet and transform the content to a table and data within a data source specified in Process Director. This is useful when you need to add data to a database without having to use a database management tool. A form that performs these functions can be distributed from BP Logix and is also included in the sample files Process Director.

Creating the Excel File

It is important to configure your Excel file to these specifications in order for Process Director to transfer the data correctly.

  1.  Create an Excel file using Microsoft Excel.
  2. Rename the Excel sheet to the name of the table you would like to create. Please do not name your table starting with “tbl”. The form will result in error.
  3. Create column names by adding the names of the column to the first row and in each cell. If you do not specify a type, it will be determined automatically based on the value of the first record. Process Director will attempt to get the cell/column type from the Excel column, and set the DB column type accordingly. For instance, if the column header cell is formatted as a a date/time cell, Process Director will use the Datetime column type.
  4. Optionally set the data type of the column by appending ":type" to the column name, where type is the native database type (e.g. varchar) or one of the following keywords: bp_string, bp_bool, bp_decimal, bp_int or bp_datetime. The default size of bp_string is 512 characters, but you can override the string size using bp_string(size) to set the string size to a different number of characters.
    image

If you use any of the "bp_*" keywords, the data will be converted into a SQL data type, as follows:

Keyword

SQL Data Type

bp_string

Varchar(512)

bp_bool

Bit

bp_decimal

Decimal(18,4)

bp_int

Int

bp_datetime

Datetime

  1. Add the data to the corresponding rows and columns.
  2. Save your Excel file.

Your Excel file should represent a database table with columns and rows. Here is a sample of what it can look like:

image

Transfer Excel Data

  1. Load the Excel file.
  2. Go to the “Import” tab.
  3. Select a data source to import your data from Excel.
  4. Click on the Run Import button to insert the data.

Additional options allow you to:

  • Prefix the destination table name.
  • Drop (Delete) tables with the same name
  • Clear all rows in table before inserting.
  • Create table if it does not exist.
  • Was this article helpful?