u3a SiteWorks CSV Import and Export

Introduction #

The SiteWorks development team has created a WordPress plugin called u3aImportExport. This tool can be used to import information into a SiteWorks site or export information from it. The extent of information which can be imported or exported is:

  • Venue
  • Contacts
  • Groups
  • Events

Information is imported from a set of comma-separated variable (CSV) files – one for each type of information listed above. The files have a predefined structure as described below.

The CSV method is especially useful for creating a SiteWorks site from a Site Builder site. A SiteWorks Starter Site can be created and this can then be partially populated using the CSV import method.

Creating the CSV Files #

The common structure of all the CSV files is as follows:

  • Row 1 must contain the header (column) names.
  • The data rows must start in row 2 and there cannot be any blank rows within the rows containing the data.
  • The four CSV files must be separate files.
  • If special characters are included, such as a £ sign in the Cost field of the Groups or Events file, the CSV file must be in UTF-8 format. See below for more details.
  • The header names must be as detailed in the following tables and no other columns may be included.

The CSV files can be created manually, typically using Microsoft Excel. Alternatively, they can be created using the “Download Table as CSV” extension added to the Google Chrome web browser which can extract information from Site Builder. This method can only be used if the user has access rights to the relevant u3a site within Site Builder. In addition, some relevant data can be exported from Beacon in CSV format.

Where data is obtained from Site Builder or Beacon, some manual manipulation of data will be required to comply with the rules for import to SiteWorks. An import template for the CSV files can be downloaded from the u3aImportExport plugin Export tab:

The downloaded file will be saved to the Downloads folder as defined in the web browser you are using (typically under Settings).

If the file needs to be in UTF-8 format, it is easiest to create it first in ordinary CSV format and then save it again as a CSV UTF-8 file. See the screenshot of the Excel Save As function and the file type options:

The rules for the structure and content of the CSV files are as follows:

Venues #

Header NameRequired?Can be Blank?Comment
NameYesNo
DistrictNoYesIf this field is not empty in any row of the CSV file, the District checkbox in u3aSettings > Venues within SiteWorks must be checked, otherwise an error message will occur
Address Line 1 NoYes
Address Line 2NoYes
Town NoYes
PostcodeNoYes
AccessibilityNoYes
Phone NoYes
URLURL must include either the http:// or https:// prefix or a syntax check will fail.

Contacts #

Header NameRequired?Can be BlankedComment
Name YesNo
Membership no. NoYes
Given NoYes
FamilyNoYes
Phone No NoYes
Phone2 No NoYes
Email NoYesMust be a valid email address or blank.

Groups #

Header NameRequired?Can be Blank?Comment
Name YesNo
StatusYesNoMust be: Active, open to new members or Active, not currently accepting new members or Active, full but can join waiting list or Temporarily inactive or No longer meeting.
Category YesNoMust be an existing Group Category within SiteWorks.
DayNoYesMust be: Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday or blank.
TimeNoYesMust be: Morning, Afternoon, Evening, All Day or blank
FrequencyNoYesMust be: Weekly, Fortnightly, Monthly or blank.
WhenNoYes
VenueNoYesMust be a valid venue already present within SiteWorks or blank.
CoordinatorNoYesMust be a valid contact already present within SiteWorks or blank.
Coordinator2 NoYesMust be a valid contact already present within SiteWorks or blank. If this field is not empty in any row of the CSV file, the Second interest group coordinator checkbox in u3aSettings > Groups within SiteWorks must be checked, otherwise an error message will occur
Deputy NoYesMust be a valid contact already present within SiteWorks or blank. If this field is not empty in any row of the CSV file, the Deputy checkbox in u3aSettings > Groups within SiteWorks must be checked, otherwise an error message will occur.
TutorNoYesMust be a valid contact already present within SiteWorks or blank. If this field is not empty in any row of the CSV file, the Tutor checkbox in u3aSettings > Groups within SiteWorks must be checked, otherwise an error message will occur.
EmailNoYesMust be a valid email address or blank. If this field is not empty in any row of the CSV file, the Group email checkbox in u3aSettings > Groups within SiteWorks must be checked, otherwise an error message will occur.
Email2 NoYesMust be a valid email address or blank. If this field is not empty in any row of the CSV file, the Second group email checkbox in u3aSettings > Groups within SiteWorks must be checked, otherwise an error message will occur.
CostNoYesIf this field is not empty in any row of the CSV file, the Cost in u3aSettings > Groups within SiteWorks must be checked, otherwise an error message will occur. If a £ sign is used, the import file must be in CSV UTF-8 format or an error will occur.

Events #

