Monday, May 4, 2020

Importing "In Depth"

MyFlightbook has rich capabilities for importing your flights.  This will be a rather long post in which I will go into best practices and tips for successfully importing your flights.

There are three key scenarios here:
  • Basic importing - typically this is data that you enter directly yourself, transcribing from paper logbooks, but it also covers importing from other logbook solutions or spreadsheets.
  • Importing from other electronic logbooks - if you can get the data from the other logbook into a spreadsheet, you can generally import it with minimal effort
  • Bulk editing.  Possibly unique among electronic logbooks, MyFlightbook supports bulk editing by doing a round-trip: export data into a spreadsheet, bulk edit the spreadsheet, and then re-import the results.
I'll discuss each of these in turn.

Basic Importing

All importing is  done with a spreadsheet that is saved in "CSV" format.  Every spreadsheet program is capable of reading/writing CSV, but you may have to explicitly import to or export from the spreadsheet's native format (e.g., .xlsx for Excel or .numbers for Numbers).

CSV is really nothing more than a basic text file that is structured to denote a table with rows and columns of data: each line of text in the file corresponds to a row of data, and a comma (or semicolon, depending on local conventions) separates the columns of data within the row.

E.g., if your spreadsheet looks like this in Excel:
 
Then if you save it in CSV format and open it in a text editor, it would look like this:

       First Name,Last Name,Show
       Fred,Flintstone,The Flinstones
       Hermione,Granger,Harry Potter

There's a bit more to CSV than that, but that's probably already more than you needed to know.  The key thing is that you can work in Excel, Numbers, Google Sheets, etc., but the file you import must be in CSV format.

You can download a starting CSV Template here

So how do you structure the data within the CSV file?  It's pretty simple; at a high level, there are only two rules:
  • The first row of data in the file must contain the names of the fields you are importing, with each column containing the name of one field.  E.g., "Date" for the date of the flight or "Tail Number" for the aircraft used for a flight.  These can be in any order that you like.  Each column name must be unique, and it cannot be blank.
  • Each subsequent row contains the data for a single flight, one flight per row, with data for each field in the appropriate column.
There are lots and lots of different data fields that you can import - over 700 as of this writing.  These are described here.  There's a core set of about 2 dozen fields that are common to most logbooks, and there are nearly 700 more ranging from "Acting PIC Time" to "Zero Visibility Takeoff".  But be careful to use the column name EXACTLY as written in this list; if it isn't a match, the entire column will be ignored.

Only three of these fields are required: Date, Tail Number, and Total Flight Time.  You can use any additional fields that you like.

On the data rows, you can leave a field blank for anything that should have a default value - e.g., blank means 0 for a numeric field, no for a yes/no field, empty for a text field, etc.

Dates and numbers should be formatted using whatever your browser's conventions are.  So if you're in the US, 90 minutes would be "1.5" but in France it would be "1,5"; similarly, dates are in m/d/y format in the US but d/m/y in much of the rest of the world.  The system figures out which format to use based on the locale setting of your browser.  For dates, it's often a good idea to avoid ambiguity by using a "universal" notation of yyyy-mm-dd hh:mm.  E.g., "2020-05-04 13:49" for May 4 2020 at 1:49 in the afternoon works everywhere (but in the US, "5/4/2020 1:49PM" should actually work fine for this.)

For time fields, you can use decimal.  HH:MM (e.g., 1:03 for an hour and 3 minutes) notation should also work, but I'd advise against it because most spreadsheets get confused - they expect HH:MM to represent a time of day, not a quantity of time - and thus they both do math (e.g., addition) incorrectly or they convert it to a full date format when saving.

OK, that's the basic mechanics.  Let's talk data about some of the data fields.

Data Fields

As mentioned above, the 3 required fields are the date of the flight, the tail number (which identifies the aircraft) and total flight time.  Of these, only Total Flight Time can be empty.

The aircraft is important enough that it deserves its own discussion (below), but the key thing here is that the aircraft ties a flight to a model, and the model is what ultimately determines a lot about a flight, such as whether it was turbine time, or tailwheel time, or multi-engine time, etc.  So there is no such thing in MyFlightbook as logging "multi-engine" time.  You just log time in a flight, and the system will sort it out into the relevant categories of time for purpose of currency or totals.

Similarly, with one exception, there is no logging of "Day xxx" in MyFlightbook.  Instead, you log total xxx and night xxx.  Day xxx is - by definition - Total xxx minus Night xxx.  The one exception is that you do log full-stop day landings to specify the subset of total landings that were to a full-stop but were not day landings.  (See this entry for a discussion about how to log landings).

One of the most common errors on importing (but which is easily fixed) is indicating night landings without indicating any night flight.  MyFlightbook doesn't do much validation on flights, but that is one check that it does do.  Adding any non-zero night flight (e.g., 0.1 hours) to a record works fine to make this error go away.

The "Route" field contains your departure, arrival, and any intervening airports.  There's no need to specify "From" or "To", because these can be determined automatically from the first and last airport in the route.  (If you already have data that has "From" and "To" columns, that's OK - the system will merge them for you). You can separate airports in the route with anything that isn't a number, letter, or "@" - e.g., "SEA-JFK", "SEA JFK", "SEA.JFK" are all fine ways to indicate Seattle to New York.

Some logbooks contain fields for things like total block time or total duty time.  Because MyFlightbook uses these fields for computations that require a specific start time and end time, you may need to assign a start and end time; the total will then be computed as needed.  E.g., instead of "Total Block Time" of 1.5 hours, you would want a Block Out time of 12:00 and a Block In time of 13:30, for example.  Best to include a full-date (e.g., "2020-05-04 13:30") rather than a naked time, and this time is always in UTC, but if it's a naked time (e.g., "13:30") the system will use the date of the flight (which could be off from the UTC date, since the date-of-flight is generally in local time).

The remaining fields are explained here and should be fairly straightforward.

Aircraft

Each flight is tied to an aircraft by the Tail Number field.  So if the aircraft is in your account, then on import it will be found and matched up to the flight.

But what if the aircraft isn't in your account yet?

Often the answer is "no problem".  As of this writing, MyFlightbook has nearly a quarter million aircraft in its database (and growing all the time), so if N12345 is in the system, but isn't yet in your account, then it will simply pull N12345 into your account.  (By the way, this is why you should use a full tail number, including the country-code prefix.  Using the full country-code also prevents confusion between, say, a US-registered Nxxxx and a Canadian registered Cxxxx.)

That's great, if it's in the system.  But if not?  This is where the "Model" column comes into play.  Here you can put the model identifier for the aircraft.  On import, the system will use the model identifier to suggest a likely model for the aircraft and help you to add it to your account.

The model identifier can be fairly general, like an ICAO identifier - e.g., "C172" for a Cessna 172.  Or, it can be more specific, like "C-172 S".

On import, the system will see the aircraft and try to match it to a likely model:


You can then add it using the suggested model, or click the pencil to be a little more precise:



There are two categories of aircraft, though, for which you don't have a tail number.  The first is flight time that is either long in the past (with forgotten tail numbers), or airline flight where you fly a different B737 every day, or aggregated flight where you have a whole bunch of flights that you've put into a single entry.  MyFlightbook handles these with "Anonymous" aircraft.  The second is sims/training devices.

For anonymous aircraft, you can specify a model and use a pseudo-tail number of "#".  On import, this will try to create for you an anonymous aircraft using the specified model.  But that can be a bit fragile; it's better to use the full anonymous tail number.

To do this, add a new aircraft to your account using the desired model, and check the radio button that indicates it is anonymous.  The system will then show you a pseudo-tail number beginning with "#"; in the sample below, #002377 is an anonymous Boeing 737 and will display as "(Anonymous B737)".

You can then use that pseudo-number as your tail number, and it will be added to your account directly and unambiguously.

