Creating a Database Using Microsoft Excel
Creating the Structure
To create the structure of an internal database table, one must use MS Excel following these steps:
1. The names of each of the cells in the top row of the Excel sheet will become the names for the database fields.
2. Each row below the top is a place for data to be entered. Enter the proper information under the proper columns.
3. Each sheet acts as a different table in the database. Each table can have different field names and different data.
One note – the default name of “Sheets” in Excel are “Sheet1”, “Sheet2”, and “Sheet3”. Process Director will not upload sheets called “Sheet#”. You must put a more descriptive name as the table name to be used inside PD.
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.”
Once you select the data source, name the data source and click “OK.”
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.”
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”
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.”
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.