Two text file layouts are supported:
If a value contains a comma, the whole value must be enclosed in quotes. A quoted area may not itself contain a quote. If this situation would apply, the TAB layout must be used.
In either, blank lines and lines starting with a ; are ignored.
PRESTO imports those columns whose first row contains:
There can be 1 to 3 letters, and they encode this column's data type, required-ness, and visibility. If the 3rd letter is omitted, it defaults to Publicly Editable. If the 2nd is omitted as well, it defaults to Optional.
This identifies a composite column. Composite columns enable you to tweak values in a column. The expression is a mini-program in effect. For more on them, click here.
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.
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:
Registration date, and Expires from it | regexp1 | A date (see note 2). If there is no Expires value on a new registration, Expires will be set to one year after Registered. |
| Person's privilege level | privs | Should be: blank, Self Only, See All Signups, or Observer. Agent, Admin, and blank cause your group's default initial privilege to be assigned for a new person, and are ignored for an existing person. 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). |
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.
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 nursery@rhyme.com Jane/John Smith&Doe smith@who.com, doe@who.com
Creates these registrations:
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.
If a row identifies an existing person, any supplied Registered value is ignored. In other words, you can retain a pre-PRESTO registration date when a new person is added to the PRESTO data base, but you cannot ever make it later than when the person was added to PRESTO.
When this column exists, its value for a line determines how the line is processed. If its value is:
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.
The Import Result page color codes the background of the person's name according to the action specified. The colors are respectively: no color for No Change, pale yellow for Update, gray for Delete or Expunge, green for Create, and yellowish green for Undelete.
If an import line has an error, it is shown in a pink area below the line. Note that this does NOT affect the line's color coding. On the other hand, lines with errors are not included in the counts at the top of the results page.
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.
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.
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.
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.
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.
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 ???.
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.
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.
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.
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: