Process and Import Uploaded CSV Club Members List
Online Croquet England Directory Help.
Please refer to Upload Club Membership CSV for guidance on creating and uploading a club member list as a CSV file to the online Croquet England Directory.
Processing an Uploaded CSV
The club's list of members is uploaded in a CSV file by the club's administrator and is held securely but separately from the main database until its records have been processed.
An uploaded CSV provided by a club is processed in several steps as follows:
- Mapping : the system must understand what the columns in the uploaded list mean
- Cleaning : the contents of each person's membership record are checked for uniformity and completeness, referring back to the club if data is missing or ambiguous
- Linking : each line in the upload is compared with the current contents of the Croquet England Directory to decide whether or not it refers to an existing member. A fuzzy-matching algorithm is used for this, as some of the details provided about a person may differ from those currently recorded
- Importing : the data is imported and becomes live:
- if the record matches someone already known to the system their record may be updated with any changes (see below for details) or the submitter may be emailed to check the details held online and those uploaded by the club
- otherwise, a new record is created
- if the club membership type does not exist, it is created
- if the club membership type is not the same as the Directory, the club member is transferred to the new membership type
- new members are created but members missing from the uploaded CSV are neither reported upon nor removed from the Directory
- this is so the upload can be used to add any new members without having to re-upload all members
The uploader can undertake as many of these steps as they wish up to and including the cleaning stage (subsequent ones require privileged access to the Croquet England Directory). However, that is not compulsory - some uploaders stop as soon as the upload has been accepted and so leave Croquet England staff to complete the process.
Importing a List of Club Members
The process to import the uploaded CSV club members list is as follows:
- (status: Uploaded) Action: Map the columns to match database fields
- If data is missing, mapping cannot be completed and the club data should be enhanced and re-uploaded
- Once mapped, with all the required columns set (status: Mapped) copy becomes possible
- (status: Mapped) Action: Copy the data into the mapped columns (ignoring any uploaded columns that are not mapped) in readiness to import into the database
- The copy operation automatically reformats some fields (such as telephone numbers) and raises warnings and errors on others for review before importing
- The uploaded (original) data is never modified and remains available for administrator inspection
- (status: Copied) Action: Clean and Check. The copied data is reviewed and, once the errors and warnings are dealt with, the data is considered clean
- The copy can be deleted if needed in order to start again. This does not delete the original uploaded data, which cannot be deleted once mapped
- (status: Cleaned) Action: Link to people. Find any records already in the database that might match an uploaded record
- Inspect the import candidate and already-existing record(s) side-by-side and mark them as matching (or not)
- On subsequent imports, club membership number helps with the linking process
- (Possible status: Reverted) Action: Revert to club usually because of missing essential data that can be easily corrected
- Once the query is answered, the uploaded CSV can be deleted or resurrected as appropriate
- (status: Linked) Action: Import. Update matching records from the imported data (e.g. a phone number might have changed - but see notes below on what data is not copied) and import in their entirety any people that don't link to an existing record (new member)
- (Status: Imported) Nothing more to do! At any previous stage though it may be that individual rows cannot be recommended for import without clarification and such rows are marked as Ignored. After the bulk has been imported, these individual records might be updated and then join their fellows in the main database
What is Mapping?
Column Mapping means telling the system what each of the uploaded CSV file columns means, and is performed by selecting a standardised name to match each heading in the uploaded CSV file.
- Some columns can be left unmapped and they will not be incorporated into the database
- Some columns are mandatory and the upload cannot be progressed until they are available in the mapping
- Mapping happens automatically if the guidelines are followed, but inspection and manual intervention might be required otherwise
If you need to add more columns to the uploaded CSV file, or tweak your data (such as moving the town into a unique column), then delete the upload and start again. You can only delete the upload from the page where you are viewing its contents.
There must be a column to show on each row whether or not this is the member's primary club - a Croquet England Standard subscription is created for all club members so marked unless they are Croquet England subscribers already.
Details of Data Processing
This section describes what is done when a record in the upload that has been linked to one in the online Croquet England Directory is imported:
- If the record in the upload is not linked with any record in the Croquet England Directory, a new one is created in the database by copying the data from it
- Some blank fields in the online Croquet England Directory are populated if set in the uploaded CSV
- Some fields, if different in the CSV and online Directory, trigger an email to the person who uploaded the CSV to ask them to check whose version is right and to update whichever version is wrong
The table summarises how the uploaded CSV is treated when updating an existing record, as well as which columns are required in the CSV upload and how blank cells are treated.
Field | Type | If Column Missing | If CSV Field Blank | If DB Field Blank and CSV not | If CSV and DB Fields non-Blank and Differ |
---|---|---|---|---|---|
Title | Text (title) | Warn | Copy | Ignored | |
Initials | Text (initials) | (warn if column has blank values) | => first letters of forename(s) | Ignored | Ignored |
Forename | Proper Name | Reject | Block | Copy | Email Sent |
Surname | Proper Name | Reject | Block | Copy | Email Sent |
Address Line 1 | Address | Reject | Block | (can't happen) | Ignored |
Address Line 2 | Address | Ignored | Ignored | ||
Address Line 3 | Address | Ignored | Ignored | ||
Address Town | Address | Warn | Ignored | Ignored | |
Address County | Address | Ignored | Ignored | ||
Postcode | Postcode | Reject | Block if UK address, else Warn | (can't happen) | Email Sent |
Address Country | Choice | => "UK" | (can't happen) | Ignored | |
Warn | Warn | Copy | Email Sent | ||
Skype | Text Line | Copy | Email Sent | ||
Tel. Home | Phone | Ignored | Ignored | ||
Tel. Mobile | Phone | Ignored | Ignored | ||
Tel. Work | Phone | Ignored | Ignored | ||
AC Handicap | Real Series | Copy | Email Sent | ||
GC Handicap | Real Series | Copy | Email Sent | ||
SC Handicap | Real Series | Copy | Email Sent | ||
Has CVD | Yes/No | => No | - | Ignored | |
Date Of Birth | Date | Reject if Is Junior column present |
Block if Is Junior |
Copy | Email Sent |
Visible To Club Members | Yes/No | Reject | => No | - | Ignored |
Visible To Croquet England Subscribers | Yes/No | Reject | => No | - | Ignored |
Membership Type Name - Club | Text Line | Reject | Copy | Overwritten | |
Is Primary Club | Yes/No (or name of club uploading) | Warn | => Yes | - | n/a |
Is Junior | Yes/No | Warn | => No (= adult) | (can't happen) | Ignored |
Notes and Key:
- Type: the input filter applied to the submitted (or missing) data
- If Column missing (at mapping stage ):
- reject: column must be present in the mapping to proceed to cleaning
- warn: warning issued but not blocking
- If CSV Field Blank (at cleaning stage ):
- warn: display a warning but allow import
- block: row must be ignored to proceed to import the CSV upload
- Missing data is replaced by the default - shown with => default-value
- If DB Field Blank and CSV not (at import stage )
- Copy: if Is Primary is yes the online Croquet England Directory is updated with the CSV value
- if not Is Primary then the CSV is ignored
- Copy: if Is Primary is yes the online Croquet England Directory is updated with the CSV value
- If CSV and DB Fields non-Blank and Differ (at import stage )
- Email Sent:
- An email is sent if all the following are true:
- Is Primary is yes (the subject's primary club uploaded the data)
- non-empty values are present in both the CSV and the online Croquet England Directory
- the values are different
- The email is sent to the person who uploaded the CSV alerting them to a discrepancy between the club and Croquet England records asking them to update whichever is in error
- An email is sent if all the following are true:
- Ignored: no action is taken for different values in the club version and the online version
- Email Sent:
- Further notes:
- At the cleaning stage :
- All data is passed through its input filter
- This is what is applied when editing an individual record online
- It includes formatting, default value, and validation as defined in the database schema
- Any user feedback generated by the input filter is presented in the clean-up table as an error, blocking progress to import
- The Country is forced to UK for any recognisable alternatives (e.g. England, Scotland, GB, etc.)
- Address lines over 30 characters are warned as "too long"
- If the input filter returned blank and the CSV data is not blank, CSV data is displayed with a warning (the database is only updated where shown)
- If the Croquet England subscription is U25 Standard then the DoB is checked, giving a warning if not in range:
- If missing "U25 Standard subscription requires Date of Birth"
- If over 25 "U25 Standard subscription does not apply to this DoB"
- If DoB is present then it must indicate a plausible age, or else a warning is given: Birth date is out of range
- If the forename or surname is not all alpha, dash, quote, and space a warning Possibly invalid forename or Possibly invalid surname is issued
- UK postcodes are checked for validity and corrected automatically (and silently) if possible
- if still invalid then an error is generated
- this deals with common errors, for example, typing the letter O instead of the number 0
- All data is passed through its input filter
- At the importing stage :
- If the CSV line is marked as ignored then nothing happens
- If the CSV line is not matched to an online Croquet England Directory record:
- A new person record is created with all the data in the CSV line
- If the CSV line is matched to an online Croquet England Directory record:
- Email the uploader if the fields marked email sent in the table above differ between the CSV line and the record, and Is Primary is set yes for the line
- Otherwise, ignore any differences (such as phone numbers or address formatting)
- In all cases, any issues raised are stored and shown on the relevant line when the CSV is subsequently displayed
- At the cleaning stage :