Header NameRequired?Can be Blank?Comment
NameYesNo
Category YesNoMust be an existing event category within SiteWorks.
Date YesNoThe format must be YYYY-MM-DD. See WARNING below regarding Excel.
TimeNoYesFormat must be HH:MM or blank.
DaysNoYesMust be an integer value or blank.
Group NoYesMust be an existing group category within SiteWorks or blank.
VenueNoYesMust be an existing venue within SiteWorks or blank.
OrganiserNoYesMust be an existing contact within SiteWorks or blank.
Cost NoYesif a £ sign is used, the import file must be in CSV UTF-8 format or an error will occur.
Booking No Yes Must be Yes or No or blank.

WARNING: If you create your Events CSV file within Excel, you will need to ensure that the Date format is correct. In the UK, Excel will typically save the date in DD/MM/YYYY format. However, this format can be changed to YYYY-MM-DD using Format Cells > Date > 2012-03-14. See the screenshot below:

Save the file as a CSV file or CSV UTF-8 file. DO NOT open the file again within Excel because the date format will be overwritten back to the DD/MM/YYYY. To be certain that the date format is correct prior to import to SiteWorks, you can open the CSV file using a text editor such as Notepad to ensure that the date format is correct.

Importing CSV Files into SiteWorks #

Use the u3a ImportExport plug-in included with the SiteWorks Starter Site. Choose the Import tab:

The CSV files MUST be imported in this order: Venues > Contacts > Groups > Events. The reason for this is that Groups depend on entries already imported from Venues and Contacts. Events also depend upon entries imported from Groups, Contacts and Venues.

For instance. if you wish to import the Venues CSV file, use the Select venues file button and choose the correct Venues CSV file, typically on your local hard drive. Then click on Upload selected files.

If there are no errors in the import file, then the screen for a venue file will be as below:

Click the Import files into WordPress button and the entries in the CSV file will be imported into WordPress and will show under u3a Venues. The number of entries successfully imported will also be shown:

Open u3a Venues and check that the imported entries all look correct.

If there are errors in the import CSV file, they will be detailed in plain English (with the row number where appropriate) after the Upload selected files button is clicked. These errors will need to be corrected before the file can be imported.

An example error message is shown below:

When you are importing Events, after the file is successfully uploaded, note the “Always create new posts when importing events?” Checkbox.

By default this checkbox is checked. If you are importing any time after the first time, this will lead to duplicate events being created. In most cases, this checkbox needs to be unchecked unless you are sure that you want to import duplicates.

Exporting CSV Files #

It is also possible to export Venues, Contacts, Groups and Events from SiteWorks in CSV format. This can be useful if changes to a lot of entries are required. It is far quicker to bulk edit in Excel than it is in SiteWorks. The revised file can then be re-imported back into SiteWorks.

First, it is necessary to update the export files using the Generate New Export Files button. If you do not do this, the Export files may not match the SiteWorks entries if SiteWorks has been modified since the Export files were last updated. Check that the date and time shown in the “Generated” message show the very recent past.

Then choose the file to be downloaded (exported): Venues, Contacts, Groups or Events. When exporting, it does not matter in which order the exports occur.

If, for example, we choose to export Events, a pop-up message thus will occur:

The file name is predefined as “events.csv” (or venues, groups or contacts as appropriate). The downloaded file will be saved to the Downloads folder as defined in the web browser you are using (typically under Settings). Using your file manager you can move this file, rename it etc as required.

If we look at the contents of the events.csv file:

Note that the first (most lefthand) column contains a numerical ID. This applies to the entire row. Note also that Group, Venue and Organiser also have their own numerical IDs. These ID numbers are assigned automatically by SiteWorks and will always be integers.

The other export files also use IDs against various fields (immediately to the left of the relevant field).

Re-Importing CSV Files #

DO NOT change IDs other than to delete them completely where required as described below. These ID fields are used to tell the Import process whether to update an entry or not.

There are various possible situations which affect how IDs are used:

Adding a Complete New Entry #

To add a new entry, obey the same rules as detailed for Imports under Section 2. Fill in all required fields for the new entry but leave the ID fields blank. Import the revised file again using the same procedure as in Section 2.

Changing the Entry Name #

Edit the Name as required and any other fields. Leave all ID fields unchanged. When the file is imported using the same procedure as in Section 2, a new entry will be created using the new Name and the entry using the old name will be deleted.

Changing Fields Within an Entry (Other than Name) #

Leave the Name field and the first ID field unchanged. Change the required field and, if it has a related ID entry, delete the ID in only the relevant ID field. Import the revised file again using the same procedure as discussed above.

Revision 1: 19/07/2023
Minor updates: 18/10/2023

Author Nick Amery
Uploaded: Richard M. Crowder

What are your feelings
Updated on 2023-12-13