I do request that you please DO NOT make up tail numbers.  E.g., don't use "C-172S" as a tail number for an anonymous Cessna 172, since that could conflict with a Canadian aircraft (which begin with "C-").  Use the anonymous method above.

The process is almost exactly the same for training devices.  You can use a model and "SIM" when you import, but again, since the model may not be an unambiguous match to what's in the system, you may have to fix it up at import time.

Instead, follow steps very similar to that of anonymous aircraft, except that instead of checking the button for anonymous, indicate that it is a sim:


Here you'll see a pseudo-tail number that begins with "SIM".  No country-codes begin with SIM, so this can't conflict with a registered flying machine.  So if you were to then use this particular tail number ("SIMB737") on a flight, it would unambiguously reference a Boeing 737 full-flight simulator.  Note that the system assigns sim and anonymous pseudo-tailnumbers; you don't.  If you make these up, you probably won't get what you expect.

Certified sims often have individual registration numbers.  This is best recorded in the "Simulator/Training Device Identifier" field; this also lets you avoid having a proliferation of sims in your aircraft list; see this blog post for more useful information about logging time in sims.

There is one other relatively rare case that bears mentioning.  Sometimes are multiple versions of an aircraft with a given tail number.  For example, an aircraft might be on floats part of the year and wheels part of the year and thus have a float version and a land version, or an old aircraft may have been destroyed and its tail number assigned to a new aircraft.  If you only fly one version of the aircraft and its in your account, then there's no ambiguity: that's the version that gets used.  But if you have both (such as in the float plane scenario), then the tail number column is ambiguous.

There is another column you can use to disambiguate in this scenario: the "Aircraft ID" column.  This references the unique ID for each aircraft in the database, and can be found by clicking on the aircraft that you want in the aircraft list and looking at the URL in your browser's address bar.  At the end of the URL should be something like "?id=xxx".  The "xxx" is the ID of the aircraft.  If you add this column, its value will be used to disambiguate any ambiguous tail numbers.

The Import Process

Once you have your spreadsheet filled out and saved in CSV format, you're ready to import.  Go to "Import" on the "Logbook" tab on the MyFlightbook website.

This is a 4 step process:
  • Upload your spreadsheet
  • Review any aircraft
  • Preview
  • Import
Uploading is pretty straightforward, and the main thing here is that the system checks that it can read the file (i.e., that it is CSV) and that it can interpret the data contained therein (e.g., it has to have a Date column, or it has to be translatable from another known file format).  You also have the option here to ask the system to automatically fill in fields like Night and Cross-Country time for you based on the data it finds in each flight.

If the file is converted from another format, you can see some useful debugging tips in fine print on this screen; click where it says "Notes about file being imported (Click to Show) to see the details:



Next the system will look at all of the aircraft across all of your flights.  Any of these that are in the system but are not already in your account will simply be added to your account.  The remainder will be shown for you to edit/add as appropriate, as in the images above.

Once all of the aircraft are entered, you will be shown all of the flights that will be added to your account, along with any errors.  The errors are shown in a group at the top of the screen, and repeated in context in the list of changes.

If this is your first time importing, then all of your flights will be new and you should see a green "plus" sign next to each flight,  indicating that the flight will be added to your account:


If you are doing a bulk edit (described below) and are updating flights, you'll see a round "update" icon instead:

Each flight that is being updated will also show a list of the changes that will be applied to it.

At this point, though, nothing (other than aircraft) has been changed in your logbook proper, so if there are errors, it's fine to go back to the spreadsheet and fix them there, then start the import process over.

If everything is OK, then you can import after previewing.  Any flights that have errors will go into your "Pending Flights", where you can fix the error and save the flight at a later time; any flights without errors will be entered directly into your logbook.


If you make a mistake on importing, you almost certainly don't want to import the same sheet a second time; if you do, and you see the green plus signs, STOP because you will get duplicates.

