Overview

This is a very simple web application that may be useful to those managing their electronic logbooks using general purpose spreadsheets, databases etc. rather than proprietary commercial logbook programs. It takes a simple input format and produces comma separated value (CSV) text that can easily be copied and pasted wherever desired.

It can be used manually, but is probably most useful when used in conjunction with a program that extracts data from a rostering system. A suitable extraction program will soon be made available to easyJet pilots.

10 minute tutorial

This tutorial demonstrates how data can be manually input and converted. While this is substantially easier than entering the data manually into a spreadsheet or database form, manual input remains fairly tedious. A more likely workflow is that data is extracted from a rostering system and saved into a file in the input format which is then uploaded to be tweaked. The button on the left is provided to allow upload of a suitable file.

For those with access to a suitable rostering system extraction program, the tutorial is a familiarisation with the input format so that you know how to tweak it. For those without it will represent actual workflow.

Add a date

In the text area, type 2016-11-16

This is 16th November 2016 in ISO format.

Add an aircraft

On the next line down, type G-EZUS:A320

This is the A320 with registration G-EZUS.

Add a sector

On the next line down, type BRS/GLA 1730/1830

This is a sector from Bristol to Glasgow, off blocks at 17:30z and on blocks at 18:30z.

Run the parser

You should now have:

2016-11-16
G-EZUS:A320
BRS/GLA 1730/1830

Click Run Parser

You should see a Sectors box open with an entry for the sector:

2016-11-16 17:30:00, 60, 0, "BRS", "GLA", "G-EZUS", "A320", "Self", "p1", ""

This is the sector in Comma Separated Values (CSV) format, where fields are separated with commas and strings enclosed in straight double quotes. CSV format is recognised by spreadsheets, so this line can be copied in the browser and pasted into a spreadsheet and the spreadsheet will be able to divide it into columns. With Google Sheets, after pasting, a clipboard icon opens next to the pasted text with the option Split text into columns. This functionality is also available via Data > Split text into columns. LibreOffice Calc and other spreadsheets offer similar functionality.

The fields are:

  1. Off Blocks in ISO Datetime format
  2. Flight duration in minutes
  3. Night flying duration in minutes
  4. Departure airport
  5. Arrival airport
  6. Aircraft registration
  7. Aircraft type
  8. Captain's name
  9. Role
  10. Comment

Add the return sector

On the next line down, type / 1900/2000, then re-run the parser. The sectors box should now show:

2016-11-16 17:30:00, 60, 0, "BRS", "GLA", "G-EZUS", "A320", "Self", "p1", ""
2016-11-16 19:00:00, 60, 0, "GLA", "BRS", "G-EZUS", "A320", "Self", "p1", ""

If the departure and/or arrival airports are omitted, the departure airport is assumed to be the arrival airport of the previous sector, and vice versa. Also note that the date and aircraft are carried forward, and that the order of these lines is not important as long as once a sector is encountered, a date and an aircraft have been specified.

Add an aircraft swap, then an Edinburgh and back

On the next lines down, add G-EZOK:A320, /EDI 2030/2130 and / 2200/2300. You should now have:

2016-11-16
G-EZUS:A320
BRS/GLA 1730/1830
/ 1900/2000
G-EZOK:A320
/EDI 2030/2130
/ 2200/2300

Running the parser now gives:

2016-11-16 17:30:00, 60, 0, "BRS", "GLA", "G-EZUS", "A320", "Self", "p1", ""
2016-11-16 19:00:00, 60, 0, "GLA", "BRS", "G-EZUS", "A320", "Self", "p1", ""
2016-11-16 20:30:00, 60, 0, "BRS", "EDI", "G-EZOK", "A320", "Self", "p1", ""
2016-11-16 22:00:00, 60, 0, "EDI", "BRS", "G-EZOK", "A320", "Self", "p1", ""

Add the duty times

On the next line down add 1630/2330 so that the text area looks like:

2016-11-16
G-EZUS:A320
BRS/GLA 1730/1830
/ 1900/2000
G-EZOK:A320
/EDI 2030/2130
/ 2200/2300
1630/2330

Running the parser will now open a Duties section, with the following contents:

2016-11-16 16:30:00, 420, ""

This is the duty start time in ISO Datetime format, the length of the duty in minutes in CSV format and an optional comment. Again, this can be copied from the browser and pasted into a spreadsheet and the spreadsheet will be able to divide the columns properly.

Night flying

Let us assume that sectors 2 to 4 inclusive are night flights. Modify the text area by adding the night flag, n to sectors 2 to 4 so that it looks like this:

