Importing membership records from spreadsheet files has been considerably simplified. From the outside, it doesn’t look much different, but it is now much more forgiving in its handling of spreadsheet files and doesn’t rely so much on Cameo’s internal structure. You can now also:
- import Microsoft Excel .xlsx files, and
- import directly from Google Sheets
as well as from CSV.
This article summarises the changes. Another article, Import membership records from spreadsheets, explains importing spreadsheets from scratch.
Contents
Background
Cameo’s file imports were never intended for day-to-day use. The intention was to be able to move an existing database (or spreadsheet) of members into Cameo at the beginning.
It was aimed at people who knew how to get data out of existing, raw databases, and the quirks of this kind of operation. The import was very definitely linked to how Cameo operates inside, and it showed. But the implementation and maintenance cost was low, which was important for a rarely used feature.
CSV imports were also grafted onto JSON imports. But while JSON is a well-defined, stable format, CSV is a terrible format. It has no real specification, so it means what people want it to mean. What you mean may not be what I mean, and more importantly what Microsoft Excel thinks it means. Its simplicity is what makes it at the same time so enduringly popular, and so dangerous.
There has been consistent demand to use spreadsheet import routinely, however, with fewer database-led skills. This update attempts to address at least some of that.
New import features
CSV character set detection
The character set (or encoding) of CSV files is now detected automatically. Previously, this was something you had to worry about, to the extent at least of using Google Sheets which gets this right, unlike Microsoft Excel’s CSV files; now you don’t really need to know what an encoding is.
CSV files exported from Microsoft Excel (and LibreOffice Calc) are still a problem though. This is because of a long-standing issue in both Excel and LibreOffice Calc with how they export CSV files containing accented characters from non-English languages, many currency symbols, and emoji and the like. Default settings (which are hard to change in Excel) don’t allow CSV files to properly represent many characters, and Excel doesn’t tell you it can’t! This has ruined many a database through naïve use of CSV files containing letters like ï.
Therefore, while Cameo can now work out what the characters in a CSV are, it is an Excel problem that some characters can’t be accurately included at all, so I recommend using Google Sheets (which saves CSV correctly) or .xlsx files from Excel and LibreOffice Calc.
Import direct from Google Sheets
Share your Google Sheets spreadsheet (Fig 1: 1; Fig 2) and give Cameo its URL (Fig 1: 2) and we’ll collect it automatically. When you’ve pasted in the URL once, you can import it again after making corrections with a single button click (Fig 1: 3).
If you have only one sheet in the spreadsheet or only want to import the first, you can also paste the link the Google Share box provides into Cameo. It is the same as the browser address bar except that the latter also identifies a specific sheet.
Import .xlsx files
You can now import Microsoft Excel’s native .xlsx files. Open them in the same way you would a CSV. This avoids Excel’s problems with character set encoding in CSV files.
Note that the much older .xls format is not supported.
Example proforma spreadsheets
The import reference article has links to four example Google Sheets which you could use as a starting point for your import.
Column headings are familiar captions
Cameo can’t guess what data you want to go where, so you still tell Cameo which columns of your spreadsheet should go in which fields of each imported membership record using column headings in the first row.
However, these no longer need be internal names for fields in the membership records (though they still can be if you want). Instead, you can use the captions that appear alongside the fields in the user interface (Fig 3, examples outlined), or other natural variations. Errors on import are also now worded in terms of caption rather than internal field name equivalent.
| Membership type | First name | Last name | |
| individual | Aimée | Simpson | amysimpson34@ddje.uk |
| individual | Callum | Rice | callum_rice60@ddje.uk |
| individual | Sarah | Harper | sarah_harper67@ddje.uk |
You can also now use just Name for someone’s name, and Cameo will sort out Called, Salutation, and individuals’ First name and Last name.
Case, spaces and punctuation ignored
It doesn’t matter whether you put Membership type, membership type, membershiptype or membership-type, for example, in the column headings.
Also, in fields where the value is one of a known set of possible values (Membership type, for example), then the spreadsheet cell content is also case-insensitive (notice lower-case individual and supporter in Fig. 4 and Fig 5 respectively).
Column headings for multiple individuals’ fields can just be repeated
To add more than one individual in a record (like a household membership), you can now just repeat the columns relating to the individual (First name, Last name, Email etc) as shown in Fig 5.
Previously you had to write cumbersome and programming-related headings like individuals[0].firstname, individuals[1].firstname and so on.
For example:
| Contact type | First name | Last name | First name | Last name | ||
| supporter | Ellis | Watson | ellis.watson63@ddje.uk | Sophie | Watson | s.watson@ntlworld.com |
| supporter | Shannon | Clarke | shannonc@gmail.com |
Error messages refer to spreadsheet row number
We still do a “pre-flight” check on your file before importing it for real. This shows any problems that need to be corrected before it can be imported.
However, any error messages are now related to the spreadsheet row number rather than the record number, so it more obviously relates back to the source. Because of the header row, row 2 of the spreadsheet was record 1 and so on!
Skip rows and columns you don’t want to import
If the first non-space character of a cell in column A is a # sign, the whole row is ignored – treated as a comment.
This means:
- you can prevent certain rows from being imported, and
- make notes about the file in the file itself.
This also affects the difference between record number and spreadsheet row number.
For example:
| Contact type | First name | Last name | ||
| # sign-ups from fair March 19, 2019 | ||||
| Supporter | Jacob | King | jacob-king38@ddje.uk | |
Similarly, put # before a column heading and the column will be ignored (note this doesn’t work for column A as that would ignore the whole header row). You can also ignore columns interactively while importing.
More information about what went wrong
There is now a link – more info/fix this – alongside each short error message (Fig 8) about problems detected in each row. Clicking that link shows a box (Fig 9) which:
- explains the error,
- what may have caused it, and
- what to do about it.
Fix many problems on-the-fly
In many cases, as well as the explaining an error, there are also options to fix it without having to edit and upload your spreadsheet again (Fig 9).
For example, if you miss out the record type column Type, and the records would all be the same type, say contact, you can select that type from the menu in the missing type explanation to have it applied to all the rows.
Flexible formatting of dates, currency, names, yes/no values and options
- Date fields can be given, as in Cameo, in any sensible date format.
- Currency fields can include the currency symbol or not.
- You can give a single Name column and let Cameo sort out which parts go where.
- Yes/no fields can be given with any of the following for yes (case-insensistive): Yes, Y, 1, True, On and for no: No, N, 0, False, Off. (Leaving a yes/no field blank will default to a sensible value, not always No).
- Options like types, list names and so on are case-, space- and punctuation-independent (if there is any ambiguity, an exact match takes precedence).