Import Export

Fabian4 Import and Export Functions

1 Management of Data

Figure 1 - Fabian 4 Import / Export Data Flow

1.1 The Fabian4 database resides on the Fabian4 web server and can be accessed via the Organiser's Import & Export web page interfaces, 24/7. There are 2 export formats available. The comma separate variable (csv) format is suitable for the Sport Software OE (single day), MT (multi-day), and MultiSport event software packages, as well as MERCS, and Microsoft Excel (or any spreadsheet package). The XML format conforms to the IOF XML entry list standard, and is suitable for loading into Aut-O-Download, or any other xml tool.

1.2 Online entries are written directly to the database as they occur. Postal entries may be added via a web interface, or by import in the csv Sport Software OE/MT format (see section 5 below). Depending on your chosen method of adding postal entry data, the "master" set of data may reside wholly within the Fabian4 database, or may be split between the Fabian4 database (for online data) and an external data store such as Sport Software OE/MT200X (for postal entry data).

1.3 Where the data is split, each set may be transferred into the other using the Import/Export functions. The start list associated with each event is a live view of the competitor data currently held within the database. Start times are displayed as "tba" until allocated. It is recommended that postal entries are added to the database at regular intervals, in order to keep the start list as current as possible. In the period leading up to the event, any data transfer will mainly be towards the Fabian4 database. Just prior to the event, a final export should be performed, and this csv file imported into your chosen event software package. As can be seen below, certain of the Text1, Num1, Text2, etc fields are populated as part of this export.

1.4 If you are importing start times, this can be done periodically in a number of stages while the online entries are still open. Export the data, add the times, import the data back, export again capturing the latest entries, add the times to entrants without a time, import back, etc.

1.5 In summary therefore, it is recommended that you concentrate on keeping the Fabian4 database as up to date as possible until you are in a position to perform a final export, the generated file then being imported directly into your event software package.

2 Conventions

Postal entries uploaded via import should use start numbers (Stno) from 5001.
Postal entries uploaded via import should use form numbers (Num1) from 5001.
Online entries & postal entries entered via the web interface have start numbers in the range 1 to 5000.
Online entries & postal entries entered via the web interface have form numbers 1 to 5000.

3 Importing

3.1 Competitor Data can be imported into the Fabian4 database using the import function. The imported data must be in OE2003 CSV format for single day events, and MT2003 CSV format for multiday events. See section 5 for further details on the exact format required.

3.2 The import options are as follows:

3.3 Validate File Only - It is recommended that this option is ticked, in the first instance, in order for you to analyse any error before applying the imported file to the database.

3.4 Clear Imported Postal Entries and re-import from file - Whenever postal entries are imported (with this option selected), any existing imported entries are cleared from the database. Hence you must import the full set at all times. Postal entries added via the web interface are unaffected and are treated as though they are online entries. Any start time information contained within the file will be ignored unless the next item is ticked.

3.5 Import start times - With this option selected, any start times associated with online entries, or postal entries added via the web interface (i.e. start nos. 1 to 5000), are written to the database ready for display on the start list. Likewise, start times associated with imported postal entries (i.e. start nos. >= 5001) are written to the database as long as the "Clear Manual Entries and re-import from file" option is ticked also. Note that a start time of 00:00:00 is displayed as "Open" on the start list. Use this feature for helpers if appropriate.

3.6 Import hired e-card numbers for online entrants - This option functions as the name suggests. E-card numbers associated with imported postal entries are always written to the database when the "Clear Manual Entries and re-import from file" option is ticked. Competitors hiring an e-card will have "Hire" displayed in the start list while the SI card field remains blank (i.e. do not populate these fields with 0).

3.7 Import bib (race) numbers for online entrants - Bib / Race numbers inserted into the Num3 column (see Table 1) can be imported into the database. To display the bib / race numbers on the start list see the options on Control Panel page.

4 Exporting

4.1 The export function provides the following options:

4.2 Competitor Data (CSV format for Sport Software / MERCS / Excel) - This exports competitor data in OE2003 or MT2003 format, depending on the number of days in your event. For full details of the fields populated and their syntax, see section 5 below. This option populates some of the Text and Num fields with additional data including: country code (e.g. ENG, FRA, etc), age class (e.g. M21), split start requests, entry form number and course number. The block field(s) is/are populated with any start time preferences.

4.3 Competitor Data (XML IOF Entry List format for Aut-O-Download) - This exports competitor data as an xml file as defined by the IOF XML standard. The Aut-O-Download Sports Software package accepts this format.

