Import membership records from spreadsheets

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

If your data isn’t already in a spreadsheet, I recommend using Google Sheets rather than Microsoft Excel (or LibreOffice).

If you already 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.

• 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	Email
# sign-ups from fair March 19, 2019
Supporter	Jacob	King	jacob-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.

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

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
Ś. Żytomir	Świętosław	Świętosław	Żytomir

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

Name	Name
Ms Jane Doe	John Bull
Świętosław Żytomir

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:

Name	Expiry date
Jocelyn Bell	Jan 12th 2021
Świętosław Żytomir	tomorrow

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

Name	Lists
Jocelyn Bell	Photographers
Jane Doe
Świętosław Żytomir	Photographers; Committee members

To pre-set opt-outs from automatic lists (that is, to set the automatic lists from which they are already opted-out), 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
Jocelyn Bell	1	0
Jane Doe	0	0
Świętosław Żytomir	1	1

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
Jane Doe	
Świętosław Żytomir	Photographers	Photographers	2 Jan 2019	email from member

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 with the link has access (Fig 8a, Fig 8b), 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 are using Google Workspace (formerly G-Suite), rather than the consumer version of Google Sheets your administrator may not allow you to do this, so you would not be able to use this method: you would need to download as CSV and upload to Cameo each time instead.
• 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).

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

Caption	Alternative captions	Notes	Requirements	Example data
Type		one 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)	Required	new
Membership number	Contact number		Usually omitted so that Cameo allocates a new one for you	1234
Called	Called as on envelope, Envelope, Called envelope	Name as it might appear on a letter or envelope	Derived from Name/Last name if omitted	A & R Franklin
Salutation	Salutation dear, Dear	Name as it might appear at the start of a letter, as in “Dear…”	Derived from Name/Last name if omitted	Antonia & Rosalind
Membership type	Member type	one of the membership types defined in Organisation settings	required for type new or current	Household
Contact type		one of the contact types defined in Organisation settings	required for type contact	Follower
Source		Where the member/contact was acquired		Recruitment fair May 2019
Group URL		Website of group member	New/current only	https://example.com

Contact related

Caption	Alternative captions	Notes	Requirements	Example data
Contact awaited	Awaited, Welcome, Contact welcome, Welcome awaited	Whether or not the contact should receive a welcome email	Contacts only. If omitted, the contact type category determines this so that Supporter will have this turned on and everyone else off.	Yes
Area	Contact 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 represent	Representatives 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
Body	Contact body, Organisation, Organization, Company, Authority, Local authority Council, Body (organisation), Body (organization), Parish, Parish council	The organisation to which the contact belongs	Contacts only	Cambridgeshire Constabulary
Discount	Contact discount	Whether a retail contact offers members a discount	Contacts only	Yes
Discount details	Contact discount details	The details of any discount	Contacts only	10% off everything on production of membership card
Party	Contact party, Political party	For a political representative, the party they belong to	Representative Contacts only, where enabled	Labour
Term start	Contact term start, Electoral term start, Term start date	For a political representative, the date on which their elected term starts	Representative Contacts only, where enabled	1 Jan 2020
Term end	Contact term end, Electoral term end, Term end date	Ditto, ends	Representative Contacts only, where enabled	31 Dec 2020
Title	Contact title, Job, Job title, Role, Rôle, Position	Job title of contact	Contacts only	Senior engineer
Contact UID		A unique identifier for a contact	Contacts only. Only usually included automatically	cambs.police.uk/1234

Individuals

Repeat these columns for multiple individuals in a membership record.

Caption	Alternative captions	Notes	Requirements	Example data
First name	Forename	An 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 name	Name, 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
Email	Email address	An individual’s email address	Repeat column for multiple individuals.	
Personal phone	Mobile, Phone (mobile), Phone (work), Mobile/Work, Work/Mobile	An individual’s phone number (see also land line number below)	Repeat column for multiple individuals.	
Lists	List, Manual lists mlsin,	The manual list or lists to which an individual is subscribed on import. Separate multiple list names with commas or semi-colons	Repeat column for multiple individuals. See section above about lists.	Photographers; Committee members
List:List name		The 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-outs	Opt 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 list	List evidence, List for evidence	Name of a list. For GDPR: see discussion of list subscriptions	Repeat column for multiple individuals and for multiple evidence. Should always come in threes with Evidence date and Evidence	Newsletters
Evidence date	Evidence on	Date 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 Evidence	1 Jan 2020 10:40
Evidence		Text of evidence. For GDPR: see discussion of list subscriptions	Repeat column for multiple individuals and for multiple evidence. Should always come in threes with Evidence date and Evidence	Ticked box on join form
Child consent	Child data processing consent	For GDPR, explicitly indicating parental permission for a child to have their data processed. One of awaited, permitted, attached. See discussion of child consent above	Repeat column for multiple individuals. See discussion of child consent above	awaited
Custom fields for individuals	The caption for the custom field	Additional fields specific to installation by caption as shown in UI	Repeat column for multiple individuals	
Social media, additional email addresses or other handles	Email (other) Email (work) Facebook Instagram LinkedIn Phone (mobile) Phone (work) Pinterest Website Whatsapp X	These are the services supplied as standard, but you can add your own. Include whatever you the service is called as the column heading. Note Twitter is now called X. Twitter won’t be recognised.	Repeat column for multiple individuals	@cameo anotheremail@me.com

