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.
