Exports and import

The Imports and exports screen moves tournament data in and out of Meridian. It holds a wizard that reads a spreadsheet and writes entries, judges, rooms, debates and results; a roster download; the tab workbook export; and the DebateXML and Meridian JSON exchange. A separate bar of CSV downloads sits at the top of every other admin screen.

The screen is the Exports entry under Records in the left rail, at the route /tournaments/{id}/admin/data. Its on screen eyebrow reads Data & Integrations and its heading reads Imports and exports. The CSV bar reads Export tab data as CSV, appears on every admin screen except Setup, and downloads from /tournaments/{id}/admin/export?resource= followed by the name of the file.

Who can use it

Every download and every import checks admin access again on the server. A person who is not signed in is refused with a 401 answer, and an account that does not administer the tournament is refused with a 403 answer, apart from the tab workbook, which answers as not found.

The CSV bar

The bar is a collapsed section. Selecting Export tab data as CSV opens a row of eight links. Each link downloads one file directly from the table named below, filtered to this tournament. A file is named resource-tournamentid.csv, for example rooms- followed by the tournament's identifier.

• Header order. The declared columns come first, then every other column the rows carry, in the order first seen. Rooms and Break categories declare their columns, so the header is written even with no rows. The other six files declare none, and a tournament with no rows in that table downloads a file holding only a line break.
• Cell rules. An empty value is an empty cell. A number is written as a number. A JSON value is written as JSON text. Text that begins with an equals sign, a plus sign, a minus sign, an at sign, a tab or a carriage return gets a leading apostrophe, so a spreadsheet program does not run it as a formula. A cell with a comma, a quote or a line break is quoted.
• Personal data. The Registrations file carries the participant email and any other contact or health information stored on an entry. Store the file as carefully as the registration list itself.
• Judges. Judge names and emails live in the judge profiles, which this file does not include.

Roster download

The columns, in order, are ID, Participant Name, Partner Name, Email, School and Event Code. School is the club recorded on the entry, or the school when no club is recorded. Event Code is the short code of the entry's event.

Debate interchange section

The tab workbook

The workbook has the sheet TeamData and ten sheets named Round1 to Round10, always, because the legacy macros look sheets up by name. Names are written as LastName/LastName. The wizard reads the same layout back.

Entries sort by participant name. A debate in a round above 10 has no sheet and is left out of the file. A league that runs more rounds should use the CSV files.

DebateXML and Meridian JSON files

The export carries teams, rooms, rounds, adjudicators, panels, motions and results. Conflicts and feedback are not part of the interchange. The import creates the teams as entries in the chosen event and the rounds as that event's rounds. Details of the import:

• Entries. A team whose name matches an entry already in the chosen event reuses that entry. A new team is created as an entry with a placeholder email address and the payment status completed. The same name in another event is another entry.
• Judges. Each adjudicator is added to the judge roster as pending, through a judge nomination that is accepted at once. An adjudicator without an address receives a placeholder address.
• Rounds. Rounds are numbered from 1 in file order, whatever rounds the tournament already has. A round with results is created as completed, and one without as upcoming.
• Pairings and motions. Each result becomes a pairing with its room, judge, winner and speaker points. Motions become unreleased resolutions. No pairing is released. Release each round from Rounds and pairing when it should be public.
• Undo. A failure removes the rows already written in reverse order and reports any row that could not be removed.
• Completed tournaments. A completed tournament refuses every import, because its results are a record.
• Records. A success writes an entry named Tournament file imported with the numbers of rounds and pairings.

The success message reads Imported a number of records from followed by the file name. The number counts rounds plus pairings and leaves out the entries and judges created.

The import wizard

The wizard imports a spreadsheet in three steps: upload, confirm, done. Nothing is written until the director commits at the end of the confirm step. The wizard accepts .csv, .tsv, .txt, .json, .xlsx, .xlsm and .xls files up to 5 MB. A title row above the headers is allowed. A tab separated file named .csv is read correctly: the delimiter is chosen from the file, from commas, tabs, semicolons and vertical bars.

The file stays in the browser between the steps and is sent again at commit. The server reads it again and takes only the column mapping and the row decisions from the screen. It never takes rows from the browser.

Step 1, upload

Step 2, confirm

The step bar shows the file name and the number of rows read from the sheet in view. A file with several sheets shows one button per sheet, labeled with the sheet name and the kind the sheet was read as. Selecting another sheet restarts its mapping and its row decisions.

Notices from reading the file appear above the first section, for example that a delimiter other than a comma was used or that a sheet was empty or cut.