2016-11-16
G-EZUS:A320
BRS/GLA 1730/1830
/ 1900/2000 n
G-EZOK:A320
/EDI 2030/2130 n
/ 2200/2300 n
1630/2230

Running the parser now gives:

2016-11-16 17:30:00, 60, 0, "BRS", "GLA", "G-EZUS", "A320", "Self", "p1", ""
2016-11-16 19:00:00, 60, 60, "GLA", "BRS", "G-EZUS", "A320", "Self", "p1", ""
2016-11-16 20:30:00, 60, 60, "BRS", "EDI", "G-EZOK", "A320", "Self", "p1", ""
2016-11-16 22:00:00, 60, 60, "EDI", "BRS", "G-EZOK", "A320", "Self", "p1", ""

Note that the third column now matches the second column for sectors 2 to 4.

Not the Captain?

If using a rostering system extraction tool, this will be the major thing that requires tweaking, since rostering systems do not record the per sector role. Above the first sector add {CP:Bloggs Jo} and add "p1s" and "p2" flags to the sectors so that the text area looks like:

2016-11-16
G-EZUS:A320
{CP:Bloggs Jo}
BRS/GLA 1730/1830 p2
/ 1900/2000 n p1s
G-EZOK:A320
/EDI 2030/2130 n p1s
/ 2200/2300 n p2
1630/2230

Running the parser now changes the sectors box to:

2016-11-16 17:30:00, 60, 0, "BRS", "GLA", "G-EZUS", "A320", "Bloggs", "p2", ""
2016-11-16 19:00:00, 60, 60, "GLA", "BRS", "G-EZUS", "A320", "Bloggs", "p1s", ""
2016-11-16 20:30:00, 60, 60, "BRS", "EDI", "G-EZOK", "A320", "Bloggs", "p1s", ""
2016-11-16 22:00:00, 60, 60, "EDI", "BRS", "G-EZOK", "A320", "Bloggs", "p2", ""

Other crew members

When you declared the Captain in the previous section, a Crew section will have opened up. This allows you to record the rest of the crew on your flight, should you desire to do so. In practice it is unlikely that you will want to do this unless you have access to a rostering system extraction tool. Change {CP:Bloggs} to {CP:Bloggs Jo, PU:Smith Jane, FA:Brown Mike, FA:Taylor Liz} , so that the text area looks like:

2016-11-16
G-EZUS:A320
{CP:Bloggs Jo, PU:Smith Jane, FA:Brown Mike, FA:Taylor Liz}
BRS/GLA 1730/1830 p2
/ 1900/2000 n
G-EZOK:A320
/EDI 2030/2130 n
/ 2200/2300 n p2
1630/2230

Running the parser now changes the Crew section to:

2016-11-16 17:30:00, "CP:Bloggs Jo", "PU:Smith Jane", "FA:Brown Mike", "FA:Taylor Liz"
2016-11-16 19:00:00, "CP:Bloggs Jo", "PU:Smith Jane", "FA:Brown Mike", "FA:Taylor Liz"
2016-11-16 20:30:00, "CP:Bloggs Jo", "PU:Smith Jane", "FA:Brown Mike", "FA:Taylor Liz"
2016-11-16 22:00:00, "CP:Bloggs Jo", "PU:Smith Jane", "FA:Brown Mike", "FA:Taylor Liz"

The spreadsheet

This service will most likely be useful to people who already use a spreadsheet of their own design for their logbook. If you haven't already created one, I did create a logbook spreadsheet to test the methodology, which may be a useful starting point. In particular, the “Scratch” tab demonstrates how you can easily mould the data to your own designs: paste sectors on the left side to get an alternate format on the right.

Future-proofing

The critical question with respect to logbook data is how to avoid losing it. Firstly, it needs to be in a file format that is very widely used in order to minimise the danger of loss through software obsolescence. Secondly, it needs to be created with software that is very well tested, i.e. it has a very large installed user base. This minimises the chance of an undiscovered software bug insidiously corrupting your data. Lastly, it needs to be easily transportable, such that you can regularly place copies of the file in diverse locations so that no feasible combination of hardware failures can destroy your data.

My solution is to use a widely used open source database server (MariaDB) in combination with an overall backup strategy based on Borg. This solution is a little complex to set up, however.

Spreadsheets would seem to be a reasonable compromise. They are fairly easy to work with, many people already have experience with them, and their installed user base is huge. If you use an online spreadsheet such as Google Sheets, you automatically have an off-site backup, so an occasional download to your local machine will give you pretty good protection against hardware failures.

The major problem with spreadsheets is that data entry is soul crushingly tedious. The purpose of this service is to reduce some of that tedium. It is intentionally not critical that the service continues to exist; there will always be the fallback of manual data entry straight into the spreadsheet.