4.3 Entry Form Data (includes contact details, event options & special requests) - This exports all "form" related data including addresses and other contact details, special requests, and event options. See section 6 for full details. Data is only exported for online entrants, and for those postal entrants added via the web interface. This export is in CSV format.

4.4 Competitor Questions/Answers - This exports all the answers to competitor specific questions as collected in step 3A of the entry process. Data is only exported for online entrants, and for those postal entrants added via the web interface. When viewed with a spreadsheet application (e.g. Excel) there is one row for each competitor, with the answers in the various columns. This export is in CSV format.

4.5 Include address details in the Competitor Data (CSV) export - This will include certain contact details in the OE2003 or MT2003 format export. See Table 1 below. Note that where several people have entered together on the same form, the contact details are repeated for each entrant. For a list of unique contact details (e.g. for printing address labels) you should use the Entry Form Data export.

5 SportIdent File Formats

5.1 Table 1 describes the SportIdent OE2003 v10.3 format for single day events:

Table 1 - SportIdent OE2003 (v10.3) Format

Field Name	Description	Populated by Export	Read by Import
Stno	Competitor Start Number	X	X
Chip	Competitor SI Card / Chip Number Null = not yet allocated	X	X
Database Id	Competitor BOF number Null = non member	X	X
Surname	Surname	X	X
First name	First name	X	X
YB	For orienteering and bike events: Year of Birth 4 digits. e.g. 1971 For running events: Date of Birth ddmmyyyy e.g. 23031987 Note1: If you open the csv file in Excel, any leading zero is not displayed (e.g. 04071999 is displayed as 4071999). To see the full ddmmyyyy select Format >= Cells... => Number Tab => select Custom and set to 00000000 Note2: Importing into SportSofware OE/MT2003 will take the final 2 digits. E.g. 04071999 or 1999 will result in 99 being imported.	X	X
S	Sex (M or F). Null defaults to M on SI import	X	X
Block	Start time preference. Used by SI for start time allocation. Null = Any 1 = V Early 2 = Early 3 = Middle 4 = Late 5 = V Late	X	X
nc	Non competitive 0 = competitive X = non-competitive Null defaults to 0 on SI import	X	X
Start	Start time - relative to zero time. Zero time is usually set to 00:00:00. Syntax hh:mm:ss Null = not yet allocated A start time of 00:00:00 is displayed as "open" on the start list.	X	X
Finish	Finish time - relative to zero time
Time	Competitors total time
Classifier	0 = normal 1 = dns (did not start) 2 = dnf (did not finish) 3 = mp (miss punched) Null defaults to 0 on SI import
Club no.	The id number of the club. BOF allocates numbers to all UK clubs. List available from the SI archive. Foreign clubs are automatically allocated the next available number.	X
Cl name	Not used within the UK. Identifies individual clubs within the same City.
City	The BOF abbreviation of a UK club, or the name of a foreign club.	X	X
Nat	The nationality of the club. IOF abbreviation. E.g. GBR, IRL, FRA, SWE, etc.	X	X
Cl. no.	The id allocated to the class. Please contact Fabian4 if you require the master set of Class / Id mappings. Non-standard classes are allocated a unique id as part of the event set-up.	X
Short	Short name of the class. E.g. M21L, MN, Y	X
Long	Long name of the class. Long names and short names are usually the same, but can vary if desired. E.g. M21L, Men Novice, Yellow.	X	X
Num1	Entry Form number (see section 2, Conventions)	X	X
Num2	Course number	X
Num3	For team events: The "Team Number" as displayed on the start list. For other events: Bib (race) number	X	X**
Text1	For orienteering events: Age class - E.g. W16, M35, etc. For running and cycle (AUDAX) events: Age category based on age on competiton day: U3 to U19 in 1 year bands, U23, SEN, 40, 45, increasing in 5 year bands up to 95. All categories preceeded with "M" or "W". For mountain bike (BMBO) events: BMBO class - E.g. Y18, M21, X40, etc.	X
Text2	Country code IOF abbreviation. E.g. GBR, IRL, FRA, SWE, etc. When Nat=GBR this field may contain ENG, CYM, SCO, NI or IOM (as entered by the competitor in step 2, or as read on import).	X	X
Text3	For orienteering events: Split start requests & Ecard Touch Free Hire. Split = Parent split start TFH = Touch Free Hire Split TFH = Parent split start and Touch Free Hire Null = no request. For UK Athletics registered running events: Affiliation number (where applicable) For ARC registered running events: The text ARC where the competitor's club is affiliated to ARC or UKA For Cycle (AUDAX/BC/CTC) events: The AUDAX/BC/CTC Membership No.	X	(X)*
Adr. name	(optional) Address1	X
Street	(optional) Address2	X
Line2	(optional) City	X
Zip	(optional) Postcode	X
City	(optional) County	X
Phone	(optional) Home Telephone	X
Fax	(optional) Mobile Telephone	X
Email	(optional) Email Address	X
Id/Club
Rented	Rented SI card indicator. X = hired 0 = not hired Null = not hired	X	X
Start fee	Entry fee paid (online entrants only) 0 = postal entrants	X
Paid	Paid indication - populated with X for all entrants 0 = no X = yes	X
Team	For team events only (as used by OEScore 2003): The start number of the leading (1st) team member of the team. Where there is only 1 competitor in the team this field will be blank. This field will not be present of non team events. Note that although this is labelled as Team, it is not the Team Number as displayed on the start list. The Team Number is exported in the Num3 column (see above).	X