Membership dates

Caption	Alternative captions	Notes	Requirements	Example data
Expiry date		When a membership expires next	New/current only	1 Jan 2020
Joined	Added, Added on, Joined on, Added on date, Joined on date, Date joined, Date added	The date a member joined or a contact was added		1 Jan 2020
AKA		A reference to another membership record by membership number (the same person in different rôles)		3456

Postal address-related

Caption	Alternative captions	Notes	Requirements	Example data
Address 1	Address first, Address first line, Address line 1	First line of postal address (usually house number and street)		44 High Street
Address 2	Address second, Address second line, Address line 2	Second line of postal address (often omitted, usually locality)		Fulbourn
City	Town, City/town, Address 3, Address city, Address town	Post town/city of the postal address		Cambridge
Postcode	Postal code, Zip code, Address 4, Address Line 4, Address postcode, Address postal code, Address zip code		Must match the postcode format for the relevant country	CB21 1DD
County	District, Address county		Only in Cameo versions where county offered	Cambridgeshire
Country	Address country		Only in Cameo versions where country offered	UK
Phone	Phone (land line), Phone (land), Phone (home), Home Land line	Home phone number		01223 456789

Delivery-related

Caption	Alternative captions	Notes	Requirements	Example data
Delivery method		For publications, how they should be delivered, one of Web, Post, Hand, Unknown		Web
Delivery round	Round	A round name/number for hand delivery as defined in Organisation Settings	If omitted, will be determined automatically from the map if possible. Use MAIL to override hand delivery.	1
Delivery copies	Copies	For 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 note	Delivery note for label	A 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 renewed	Date 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 processed	New/current only	1 Jan 2020

Custom fields

See also custom fields for individuals, above.

Caption	Alternative captions	Notes	Requirements	Example data
Custom fields	The caption for the custom field	Additional fields specific to installation by caption as shown in UI		

Financial

Caption	Alternative captions	Notes	Requirements	Example data
Payment enquiry	Enquiry, Anomaly	Payment has a problem	Unusual to set this on import	Awaiting reply
Date last paid	Date 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 only	1 Jan 2020
Payment method	Expected payment method	One of the payment methods defined in Organisation settings.	Required, for New/current only	Credit/debit card
Payment by invoice	By invoice, Invoice, Invoiced	If the member requires an invoice to pay their fees	New/current only	Yes
Donation	Donation 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
Overpayment	Overpayment amount, Amount overpayment, Extra amount, Amount extra	Any overpayment (or – negative –underpayment), ditto		1
Direct debit amount	Amount DD, DD Amount, DD, Amount direct debit	The actual amount the member’s direct debit should collect	Usually omitted, set automatically from GoCardless	22.5
To account	Payment 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 Settings	Required, 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 requested	Date 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 arrive	New/current only	1 Jan 2020
Gift aid declaration by	Gift aid declaration by, Gift aid by, GA by, Taxpayer	The name of the person making a gift aid declaration	If this is given, Gift aid declaration on must also be given	Ms R Franklin
Gift aid declaration on	Gift 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 made	If this is given, Gift aid declaration by must also be given	1 Jan 2020
Gift aid exception	Gift 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 format	2016-
Bank reference	Bank ref	e.g. bank account number, to help match with bank statement		12345678

Miscellaneous

Caption	Alternative captions	Notes	Requirements	Example data
Note	Notes, Memo, Memos	Any text about membership, changes and quirks	Repeat as many as needed. May be accompanied by name and date of person making note in separate adjacent columns	Leaving the country next March
Note by	Notes by, Memo by, Memos by, Note added by, Notes added by, Memo added by, Memos added by	Who added the preceding note	Repeat for each note	Robert
Note on	Notes on, Memo on, Memos on, Note added on, Notes added on, Memo added on, Memos added on	When the preceding note was added	Repeat for each note	1 Jan 2020
Ideas	Ideas 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