Snakes on a Plane - How to Use Python to Import Flights Into Your Calendars
For the past few years, I have been using App in the Air to track my flights. As I’m extremely paranoid with what applications have access to (and most of the time I travel for business purposes), I’m not in the best position to forward documents or grant random apps access to my inbox.
Instead, I have been storing flight data in a spreadsheet and occasionally forward the respective sheet to an App in the Air e-mail address where it gets parsed, flights are crunched and are then added to the profile associated with the sender of the CSV file.
| Date | Departure time | Arrival time | Duration | Carrier | From | To | Flight number |
|---|---|---|---|---|---|---|---|
| 06/02/2013 | 06:00 | 07:10 | 01:10 | RO | IAS | OTP | 710 |
| 09/02/2013 | 11:00 | 12:05 | 01:05 | RO | OTP | IAS | 703 |
| 11/04/2013 | 17:15 | 18:30 | 03:25 | BA | OTP | LHR | 887 |
| 11/04/2013 | 21:15 | 18:25 | 12:40 | BA | LHR | SIN | 15 |
| 12/04/2013 | 20:10 | 06:55 | 07:45 | BA | SIN | SYD | 15 |
| 22/04/2013 | 16:30 | 09:30 | 08:10 | BA | SYD | SIN | 16 |
| 22/04/2013 | 22:58 | 04:47 | 13:49 | BA | SIN | LHR | 16 |
| 23/04/2013 | 11:00 | 16:15 | 03:15 | BA | LHR | OTP | 886 |
| 23/06/2013 | 06:00 | 07:10 | 01:10 | RO | IAS | OTP | 710 |
| 28/06/2013 | 21:40 | 22:45 | 01:05 | RO | OTP | IAS | 709 |
| 12/02/2014 | 16:00 | 17:30 | 03:30 | BA | OTP | LHR | 887 |
| 12/02/2014 | 18:30 | 15:05 | 12:49 | BA | LHR | SIN | 11 |
| 13/02/2014 | 20:50 | 06:45 | 07:05 | BA | SIN | BNE | 7406 |
Throughout the years, as more and more features have become buried under their paywall, App in the Air was no longer reliably updating flights via CSV. Parsing errors have made it impossible to use use the service so I’ve been looking for alternatives. Out of the apps that allow legacy flight s to be added, one that caught my eye was able to sync previous trips if they are already added to a calendar/ Hint, the app is only available on iOS, wink wink.
Fortunately, my Python-fu is still strong, so parsing the CSV data and regurgitating them as stand-alone iCal entries shouldn’t take too much. We’ll take a little bit of time to properly format the data that goes into the calendar to include some valuable information for the user as well. While airport codes are fine for machines, calendar entries should actually give us insight into what the trip was all about.
Find the city
Let’s start with IATA airport code to city resolution (so you know where you are going to at a glance). We’ll use the airport-info API at rapidapi.com. You’ll need a free account that gives you an API key good for 1K queries for free. These should be enough for a decently-sized collection of trips. So, here we are.
- Download Python3 if you dont have it installed already
- You’ll also need to install the requests module using either
pip install requestsorbrew install python-requests.
Copy the code below, paste it into a file, save it as parser.py next to your flights.csv file and run it with python3 flights.csv (download the code from the button below if you’re really lazy).
import csv
from datetime import datetime, timedelta
import os
import requests
def parse_csv(csv_file):
flights = []
with open(csv_file, newline='', encoding='utf-8') as file:
reader = csv.DictReader(file, delimiter=';')
for row in reader:
flight = {
'Carrier': row.get('Carrier', ''),
'From': row.get('From', ''),
'To': row.get('To', ''),
'Flight number': row.get('Flight number', ''),
'Departure time': row.get('Departure time', ''),
'Arrival time': row.get('Arrival time', ''),
'Booking reference': row.get('Booking reference', ''),
'Duration': row.get('Duration', ''),
'Date': row.get('Date', ''),
'E-Ticket': row.get('E-Ticket', '')
}
flights.append(flight)
return flights
def get_city_name(iata_code):
url = f"https://airport-info.p.rapidapi.com/airport?iata={iata_code}"
headers = {
'x-rapidapi-host': "airport-info.p.rapidapi.com",
'x-rapidapi-key': "your-api-key-here" //rate-limited to 1000 results
}
response = requests.request("GET", url, headers=headers)
data = response.json()
print (data)
city = data['name']
return city
def create_ical_entry(flight):
from_city = get_city_name(flight['From'])
to_city = get_city_name(flight['To'])
ical_data = f"BEGIN:VCALENDAR\n"
# Set start and end date/time
date = flight['Date']
departure_time = flight['Departure time']
arrival_time = flight['Arrival time']
start_datetime = datetime.strptime(f"{date} {departure_time}", "%d/%m/%Y %H:%M")
end_datetime = start_datetime + timedelta(hours=int(flight['Duration'].split(':')[0]), minutes=int(flight['Duration'].split(':')[1]))
ical_data += f"BEGIN:VEVENT\n"
ical_data += f"UID:{flight['Flight number']}\n"
ical_data += f"DTSTART:{start_datetime.strftime('%Y%m%dT%H%M%S')}\n"
ical_data += f"DTEND:{end_datetime.strftime('%Y%m%dT%H%M%S')}\n"
# ical_data += f"SUMMARY:Flight N° {flight['Carrier']}{flight['Flight number']} from {flight['From']} to {flight['To']} ref: {flight['Booking reference']} \n" *
ical_data += f"SUMMARY: {flight['Carrier']}{flight['Flight number']} {flight['From']} {flight['To']} \n"
ical_data += f"DESCRIPTION:Please note that the schedules of this booking are local times. Your calendar application automatically adjusts those hours to the time zone schedule.\\n" \
"------------------------------------------------------------------------------------------------------\\n" \
f"Route: {from_city} {date}\\n" \
f"Carrier: {flight['Carrier']}{flight['Flight number']}\\n" \
f"Departure: {from_city} {flight['From']} ({date} {departure_time})\\n" \
f"Arrival: {to_city} {flight['To']} ({date} {arrival_time})\\n" \
f"PNR number: {flight['Booking reference']}\\n" \
f"Reservation code: {flight['Booking reference']}\\n" \
"Traveler: Bogdan BOTEZATU\\n" \
"Arranger: Bogdan BOTEZATU\\n" \
f"Duration: {flight['Duration']}\\n" \
f"Electronic ticket issued: {flight['e-ticket']}\\n" \
"------------------------------------------------------------------------------------------------------\\n" \
"We wish you a nice trip!\\n"
ical_data += f"LOCATION:{from_city} {flight['From']} {to_city} {flight['To']}\n"
ical_data += f"END:VEVENT\n"
ical_data += f"END:VCALENDAR\n"
return ical_data
def main():
csv_file = 'flights.csv' # Change this to your CSV file name
flights = parse_csv(csv_file)
for flight in flights:
departure_datetime = datetime.strptime(flight['Date'], "%d/%m/%Y").strftime("%A, %d. %B %Y")
file_name = f"Bogdan BOTEZATU {departure_datetime}_{flight['To']}_{flight['Booking reference']}.ics"
ical_data = create_ical_entry(flight)
with open(file_name, 'w') as file:
file.write(ical_data)
if __name__ == "__main__":
main()[*] I have sloghtly modified the code because I talked to some app vendors and said that I should keep the event sumamry short and to the point (uniterrupted flight number, start airport and destination airport). Anything fancier than that risks to confuse parsers and have the event skipped.
The script will generate individual iCal entries that you can then add into a calendar of your choice.
This is how it looks like inside the calendar. It has al the information available at a glance and also includes a lot of flight data inside the event.
Yeah, but why?
Security. The application reads the calendar locally. No data gets sent out of your device, so that’s one thing less to worry about.
Security again. You can grant the app access to one calendar only.
Easy to automate. If you have the spreadsheet already compiled, most of your work is already done.
Convenient. Once you have your trip dat ain one place, it is much easier to export, back up or move to another app that supports importing flights from a calendar.