Commit this import is disabled while a request runs, while any error remains, when the sheet kind is unknown, and, for registrations, when no event is chosen. Warnings do not block.

What each column can be read as

A sheet that carries both pairings and results columns is read as pairings, and the results are written on the same debates in the same pass.

How a sheet is read

• The header row. The first fifteen rows are scored and the best one wins. A row of words the importer recognises beats a dense row of other text, so a title, a date or a blank row above the headers is skipped.
• The kind. Each recognised header votes for a kind, and the sheet name votes as well. A kind needs a score of 2 or more. Headers outrank the sheet name.
• Column suggestions. A header is compared with a list of known spellings after capitals, punctuation and extra spaces are ignored, so Team Name, team_name and TEAM NAME match alike. A suggestion with confidence of 0.75 or more is applied to the column, and the Confidence cell shows how sure it was. A column with a lower score is left on Ignore this column. The director can change any column.
• Name matching. A name in a team column is matched to the entries already in the tournament. A match of 0.95 or more is accepted without a prompt and shows plain. A match from 0.70 to below 0.95 is proposed and shows with its percentage. Anything lower is unresolved. The three closest entries are offered as choices.
• Winners. A side word is accepted at once. A team name is matched against the two teams in that debate only. A name that matches a team outside the debate stays unresolved. An empty winner is a bye only when the opponent cell is empty or a bye word.
• Judge and Room. The matcher is given no judges and no rooms to compare against, because its reads of them name columns and a table the database does not have. A filled cell in a mapped Judge or Room column of a pairings sheet therefore matches nothing and blocks the commit. Set those columns to Ignore this column. A judges or rooms sheet writes to the same missing columns and table, so the database is expected to refuse the write and the commit rolls back.
• Unknown rounds. A pairing or result row whose round number matches no round already created in Rounds is not written, and the summary does not count it among the skipped rows. Create the rounds first.
• Existing debates. A debate that already exists in that round, in either side order, is updated with the new room, judge and result. Its released flag stays as it was. A new debate is written unreleased. Release is a step on Rounds.
• Order and undo. Rows are written in the order entries, judges, rooms, rounds, pairings. A failure removes everything written, newest first.
• Repeat protection. Each analysis gets an import id. Committing the same id twice writes nothing the second time.
• Completed tournaments. A completed tournament refuses every import, at upload and again at commit.
• Records. Every row written or updated leaves an entry named Entries imported, with the reason Spreadsheet import from followed by the file name.

Step 3, done

The step bar reads Done. A summary line lists what was added or updated, for example 12 registrations added, 3 debates updated, 1 row skipped, followed by the word Import and the first eight characters of the import id. Any issues follow, one per line with the row number. Two buttons follow. Import another file returns to Step 1. Open the record trail opens Records.

Tasks

To import a roster of new entries:

1. Open Records in the rail. Select Exports.
2. In the Import a spreadsheet bar, choose the file.
3. In section 01, check that Participant name is read from the right column. Change any other column with its Read as select.
4. In section 03, choose the event for rows that name none.
5. In section 02, select Skip on any row that should not come in. Resolve any row that shows a choice list.
6. Wait for the status line to read Nothing is unresolved. This can be committed.
7. Select Commit this import. Read the summary.
8. Select Open the record trail to confirm the entries were logged.

To bring results in from a workbook:

1. Create the rounds in Rounds first.
2. Open Exports and choose the workbook. Pick the sheet for each round in turn with the sheet buttons.
3. Set a Judge or Room column to Ignore this column when it is mapped.
4. For every Winner shown as unresolved, choose the side from the list.
5. Select Commit this import. Repeat for the next sheet.

To take the tournament out as a tab workbook:

1. Open Exports. In the Debate interchange section, select Export the tab workbook.
2. Work in the file. Keep the sheet names.
3. Bring it back through the wizard. Pick each Round sheet and the TeamData sheet in turn.

To download CSV files, select Export tab data as CSV at the top of any admin screen other than Setup, then select the link for the file.

Troubleshooting

Wizard messages appear in a red notice above the step. Messages about a row appear in the footer with the row number, or in the summary after a commit.

Interchange import messages

Things to know

• The wizard review shows the first 50 data rows. Rows below them are still read and checked at commit.
• The intro line of the screen says four file types, and the chooser accepts .csv, .tsv, .txt, .json, .xlsx, .xlsm and .xls.
• Imported entries use the signed in admin's account as their owner and a placeholder address when none is mapped. Replace placeholder addresses in Field before sending mail from Announce.
• For the rest of the entry workflow see Registration and field.