You can use a spreadsheet to import membership records for members (new and current) and contacts in the Import Members section of the New Members menu.
I recommend using Google Sheets rather than Microsoft Excel (or LibreOffice).
If you do use Excel or LibreOffice, save as XLSX and import that. This is because of a long-standing problem in Excel which makes it hard to produce a CSV file containing characters from different languages. You’d be OK with the occasional French accent, but stray into Polish names, for example, and Excel will likely get this wrong in a CSV. Also, beware of Excel’s habit of second-guessing anything that looks vaguely like a date and converting it.
Also, it is possible to share Google Sheets files directly with Cameo, which is easier for repeated attempts, avoiding manually saving files then uploading them to Cameo.
Here are four Google spreadsheets you could use as a starting point or a proforma. Make a copy (you can’t change the originals). You may well want additional columns to suit your data – for example, having only a Postcode field in the contact supporter example is not to suggest you should not include the full address.
- Example 1: A simple paying member record with a single individual ready for enrolment
- Example 2: Up to four individuals in a household membership
- Example 3: A stakeholder contact (someone at an organisation you want to keep in touch with perhaps)
- Example 4: A supporter contact (a sign-up)
Prepare your spreadsheet
Each row represents a record, except for:
- header row: you need to tell Cameo which columns correspond to which fields. The header row (usually the first row) does that.
- skip rows: if you start the first cell in a row with the # sign, or if a row is entirely blank, the row is skipped (ignored). This allows you to mark some records to not be imported or to include comments in your file for your reference. The header row can be preceded with skipped rows if you want to put a comment right at the top.
For example, the second row in this example is ignored:
|Contact type||First name||Last name|
|# sign-ups from fair March 19, 2019|
Each column in a row for a record then represents an item of data in the record.
You can ignore a whole column by preceding the column heading with # (except column A as a # there would ignore the whole header row, as above).
Cameo can’t magically guess which columns you intend to go into which fields of the Cameo membership record. You have to tell it which column is which! Do that by putting the captions with which Cameo labels the boxes in a membership or contact record in the user-interface as headings in the header row of the spreadsheet (Fig 1).
For example, put Membership type (Fig 1, as outlined in red) in the heading of the column to import that field from that column. Look at an existing record in Cameo as an example of what columns you might want to provide for different membership types.
Where a caption in the user-interface has a short explanation in parentheses, as in Called (as on envelope), you can omit or include that as you wish. For example, it is sufficient to put just Called as the header for this column.
Punctuation, case and spaces are ignored, so you can also write called as on envelope or even calledasonenvelope and similar.
Likewise it doesn’t matter if you write Membership type: (as shown in Cameo, with a colon, perhaps copied and pasted from there) as the colon will be ignored.
A complete list of column headings recognised by the import is in the reference guide at the end of this article.
If you have a header row already, perhaps from exporting from another database, you might find it helpful to keep that on the second row for reference, and comment it out.
There’s an important field in a column headed Type (which is not the same as Membership type or Contact type). Type should be one of new, current or contact (there is also old for old, expired members, shown in red, but you can’t import those).
This field is what makes new member records have a yellow background, current members grey and contacts usually mauve (though that can be customised), and more importantly to behave differently within Cameo. Type also determines which other fields a record should have (so if you’re importing contacts, look at an existing contact record for the column headings).
Quite often, Type will be the same for all rows. If you omit the Type column, you can say what type each record is interactively after uploading your spreadsheet (see below).
Mostly fields need not be included if they aren’t relevant. But a record is not much use without a name (or at least the name of a body or organisation in the case of some contacts), and at least one means of communication: email or postal address.
Contacts must have a Contact Type and members a Membership type. Paying members (new and current) must also have a Payment method.
There are four name-like fields in a Cameo membership record:
- First name, a forename like “Jane” in “Jane Doe”, but also possibly a title or title and name, so “Ms” in “Ms Doe” or “Ms Jane” as in “Ms Jane Doe”
- Last name, or surname, “Doe” in these examples.
- Called, which is how you might address someone on an envelope or label,
- Salutation, which is how you might introduce them at the start of a letter, as in “Dear Jane…”,
Depending on the record and membership type, a record may have more than one individual (as in a household membership), so you may have several first and last names: just repeat First Name and Last name headings (no numbers or anything like that). Leave later entries blank where a record has fewer individuals. For example (Fig 2):
|Called||Salutation||First name||Last name||First name||Last name|
|J Doe & J Bull||Jane & John||Jane||Doe||John||Bull|
|Prof. R & K Franklin||Prof & Mr Franklin||Prof. Rosalind||Franklin||Keith||Franklin|
You can also just have columns labelled Name (one for each individual) in which you can put a full name. Cameo will divide this up for you and provide a sensible Called and Salutation. (Actually, using just one of First Name or Last Name will do the same). For example (Fig 3):
|Ms Jane Doe||John Bull|
Row 2 gives you a record with two individuals and row 3 just one. Row 2 would set Called to Ms J Doe & J Bull, Salutation to Jane & John, First name of first individual to Ms Jane and second to John, and Last name of first individual to Doe and second to Bull. There are a few cases where this is ambiguous which you may need to sort out later. For example, in Andrew Bonar Law, is Last name Bonar Law, or just Law? Cameo will tend to use the shorter Last name, so that names like Jean Luc Goddard will work.
Dates are needed in a few places, such as Expiry date. As elsewhere in Cameo, more or less any sensible format is accepted in a spreadsheet import, including relative dates (Fig 4). We recommend avoiding the form 3/4/2019 as this is ambiguous (European or American? It depends on how your version of Cameo is set up). For example:
|Jocelyn Bell||Jan 12th 2021|
List subscriptions, opt-outs and evidence
Lists are a way of grouping people together, either automatically according to some rule (and from which they can be opted out), or manually by being subscribed to them explicitly. Individuals in a multi-person membership are subscribed separately.
To subscribe people to a manual list while importing, put the name of the list in a column headed List or Lists. To subscribe them to more than one, include more than one name, separated by commas or semi-colons. For example (Jane Doe isn’t subscribed to any):
|Świętosław Żytomir||Photographers; Committee members|
To pre-set opt-outs from automatic lists, do likewise with a column headed Opt-outs.
There is an alternative format for this: sometimes, when importing from another database, you may have a set of “flags” which say whether a member has some property or other. In the original database, you might have a tick box for Committee member and another for Photographer. The way this appears in an exported spreadsheet is as a series of columns, with 1, True, Y, Yes or On, if ticked or empty, 0, False, N, No or Off otherwise. The way to do this in Cameo is to subscribe them to corresponding lists. To make this easier for importing with spreadsheets organised like this you can add a column (for each individual) for each list like this, which is exactly equivalent to the example above:
|Name||List: Photographers||List: Committee members|
You may also want to add evidence of list subscription consent for GDPR purposes. This isn’t visible directly in membership records, but shows up when you look at the subscriptions section.
Each piece of evidence has three parts:
- Evidence list: name of list it applies to (this will always be a manual list). You need this irrespective of whether you are subscribing them at the same time in a Lists column.
- Evidence date: date (and optionally time) evidence was collected
- Evidence: evidence text (such as “ticked box on form”)
(there’s actually a fourth, which is a scanned file attachment, for physical evidence, but you can’t import that with a spreadsheet).
So for each piece of evidence, you’ll need three columns, and for more than one individual you’ll need more than one set. If you have one list this is straightforward; if you have many the principle is the same, but you may end up with rather a lot of columns. Considering just one list with one individual, for example:
|Name||List||Evidence list||Evidence Date||Evidence|
|Jocelyn Bell||Photographers||Photographers||1 Jan 2019 10:37||ticked box on membership form|
|Świętosław Żytomir||Photographers||Photographers||2 Jan 2019||email from member|
Currency fields (custom or built-in) can be given with or without the currency symbol. Cameo only understands one currency though, so only the currency symbol for the particular installation is allowed.
Welcome awaited (contacts)
This field shows up as a “starburst” at the top right of a new contact record It indicates the contact should receive a welcome message, but hasn’t yet been sent one.
If you don’t include this field, it will be set automatically for contact types which have been put in the Supporter category and not otherwise. Explicitly setting this on or off in your import means you can require this for other contacts, or turn it off for supporters (for example when importing from another database, they have presumably been included for a while).
The column should be headed Welcome or Welcome awaited and have a yes/no value: (case-insensitive): 1, True, Y, Yes, or On; or 0, False, N, No or Off. Note that for this field, leaving it blank is different from No etc, as described above, because the default isn’t always No.
Another field, that you only see when you visit the Membership status section in Member tasks, is Child consent. This records the status of data processing consent for under 16s, who require a higher standard of protection under GDPR.
You can import this field in a column headed Child consent, applied to individuals who are children, and it should have one of the following values in the spreadsheet cell (left blank for those who aren’t children):
- awaited: a child applied for membership and requires confirmation from a parent or guardian that it is permitted to process their data. (When this is received it will change to attached, and such members should not be enrolled until this is received)
- permitted: an adult applied for membership for their family and ticked the box to confirm it was ok to process their children’s data.
- attached: an attachment was provided which confirmed a child-only membership.
If you have any custom fields, you can import the values for them just like any other field. Some custom fields can apply to each individual, so they can be repeated just like Email, others just once for the whole record.
Use the caption for the column heading as it appears in a membership record (just like any other field) or in the Custom Fields section in Organisation Settings.
Some custom fields let you select more than one value from a set. To import these, put a list of the values required in the spreadsheet cell, separated with commas or semi-colons.
Some custom fields are tick boxes. To import these, you can use any of the following for the ticked (case-insensitive): 1, True, Y, Yes, or On, and for not ticked: 0, False, N, No, Off or just left blank.
You can include an explicit integer Membership number in each record. More usually, you will omit it and leave Cameo to allocate one for you.
if you do include Membership number, it must, obviously, not already be used by an existing membership, and it must also not be available for allocation to a new membership in the future. That means it must be smaller than the value in the Organisation Details section in Organisation settings. You can allocate the numbers you need by increasing this number, thereby reserving all the numbers in between.
If you omit Membership number, one will be allocated automatically. The final import may take longer for a large number of records when doing this.
qrcode (a unique and non-sequential record identifier field) is always allocated automatically for current and contact and should not be included in your spreadsheet.
Once you have a spreadsheet, import it in the Import members section in New members.
Do one of:
- Share a Google Sheets document so that anyone who knows the link has access (Fig 8), then paste the link into the box provided (Fig 9: 2). Once you have done this you can re-try the import from the same file with a single button click. Links are not guessable, so this is pretty secure.
- If you have more than one sheet in your Google Sheets document and you want to import any sheet other than the first, copy and paste the URL from the address bar, not the sharing link Google provides, as only the URL identifies a particular sheet.
- Save the spreadsheet as a XLSX file from your spreadsheet app, such as Microsoft Excel or LibreOffice Calc, and then select or drag and drop the XLSX file for upload (Fig 9: 1).
- Save as CSV and upload that if XLSX is not available.
- If you have manually prepared the CSV in a text editor (not Excel, Google Sheets etc), you can also copy the content and paste it into the box provided (Fig 9: 3).
On upload, the content will be checked;
- Problems are shown in red (for example, if you omit the Type column; Fig 9: 4).
- Places where the data has been changed are shown in yellow (Fig 11: 4; for example, splitting Name up into separate fields, or correcting the case of a list name).
Problems have a More info/fix this link alongside (Fig 9: 4). Clicking this gives a longer explanation of the problem and what to do about it.
In many cases it is also possible to apply a fix interactively. For example, if you don’t provide a Payment method column in your spreadsheet for paying members, you can give a value for this to apply to all the records, or just one (Fig 10).
These interactive changes are retained as long as you remain in the Import members section unless you remove them manually (with clear amendments – Fig 11: 3 – or individually – Fig 12: 1). You can review changes, remove them individually (Fig 11: 2, Fig 12: 1) or all of them (Fig 11: 3), and re-do the checks with the changes applied either by uploading again (or fetching again from Google Sheets) or on the same spreadsheet with no need to upload the file again (Fig 11: 1).
Once the data is all sound, an Import now button is offered to actually create the new records in the database (as a combination of the spreadsheet and any interactive changes).
The table below shows the columns you can have, how you can name them, and any limitations, grouped by function.
Case, spaces and extraneous punctuation is ignored in all captions.
Repeat these columns for multiple individuals in a membership record.
See also custom fields for individuals, above.