Format of an Import File

Two text file layouts are supported:

In either, blank lines and lines starting with a ; are ignored.

Header Row

PRESTO imports those columns whose first row contains:

When the =xxx notation is used for a column that does not yet exist, the new column is given these properties: Text data type, Optional, Publicly Editable.

Other columns are not imported. This is to allow you to import from a "wide" spreadsheet without having to remove the columns that are not applicable to your PRESTO database.

Formats of the Columns PRESTO Knows About

This table describes the recognized column names and the rules that values in such columns must conform to. Note that firstname and lastname are the only required columns. However, although a blank column value is always accepted, it may be reported as an issue.

Description Names It Can Have Formatting Rules
First name info first, firstname, fname, first name A list of first names (see note 1).
Person's middle name middle, mi, mname, middle name A middle name or middle initial (the period is optional)
Last name info last, lastname, lname, last name A list of last names (see note 1)
Alternate format:
Full name in one column
name 1-part first name (required), 1-part middle name/initial (optional), and 1-part last name (required). Thus "John" and "John J Smith III" are illegal, and "John Smith" and "John J Smith" are legal. When this column is not empty, a line's other name columns are ignored.
Email address info email, e-mail, email address, e-mail address, e-mail 1 - value A list of email addresses (see note 1)
A phone number
(up to 20 characters)
phone or phone1 May be any text, but a full phone number is preferred for the number part of the data — 111-222-3333 for example.
A second number
(up to 40 characters)
phone2 As for the first phone number. (The extra room is sometimes used for an explanatory note, e.g. 'Evenings only 123-456-7890').
Alternate format:
A column containing up to 2 phone numbers
phones A comma-separated list of phone numbers.
Person's home address address Street, city, state, and zipcode must all be present. Street and city, and city and state must be separated by a comma. State and zipcode must be separated by spaces. If an apartment number or the like follows Street, it may be preceded by a comma or spaces.
Alternate format:
A separate column for each address field
These are: street, city or town, state, zipcode or zip If an apartment number or the like exists, it should be included at the end of the Street column. State should be 2 characters.
Representing representing What this person represents, such as a couple, vendor, or team.
Roles roles A comma-separated list of roles. The first should be the person's current job. Others might be jobs the person is eligible for.
Expiration date, for membership or the like expires A date
When person was registered in the database registered A date (see note 2)
Alternate format:
Expires from Registered
regexp1 A date. Registered is set to it. If there is no Expires column, Expires will be set to one year after the Registered date.
Person's privilege level privs One of: Self Only, See All Signups, or Observer. All other values report an error. Also the setting is ignored if applied to someone with Admin or Agent privileges.
Person's unique identifier id When a line includes an id value (which can only come doing an Export People), the values on that line update the person with that ID. In particular, a new name on the line causes the person to be renamed (as opposed to registering a new person).
Import action import If you include this pseudo-column, its value on a line determines whether the person is processed, skipped, or deleted (see note 3).

Note 1a : Full Name of a Person

Logically a person's full name is one field. When supplied in multiple columns, it is simply the concatenation of first, middle, and last — with middle name set to blank if that column is omitted.

Note 1b: Multi-person Rows

A single import row can identify multiple people. That is, it will lead to multiple people being registered or updated. The default separator for a multi-value column is a forward-slash (/). If your file uses a different separator in some column, prepend it to the column name between square brackets. The columns that support this are First Name, Last Name, and Email Address. However the number of items in the Last Name and Email Address columns must equal the number of items in the First Name column, or be 1.

For example:

first          [&]Last      [,]EMAIL
Jack/Jill      Crown
Jane/John      Smith&Doe,

Creates these registrations:

Note 2: Default Registration Date

If a row identifies a new person and does not contain a Registered value, it will be set from the person's Expires info: same day, same month, and latest year that does not put Registered in the future. If there is also no Expires info, Registered will be set to the date of the import.

Note 3: The Import-Action Pseudo-Column

When this column exists, its value for a line determines how the line is processed. If its value is:

The Import Result page shows a deleted person by blacking out most of the row. By the way, if you import a bunch of new people and then want to undo this, you just need to add an import=delete column to line 1 and repeat the Import.

