Question:
I have a fleet of 50 vehicles, and we recently decided to change their labels using a better naming convention. How can I easily update the vehicles’ labels in a batch process as opposed to editing each vehicle one at a time?
You can add or edit your portal data ad-hoc or via spreadsheet import. Spreadsheet import is helpful for performing these types of batch functions. If you use the Spreadsheet Import tools, it’s ALWAYS a good practice to first export the data for safe-keeping so that you can restore if you make a mistake in your import. Or in the case of updating labels, we need to start with the existing data that we want to update.
Formatting Tips
Before starting the import process for a particular spreadsheet, become familiar with the following formatting tips:
- The maximum number of rows spreadsheet upload accepts is 1,500. If your spreadsheet has more than this number, it is recommended to break it up and perform multiple uploads for each group of 1,500 until it is complete.
- Updating information via spreadsheet upload requires that the spelling/punctuation/spacing for the “Reference” unique identifier matches exactly the data in the portal. Here are a couple examples:
- If you are updating vehicle information, the VIN or serial number must be exactly what is in the system for reference. That includes spacing, dashes etc.
- If you are updating an Attribute called “Car Color,” your spreadsheet header cell, which has all the attributes listed beneath it, must be spelled/formatted exactly the same as the Attribute in the system. If you accidentally titled the spreadsheet column “Car Colors” with an added “s,” the upload will not be successful.
- Spreadsheet upload accepts either CSV or XLS files. There should not be multiple tabs or formulas in your excel spreadsheet, and CSV format is preferred as it is usually the cleanest data format with standard text.
- Change the headers in your spreadsheet to match the headers shown in the CSV Example.
- Certain types of data, like usernames or VINs are considered unique. If your spreadsheet has duplicate data rows, like two usernames listed and each with a different phone number, the spreadsheet upload will overwrite the data with the last row of data. Example:
- There are unique characters to signify separation between multiple items within a cell. For instance, a user may need access to multiple vehicle hierarchy nodes.
- ~ is the delimiter for full path for hierarchy. Example: Company Name~East~Northeast. The ~2 in cell for hierarchy specifies the node depth. That user would have access to the final Node “Northeast” in the hierarchy. Use the ~ when the nodes are not unique names or there are multiple trees with non-unique node names.
- : is the delimiter for multiple assignments. Example: Vehicles:Drivers. This user would be designated permissions to view vehicles and drivers.
- ; is the delimiter for multiple items. Example: Red;Grey. Attribute upload for a vehicle that has a car color of both red and grey.
- If the nodes are unique and not repeated across multiple trees, no delimiter would be needed. Or, if only one item is listed in a cell, no delimiter is needed.
- In the case of hierarchy paths, if there are multiple paths listed, the delimiters can be used together. Example: East~Northeast:East~Southeast
- For Hierarchy Update and Attribute Update imports, the “Hierarchy Tree” and “Attribute Name” column cell headers must be changed to match the data in the portal in order to properly update those specific areas within the system. The rest of the headers in your spreadsheet can stay as listed in the example. Hierarchy Example:
Attribute Example:
- For Hierarchy Update and Attribute Update imports, only one column at a time can be updated. For example, you would perform the update process twice to update the Car Color then the Car Type attribute. Choose -–Ignore– for one of the attributes during each upload so only one is used at a time. The same rule applies for hierarchy trees.
- For Hierarchy Update and Attribute Update imports, the “Reference” and “Update” drop-downs must be different from the traditional selections for all other spreadsheet updates; they are actually switched. You would select the Attribute or Hierarchy Tree column as the “Reference” (like the Driver Attribute “Driving Skill”) and a unique identifier column under the “Update” area (like “Driver Full Name”).
Step-by-Step Process
In this example, we’ll stick to the above scenario of updating vehicle labels. Refer to the next section for step-by-step instructions related to other spreadsheet actions.
To update vehicle labels via spreadsheet:
-
- From the portal, hover over the Account menu, click Manage Vehicles, and then click Update/Export items using a spreadsheet.
- From the Export Details to Spreadsheet area, choose to export Vehicle Labels/Details to CSV (comma separated values) format, and click Save To File. (We’ll come back to this file later.)
- Download a CSV Example template (#1 | #2 | #3 ) next to the Update Vehicle Labels spreadsheet action.
- Within the spreadsheet, column names appear in Row 1 and an example record appears in Row 2. Using the file you just exported, replace Row 2+ with your own data. Because the columns will be different than the file you’re copying FROM, you may want to copy/paste one column at a time into the Example spreadsheet.
Field(s) in the Reference column on the Update/Export page are required. Often the system requires just one reference, but sometimes the import may require more than one.
Refer to the above general formatting tips while preparing a spreadsheet for import.
- Save the spreadsheet with your changes.
- From the portal in the Spreadsheet Actions grid, click the Update Vehicle Labels option.
- From the Import Step 1: Upload CSV or Excel File grid, click Choose File.
- In the Open window, browse to the location where you saved the example file for import, and double-click it.
- Click Upload Now.
The Import Step 2: Define Columns grid appears. This grid allows you to see how your spreadsheet column names (shown in the drop-downs) will be applied to the appropriate fields in the portal (shown in bold). Select the Reference item and match it to the Reference column in your spreadsheet. No changes will be made to the data listed in the Reference column; this will only be used as the identifier to update auxiliary information. Set any fields marked “IGNORE” to the appropriate column name if they will be updated. If you don’t want to add/update those columns, leave them set to “IGNORE.” Some spreadsheets don’t have columns named specifically for “Update” purposes like our example (“Vehicle Label Update”). In those cases, search for the Update section within the drop-down of the field you want to update.
- Click Preview the Import.
The Import Step 3: Preview Import Data grid appears. This grid allows you to verify the data that will be imported. Valid values for reference fields will be marked with a green check box. The next action will overwrite/add data in the portal, so it’s very important to verify that columns are being mapped to the correct areas within the portal. If you haven’t already exported your current data as a backup, do so now!
- Click Proceed with Import.
The Import Step 4: Import All Data grid appears. This grid provides a summary of the import results. If any rows were skipped, they will be listed here with the reason they were not imported.
- From the menu at the top of the page, click Vehicles to view the updated vehicle labels.
Other Spreadsheet Actions
For other common spreadsheet actions, click the available links for step-by-step instructions (not available for every action):
Action | Reference | Update |
---|---|---|
Create New Users | Username and Password | First Name, Last Name, Full Name, Email Address, Address, City, State, Zip, Country, Phone Number, Timezone, Vehicle Group Permissions, Hierarchy Permissions, Hierarchy Permission Types, Landmark Group Permissions, Menu Access Template, User to Clone |
Update Users | Username | Password, First Name, Last Name, Full Name, Email Address, Address, City, State, Zip, Country, Phone Number, Timezone, Vehicle Group Permissions, Hierarchy Permissions, Hierarchy Permission Types, Landmark Group Permissions, Menu Access Template, User to Clone Dashboard |
Update Odometer Waypoints | VIN or Vehicle Label or Serial Number | Odometer and Timestamp |
Vehicle Group Membership | VIN or Vehicle Label or Serial Number | Vehicle Group |
Update Vehicle Labels | VIN or Vehicle Label or Serial Number | Vehicle Label Update, VIN Update, Color, License Plate, License Country, License State, Odometer, Make, Model, Model Year, Fuel Type, Fuel Capacity, Idle GPH, Engine Size, Hide Location, Run Time, Run Time Timestamp, Vehicle Icon |
Register New Vehicles | VIN and Vehicle Label and Serial Number | Color, License Plate, License Country, License State, Odometer, Make, Model, Model Year, Fuel Type, Fuel Capacity, Idle GPH, Engine Size, Hide Location, Run Time, Run Time Timestamp, Vehicle Icon |
Create Drivers | Driver Full Name or Last Name or Reference ID | First Name, Timezone, Country, Email Address, Phone Number, Alert Preferences, Driver ID, Driver Group |
Update Drivers | Driver Full Name or Last Name or Reference ID | Reference ID Update, First Name, Last Name Update, Timezone, Country, Email Address, Phone Number, Alert Preferences, Driver ID, Driver Group |
Delete Drivers | Driver Full Name or Last Name or Reference ID | Reference ID Update, First Name, Last Name Update, Timezone, Country, Email Address, Phone Number, Alert Preferences, Driver ID, Driver Group |
Assign Drivers | VIN or Vehicle Label or Serial Number | Driver Full Name, Last Name, Reference ID |
Hierarchy Update | Tree Node or Node Path | Tree, Parent Node, Driver Full Name, Last Name, Reference ID, Landmark, Username, VIN, Vehicle Label, Serial Number |
Attribute Update | Attribute Value | Attribute Name, Driver Full Name, Last Name, Reference ID, Landmark, Username, VIN, Vehicle Label, Serial Number, Hierarchy Node |
Assign Inputs | VIN or Vehicle Label or Serial Number | Definition, Input Bit, Output Bit, Voltage |
Update Vehicle Settings | VIN or Vehicle Label or Serial Number | Report Interval, Ignition Off, Motion-Based, False Idle, Is Trailer, Trip Setting, No Idle Trips, No Force Idle, Slow Moving, Even Slower, Save All Idle, Use Diagnostic Odometer, Garmin |
Create Service Logs | VIN or Vehicle Label or Serial Number | Performed at Miles, Performed at Hours, Service Cost, Service Date, Service Notes |
Create Service Reminders | VIN or Vehicle Label or Serial Number Service | Label, Next Service Miles, Next Service Hours, Next Service Date, Interval Miles, Interval Hours, Interval Days |