What you should do at this point is either do a bulk update (described below and generally the safer option), or do a bulk-delete - which will delete ALL of your flights - and start over.  To do a bulk delete, go to "Account" under the "Profile" tab, and then go to the section titled "Big Red Buttons".

Importing From Other Logbooks

MyFlightbook can read exports from other logbook programs, usually without any modifications.  It's up to you to figure out how to get a CSV dump from the program, though.

So if you have data in CSV from another logbook, my first suggestion is to try simply importing it "as is".  MyFlightbook not only knows about various other formats, but it also has a dictionary of synonyms for many common fields, so it can often pick up most of a file already.

You can also import upcoming schedules from scheduling systems; these will just go into your Pending Flights if they are in the future.

Some programs (like CrewTrac) only use partial tail numbers.  There's a trick for mapping these to the full tail numbers in your aircraft list: add a tag to the private notes for each such aircraft in the form "#ALTxxx#", where "xxx" is the partial tail number.  For example, if you fly N123AB and Crewtrac uses "123", then you'd add "#ALT123#" to the private notes for N123AB.  Then on import, a tail number of "123" will be mapped to N123AB.

Some programs will break out into multiple fields data that is in a single field in MyFlightbook.  A good example would be approach descriptions - one column for each approach in some programs.  I recommend consolidating those using a text concatenation function.  E.g., if row B has Approach 1-4 in columns 18-21, then I would insert a new column and put in that column the formula:

    =TRIM(B18 & " " & B19 & " " B20 & " " & B21)

What this is doing is concatenating the 4 approach description columns, separated by spaces, and then trimming (removing leading/trailing spaces) the result.  You could put this column into the Comments column or (for the specific case of approach descriptions), better into the "Approach Name(s)" column.

Regardless, if you have any questions, don't hesitate to contact me and send me a copy of your spreadsheet; I'm always happy to help whip it into shape.

Bulk Editing

MyFlightbook allows you to bulk edit your flights.  The critical thing here is avoiding duplicates: if you simply upload flights, and then upload the spreadsheet again, you will get duplicates.

To avoid this problem, you must start with a CSV file of your flights that comes from the MyFlightbook site itself.  Go to "Download" on the "Logbook" tab to download a CSV of your flights.

This will contain a sheet very much like the one described above, but with one key difference: there is now a "Flight ID" column, which contains a unique identifier for each of your flights.  The values in this column are assigned by MyFlightbook when they go into the database; you cannot assign them.  But this is the column that lets MyFlightbook match up a row in the spreadsheet to an existing flight.

So now that you have this file, the process is pretty straightforward:
  • (Optional but strongly encouraged): delete any rows from the CSV file that correspond to flights you are not changing.  This will both improve performance and reduce the possibility of inadvertent edits.
  • Make your edits.  Be sure to do this only in columns that are described here; the sheet that you are working from includes some columns that are for information only ("Flight Properties", the hh:mm formatted time fields, and a few other fields) and will be ignored on re-import. 
  • If you have any all-numeric tail numbers (as many military aircraft do, for example), double check that the spreadsheet hasn't re-formatted these as dates or to remove leading zeros!
  • Save/Export the file as CSV
  • Re-import this file
The process from this point is the same as described above, except that when you preview you should see the blue "Update" circle for each flight, and you should see a detailed descriptions of the edits being made to each flight.

2 comments:

  1. Thank you for the post. I import my company schedule monthly after editing the CSV a bit, and find the process very easy and the system works well. Just one thing.

    I had actually requested the auto fill night/x-country from you and you added that promptly! Unfortunately I haven't been able to get it to work with the format that I import from my companies scheduling program.

    Is it possible that I could send you my formatting, or that you can explain a bit more about how the system attempts to auto fill these categories? I.E. should I have the Columns labeled on the first row of my CSV or omit the column entirely? (seems that it might auto fill blank as 0?)

    ReplyDelete
    Replies
    1. By all means, send me what you have and I can figure out why the night/x-country isn't working for you. You can use the contact link on the website and attach the file to that.

      Delete