Group-specific Columns

A group-specific column name may contain letters, numbers, spaces, and dashes; and be up to 20 characters in length, but there are no restrictions on the content or length of the values in such columns. There can be any number of group-specific columns, but obviously the readability of the Register, Update, and Search Results pages will degrade as the total number of columns increases.

Data Type property

A group-specific column's data type determines how it is presented and checked when a Register Person or Update Person command is done.

The following data types are supported:

The following value checks are done when a user fills out the field:

As the Import and Export file format is line-oriented, special provision is made for the multi-line column type (*l). To import a line break (the LINEFEED character), you must replace it with exactly \LF\ — that is, backslash capital-L capital-F backslash. Conversely this is what an Export will generate when it encounters a multi-line field containing a LINEFEED.

Required-ness property

The 2nd letter can be O or R — meaning Optional or Required. If R is specified, the user must provide a value for the field when doing a Register Person or Update Person. Additionally when the registration form is displayed, the field name is shown with an asterisk (*) after it.

Visibility property

The 3nd letter should be E or V — meaning Publicly Editable or View-only. If E (or P) is specified, it can be entered by anyone. If V (or A) is specified, the field is grayed out when a self-Register or self-Update is done.

Interaction with Customize Registration Form

If you import an already-existing column using the *xxx notation, the data type letter must match the date type of the existing column. But you can override the old required-ness and visibility.

If you import new columns, they are appended to the end of the registration form. This means they will become admin-only fields. If any should not be, use Customize Registration Form after the Import to achieve this.

Field Values

The supplied (or inherited from line 1) value for each field on a line is error-checked (e.g. required fields must be non-blank), and placed into the new or updated database record. However if an import file lacks a column for some field, that field's non-blank values are left unchanged and not rechecked.

If no value is given for a field — and the line is a Create or the field's current value is blank, then the field's default value is used. The default of a menu field is its form-defined default menu item if any. All other fields default to blank.

Note that the value you supply or inherit can be the admin enter-later value ???.

Composite Columns

Sometimes your columns are not quite in a format that PRESTO supports. Composite columns help with this situation.

This feature is triggered by placing name=expression, instead of just name, in the first row of a column. Doing this causes the original values in the column to be replaced by the values the expression computes to.

Format of Composite Column's 1st Row

The expression is a sequence of variable terms and arbitrary text. The value of the expression is simply the values of the terms merged with the text. Each variable value is derived from data in the current row of the import file. There are three kinds of variable terms:

Note: including an imported column's name in an expression does not cause an error. Instead the name itself is what appears in the imported data.

Simple Expressions and Column Names

Suppose you had a Phone column (containing a phone number without an area code) and an AreaCode column. Since PRESTO expects the whole phone number in one column (e.g. 123-456-7890), you have a problem. To deal with this, put Phone=AreaCode-this in the 1st row of the phone column. Then if row 2 contained 456-7890 and 123 in these columns, and row 3 contained 222-3333 and 444; then 123-456-7890 and 444-222-3333 would be imported for the phone column in those 2 rows.

Because named columns must be to the right of the current column, you would have to do a little extra work if the AreaCode column were first in the above example. You would have to rename the Phone column (eg. to PH) and put phone=this-PH in the 1st row of the AreaCode column.

Because expressions mix together text and column names, it improves readability to make column names stand out. For example, one good policy would be to name columns used in expressions all in uppercase letters and numbers. Whatever you do, note that the case of a column name in an expression must exactly match the actual name of the column. So PH would work in the above expression, but Ph would not.

Conditional Terms

Sometimes part of the job is simply making things more readable. For example, suppose you had a Street column and then an Apt# column. Suppose further the Apt# column was sometimes a number and sometimes empty. Therefore you have 2 problems. You want the apt# info to be included in the Street column, but you want Apt n or nothing to be included -- never just Apt. To solve these problems:

If Street in some row was 123 Sesame St, this would lead to either: 123 Sesame St or 123 Sesame St Apt 3 being imported, depending on whether that row's Apt# column was empty or was 3.

The (name.function\expr1\expr2) term is called a conditional term. It works as follows: