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.

Spreadsheet software

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.

Example spreadsheets

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.

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 typeFirst nameLast nameEmail
# sign-ups from fair March 19, 2019
SupporterJacobKingjacob-king38@ddje.uk

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).

Column headings

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.

Fig 1: use the captions alongside fields in a member record as a guide for the column headings in an imported spreadsheet

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.

Type

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.

Names

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):

CalledSalutationFirst nameLast nameFirst nameLast name
J Doe & J BullJane & JohnJaneDoeJohnBull
Prof. R & K FranklinProf & Mr FranklinProf. RosalindFranklinKeithFranklin
Ś. ŻytomirŚwiętosławŚwiętosław Żytomir
Fig 2: multiple first and last names for multiple individuals

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):

NameName
Ms Jane DoeJohn Bull
Świętosław Żytomir
Fig 3: Name, repeated for each individual will work out First name, Last name, Called and Salutation if omitted

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

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:

NameExpiry date
Jocelyn BellJan 12th 2021
Świętosław Żytomirtomorrow
Fig 4: any sensible date format accepted, including relative dates.

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):

NameLists
Jocelyn BellPhotographers
Jane Doe
Świętosław ŻytomirPhotographers; Committee members
Fig 5: subscribing members to lists on import

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:

NameList: PhotographersList: Committee members
Jocelyn Bell10
Jane Doe00
Świętosław Żytomir11
Fig 6: Subscribing to lists using “tick box” values

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:

NameListEvidence listEvidence DateEvidence
Jocelyn BellPhotographersPhotographers1 Jan 2019 10:37ticked box on membership form
Jane Doe
Świętosław ŻytomirPhotographersPhotographers2 Jan 2019email from member
Fig 7: list subscription evidence

Currency

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.

Child consent

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.

Custom fields

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.

Membership numbers

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.

Importing

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).
Fig 8: Sharing a Google Sheet so anyone with the link can view it

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.

Fig 9: starting an import

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).

Fig 10: clicking More info/fix this gives more information about the problem and in most cases an opportunity to fix it interactively

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).

Fig 11: when there are interactive changes, you can manage these with the links provided (1 – 3)
Fig 12: List amendments lets you remove pending amendments one-by-one

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).

Fig 13: actually do the import when all problems resolved

Reference guide

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.

Introductory fields

CaptionAlternative captionsNotesRequirementsExample data
Typeone of new, current, or contact which determines the overall record type (manifested in the background colour of the record, and determines what you can do with it)Requirednew
Membership numberContact numberUsually omitted so that Cameo allocates a new one for you1234
CalledCalled as on envelope,
Envelope,
Called envelope
Name as it might appear on a letter or envelopeDerived from Name/Last name if omittedA & R Franklin
SalutationSalutation dear,
Dear
Name as it might appear at the start of a letter, as in “Dear…”Derived from Name/Last name if omittedAntonia & Rosalind
Membership typeMember typeone of the membership types defined in Organisation settingsrequired for type new or currentHousehold
Contact typeone of the contact types defined in Organisation settingsrequired for type contactFollower
SourceWhere the member/contact was acquiredRecruitment fair May 2019
Group URLWebsite of group memberNew/current onlyhttps://example.com

Contact related

CaptionAlternative captionsNotesRequirementsExample data
Contact awaitedAwaited,
Welcome,
Contact welcome,
Welcome awaited
Whether or not the contact should receive a welcome emailContacts only. If omitted, the contact type category determines this so that Supporter will have this turned on and everyone else off.Yes
AreaContact area,
Division,
Ward,
Constituency,
Ward/division,
Division/ward,
Ward etc
Ward/division etc,
Unitary ward,
Unitary authority ward
For a political representative, the area they representRepresentatives Contacts only. They are usually imported automatically so it would be rare to include this. Must match the exact name as shown in MapIt, including case and things like “&” vs “and”. Names of wards etc are available in Politics in Organisation settings, when enabled.South Cambridgeshire
BodyContact body,
Organisation,
Organization,
Company,
Authority,
Local authority
Council,
Body (organisation),
Body (organization)
The organisation to which the contact belongsContacts onlyCambridgeshire Constabulary
DiscountContact discountWhether a retail contact offers members a discountContacts onlyYes
Discount detailsContact discount detailsThe details of any discountContacts only10% off everything on production of membership card
PartyContact party,
Political party
For a political representative, the party they belong toRepresentative Contacts only, where enabledLabour
Term startContact term start,
Electoral term start,
Term start date
For a political representative, the date on which their elected term startsRepresentative Contacts only, where enabled1 Jan 2020
Term endContact term end,
Electoral term end,
Term end date
Ditto, endsRepresentative Contacts only, where enabled31 Dec 2020
TitleContact title,
Job,
Job title,
Role,
Rôle,
Position
Job title of contactContacts onlySenior engineer
Contact UIDA unique identifier for a contactContacts only. Only usually included automaticallycambs.police.uk/1234

Individuals

Repeat these columns for multiple individuals in a membership record.

CaptionAlternative captionsNotesRequirementsExample data
First nameForenameAn individual’s first name (possibly including, or only, a title such as “Prof.”).Repeat column for multiple individuals. If omitted and the full name is given, this will be deduced from that.Rosalind
Last nameName,
Surname
An individual’s last name.Repeat column for multiple individuals. If the whole name is put here, and Called, Salutation and First Name are not given, this will be used to work out all of those automatically from this.Franklin
Name(same as Last name)An individual’s full name.Repeat column for multiple individuals. If the whole name is put here, and Called, Salutation and First Name and Last Name are not given, this will be used to work out all of those automatically from this.Prof. Rosalind Franklin
EmailEmail addressAn individual’s email addressRepeat column for multiple individuals.
Personal phoneMobile,
Phone (mobile),
Phone (work),
Mobile/Work,
Work/Mobile
An individual’s phone number (see also land line number below)Repeat column for multiple individuals.
ListsList,
Manual lists
mlsin,
The manual list or lists to which an individual is subscribed on import. Separate multiple list names with commas or semi-colonsRepeat column for multiple individuals. See section above about lists.Photographers; Committee members
List:List nameThe individual will be subscribed to the given manual list if the content is 1, True, Yes, Y or On.Repeat column for multiple individuals and as many as needed for each list for each individual. See section above about lists.1
Opt-outsOpt out,
mlsout
The automatic list or lists from which an individual will be opted-out on import. Separate multiple list names with commas or semi-colons.Newsletters
Evidence listList evidence,
List for evidence
Name of a list. For GDPR: see discussion of list subscriptionsRepeat column for multiple individuals and for multiple evidence. Should always come in threes with Evidence date and EvidenceNewsletters
Evidence dateEvidence onDate evidence supplied. For GDPR: see discussion of list subscriptions. Can include a time.Repeat column for multiple individuals and for multiple evidence. Should always come in threes with Evidence date and Evidence1 Jan 2020 10:40
EvidenceText of evidence. For GDPR: see discussion of list subscriptionsRepeat column for multiple individuals and for multiple evidence. Should always come in threes with Evidence date and EvidenceTicked box on join form
Child consentChild data processing consentFor GDPR, explicitly indicating parental permission for a child to have their data processed. One of awaited, permitted, attached. See discussion of child consent aboveRepeat column for multiple individuals. See discussion of child consent aboveawaited
Custom fields for individualsAdditional fields specific to installation by caption as shown in UIRepeat column for multiple individuals

Membership dates

CaptionAlternative captionsNotesRequirementsExample data
Expiry dateWhen a membership expires nextNew/current only1 Jan 2020
JoinedAdded,
Added on,
Joined on,
Added on date,
Joined on date,
Date joined,
Date added
The date a member joined or a contact was added1 Jan 2020
AKAA reference to another membership record by membership number (the same person in different rôles)3456

Postal address-related

CaptionAlternative captionsNotesRequirementsExample data
Address 1Address first,
Address first line,
Address line 1
First line of postal address (usually house number and street)44 High Street
Address 2Address second,
Address second line,
Address line 2
Second line of postal address (often omitted, usually locality)Fulbourn
CityTown,
City/town,
Address 3,
Address city,
Address town
Post town/city of the postal addressCambridge
PostcodePostal code,
Zip code,
Address 4,
Address Line 4,
Address postcode,
Address postal code,
Address zip code
Must match the postcode format for the relevant countryCB21 1DD
CountyDistrict,
Address county
Only in Cameo versions where county offeredCambridgeshire
CountryAddress countryOnly in Cameo versions where country offeredUK
PhonePhone (land line),
Phone (land),
Phone (home),
Home
Land line
Home phone number01223 456789

Delivery-related

