Import Trips from an Excel spreadsheet Updated Aug 8, 2008
Version 12.9

TripTracker can easily import a batch of reservations from an Excel spreadsheet. A predefined template has been provided. If other formats are needed, let us know and we can custom-program one for you, for a negotiable fee. In Version 12.8, you can create & update your own templates. Just follow the instructions at the bottom of this document.

Publish the Excel spreadsheet format:

  1. Send the spreadsheet template (ie. ResvTemplate.xls or your own customer template) to your corporate or group charter customers. This can be found in the Tripwin folder. If you have a single-user license, then it will be in C:\Tripwin, otherwise it will be on your server (typically drive T:\).
  2. If you can not find the file, download it here.
  3. There are 3 sample reservations already in this file which show how each field should be entered. If the client sends these back in their file, you can just skip them on the import. Or, feel free to delete them in the original file.
  4. If the trip has multiple segments or passengers, leave the first column blank for each 'extra' row. This will cause the row to be included with the previous row's trip.

Import formatted Excel spreadsheets:

Setup:

  1. Once the client emails back the spreadsheet, save the attachment to your computer.
  2. In TripTracker, go to Utilities and choose 'Import Reservations from Excel Spreadsheet'.
  3. When the screen displays, make sure the 'Import Set' says 'IMPORT RESV' (unless a custom template has been setup for you).
  4. Make sure the File Name points to the spreadsheet you just saved. You can either type in the full file name, or click the Browse button to select it.
  5. Enter the Customer Number you want assigned to all trips in this spreadsheet.
  6. Make sure the Header Row, Last Col, and First Data Row fields match the spreadsheet. For example, the 'IMPORT RESV' template should have Header Row of 1, Last Col of 'T', and First Data Row of 2.

Process Each Trip:

  1. Click the 'Start Import' button.
  2. This will step you through each row from the spreadsheet.
  3. For each trip in the spreadsheet, the 'Import Reservation Values' screen will be displayed, showing all the values from the spreadsheet.
  4. You can change any field value you want before accepting the trip.
  5. Note that you can scroll down to see more fields.
  6. Once all data has been validated, you can press 'Accept & Save' or 'Reject' each trip.
  7. Once accepted, the Quick Reservations form will be displayed with all values from the trip.
  8. Remember that if any field's value isn't valid, it will not let you off this screen until you fix the problem.
  9. If you 'Reject' a trip, the trip is bypassed and no confirmation is sent. If the client requires one, then 'Accept' the trip so you get the Quick Reservations screen, then send a cancellation confirmation from there and delete or cancel the trip.

Restart the Import:

  1. If you press the 'Quit Import' button, it will abandon the import at the current record. All previously imported trips will still exist.
  2. You can continue importing later by clicking 'Start Import' on the same spreadsheet, and just click 'Reject' for all previously imported trips.
  3. Note that it will not restart in the middle of a multiple segment trip. Just delete the partially accepted trip from before, and accept this entire trip.

Things to remember:

  1. If you are using Spot Times, make sure the import set uses the Pickup Time, not the Start Time. Go to the Import Set (ie. "IMPORT RESV") and click the new button 'Edit Field Layout'. On the 'Pickup Time' row, click the 'Trip Segments' button, and then choose 'PickupTime1' from the list & click OK. This will switch the fields.
  2. Make sure you tell your clients what they can enter in the 'Car Type' field. This must exist on your Vehicle Groups (e.g. 'SEDAN' or 'VAN'). Otherwise you'll have to change every imported record. If you don't do either, the import will not work!
  3. Make sure the 'previewed' airline codes are the 2 or 3 digit codes (e.g. 'US', not 'US Air'). This could cause you to lose the flight info from the trip. Also, make sure there is a space between the Airline and the Flight No. (e.g. 'US 123', not 'US123').
  4. Sometimes fields are too long and you can't leave the record. Typically this is either a phone number (max 12 chars), or the flight's city. Just shorten the data on the screen.
  5. For multi-passenger trips, if you accept any part of the trip, make sure you accept the entire trip. Otherwise, when you restart the import it will think the trip has already been processed.
  6. Sometimes Excel gets stuck & won't close the file. Usually it opens a window stating that some columns have weird data and makes you say you want to save it, although this window could be in the background. Just click on the open 'Microsoft Excel' on the taskbar.
  7. If you are using 24-hour time, you might not see the times being entered, since it is putting them in the 12-hour fields. If in doubt, just hit the PgUp key (and if it changes records, then the PgDn key) to refresh the screen.

Create your own Import Set:

  1. Create a new spreadsheet containing your desired format. Or, have your clients send you one in their format.
  2. In TripTracker, go to Utilities and choose 'Import Reservations from Excel Spreadsheet'.
  3. Click 'Add New Import Set' to give it a new name.
  4. Enter the Customer ID and the File Name
  5. Click 'Get Fields from File' to grab all of the existing spreadsheet's headings into this set.
  6. Click 'Edit Field Layout' to associate each heading with a Reservation or Trip Segment field. You can resequence the columns, change the fields, etc.
  7. If you want to delete a column, click on it, then press Ctrl-Del. Make sure you renumber the columns so you don't have a hole.

If you do not have Version 12.8 of this utility, you can download the 3 needed files here:
A. ImportResv.FDL
B. ImportResvDet.FDL
C. ImportResvFields.FDL

Need help? Just email us at support@triptracker.com