Table 2 - Key for SportIdent Tables 1 and 3

X	On Import - Database updated with imported postal entries only
X	On Import - updates online & web interface entries as well as imported postal entries
(X)*	Not yet implemented - check status if required
X**	The import is applicable to Bib Numbers only and then only for non team events.

5.2 Table 3 describes the differences between SportIdent MT2003 format, as used for multiday events, and the OE2003 single day event format, described in Table 1 above.

Table 3 - SportIdent MT2003 (v10.3) Format

Field Name	Description	Populated by Export	Read by Import
Chip1 .. Chip6	Competitor SI Card / Chip Number for each day 1 to 6. Null = not yet allocated or not entered on that day. The numbers, for each day, must match on import. Value only populated for the days entered on export.	X	X
Block1 .. Block6	Start time preference for each day 1 to 6. Used by SI for start time allocation. Null = Any 1 = Early 2 = Middle 3 = Late	X	X
E1 .. E6	Entered indicator for each day 1 to 6. 0 = no X = yes Null defaults to X on SI import	X	X
nc1 .. nc6	Non competitive indicator for each day 1 to 6. 0 = competitive X = non-competitive Null defaults to 0 on SI import	X	X
Start1 .. Start6	Start time - relative to zero time. Zero time is usually set to 00:00:00. Syntax hh:mm:ss Null = not yet allocated or not entered on that day. A start time of 00:00:00 is displayed as "open" on the start list.	X	X
Finish1 .. Finish6	Finish time - relative to zero time
Time1 .. Time6	Competitors total time
Classifier1 .. Classifier6	0 = normal 1 = dns (did not start) 2 = dnf (did not finish) 3 = mp (miss punched) Null defaults to 0 on SI import
Num2	Not populated. Use the v11 format for multiday course number information.

6 Entry Form Data Export Format

6.1 Table 4 describes the format of the "Entry Form Data" csv file.

Table 4 - Entry Form Data File Format

Field Name	Description
Form No.	The entry form number
First Name	The first name of the contact entered in step 1
Surname	The first name of the contact entered in step 1
Competitors	The number of competitors still entered into the event on this form. I.e. The count excludes cancelled competitors. Important Note: When printing address labels you may wish to set a filter on this field to exclude forms where the value = 0. This will exclude forms where all the competitors on the form have cancelled and those forms relating to the purchase of event options only (I.e. where the competitor/s have entered previously on another form number). Also see the description of 'Competitor Fees' below.
Online	True - for online entries False - for postal entries added via the web interface
Transaction ID	The SagePay transaction number.
Competitor Fees	The total fees for all competitors entered on this form (including cancelled competitors). If this figure is zero then it means the form was used for the purchase of event options only.
Option Fees	The total value of all event options purchased
Total Fees	The grand total of the competitor and option fees (or the total Adhoc Payment fee)
Date	The form creation date (i.e. for online entries the date of entry)
Address1	From the step 1 Contact details data
Address2	As above
City	As above
County	As above. This field may contain the Country.
Postcode	As above
Home tel	As above
Work tel	As above
Mobile tel	As above
Email	As above
Option1 name	The name of the first event option. The value contains the number requested 0 .. n. Tick box options have the value 0 or 1.
OptionN name	As above, repeated for all event options.
Special Request	The special request text, if any, as entered in step 3 Event specific details.