CaptionAlternative captionsNotesRequirementsExample data
Delivery methodFor publications, how they should be delivered, one of Web, Post, Hand, UnknownWeb
Delivery roundRoundA round name/number for hand delivery as defined in Organisation SettingsIf omitted, will be determined automatically from the map if possible. Use MAIL to override hand delivery.1
Delivery copiesCopiesFor publications, how many physical copies they should recive. 0 will suppress any. If omitted, depends on the delivery method (Post/Hand). If not zero (explicitly or implied), a postal complate address is required.0
Delivery noteDelivery note for labelA note of up to 30 characters to include on maling labels (specifically in response to the {show: label info} template substitution)Leave by back gate
Date last renewedDate renewed,
Date last manually renewed,
Date last manually renewed on,
Date renewed on,
Last renewed on,
Last renewed,
Renewed,
Renewed date
When a renewal was last processedNew/current only1 Jan 2020

Custom fields

See also custom fields for individuals, above.

CaptionAlternative captionsNotesRequirementsExample data
Custom fieldsAdditional fields specific to installation by caption as shown in UI

Financial

CaptionAlternative captionsNotesRequirementsExample data
Payment enquiryEnquiry,
Anomaly
Payment has a problemUnusual to set this on importAwaiting reply
Date last paidDate claimed last paid,
Date claimed paid,
Date claimed,
Last paid,
Paid on,
Paid
When membership fees last paid. “Claimed” because some payments require action such as a bank transfer being done, which may be forgotten.New/current only1 Jan 2020
Payment methodExpected payment methodOne of the payment methods defined in Organisation settings.Required, for New/current onlyCredit/debit card
Payment by invoiceBy invoice,
Invoice,
Invoiced
If the member requires an invoice to pay their feesNew/current onlyYes
DonationDonation amount,
Amount donation
Amount of any donation received (so that reconciliation can take this into account in knowing how much to expect next time)20
OverpaymentOverpayment amount,
Amount overpayment,
Extra amount,
Amount extra
Any overpayment (or – negative –underpayment), ditto1
Direct debit amountAmount DD,
DD Amount,
DD,
Amount direct debit
The actual amount the member’s direct debit should collectUsually omitted, set automatically from GoCardless22.5
To accountPayment expected to a/c,
Payment expected to account,
account
The bank account fees will be paid into (or were last time) – using the short form of account rather than the full account name, as in Organisation SettingsRequired, for New/current only, but if omitted is worked out from Payment Method by looking at the payment methods allowed on each bank account.C
Date payment requestedDate auto payment set up,
Date automatic payment set up,
Date payment set up,
Auto payment set up,
Auto payment,
Automatic payment set up,
Auto payment date,
Date auto payment,
Date direct debit,
Datestandingorder,
Date DD,
Date SO
The date on which an autmated payment like a standing order was supposed to have been made, so it can be cross checked later if it does not arriveNew/current only1 Jan 2020
Gift aid declaration byGift aid declaration by,
Gift aid by,
GA by,
Taxpayer
The name of the person making a gift aid declarationIf this is given, Gift aid declaration on must also be givenMs R Franklin
Gift aid declaration onGift aid on,
GA on,
Gift aid declaration date,
Gift aid date,
GA date,
Gift aid declaration date
The date on which a gift aid declaration was madeIf this is given, Gift aid declaration by must also be given1 Jan 2020
Gift aid exceptionGift aid exceptions,
Gift aid declaration exceptions,
Gift aid except,
Gift aid declaration except,
Exceptions,
Except
Allows specified tax years to be omitted from gift aid claims (if they weren’t paying enough tax in those years for example)See note alongside this field in Cameo UI for format2016-
Bank referenceBank refe.g. bank account number, to help match with bank statement12345678

Miscellaneous

CaptionAlternative captionsNotesRequirementsExample data
NoteNotes,
Memo,
Memos
Any text about membership, changes and quirksRepeat as many as needed. May be accompanied by name and date of person making note in separate adjacent columnsLeaving the country next March
Note byNotes by,
Memo by,
Memos by,
Note added by,
Notes added by,
Memo added by,
Memos added by
Who added the preceding noteRepeat for each noteRobert
Note onNotes on,
Memo on,
Memos on,
Note added on,
Notes added on,
Memo added on,
Memos added on
When the preceding note was addedRepeat for each note1 Jan 2020
IdeasIdeas and suggestions,
Ideas & suggestions,
suggestions
Text provided by member in repsonse to a general “what should we be doing” or “can you help” box on a form