One of the many ways that Locate2u allows you to get stops/shipments/customers into your account is via importing data from a spreadsheet. Often you will have this data in a spreadsheet already and want to get it into Locate2u on a regular basis. Rather than having to manipulate the data in your spreadsheet each time you do an import to match a predefined import format, we allow you to map the data in your spreadsheet to fields in Locate2u and save this configuration, thus allowing you to simply upload your data in whatever format you have it in.
1. Go to the Team > Settings > Import Mappings page.
2. The first step is to upload a sample file. This may or may not have data – it doesn’t matter, we only need the column headers for now.
3. We now know the structure of your spreadsheet, so you can now map columns in the spreadsheet to fields in Locate2u.
Format Strings
Sometimes you need to concatenate multiple fields, or transform field values in your spreadsheet. For example, Locate2u has a single Contact Name field, but your spreadsheet may have two fields – First Name and Last Name. Custom mappings allow you to combine both these fields into a single field and map that to the Contact Name field.
When you select the Custom Mapping option for a field, you will find a Format String field in the dialog that appears. You’ll then need to write a format expression that can be used to combine the fields. For the purpose of this example, let’s say our spreadsheet has 3 columns:
A – Address
B – First Name
C – Last Name
To start with, let’s just map the First Name column to our Contact Name field. The syntax to reference a column in the spreadsheet is the column, surrounded by braces (curly brackets).
Format String: {B}Let’s now write a format string to concatenate both the first and the last name fields:
Format String: {B} {C}You can even add in your own text to be included in the output string. For example, with inputs of Bob (column B) and Smith (column C), this will output “Bob with last name Smith”:
Format String: {B} with last name {C}There are many other options and capabilities with this feature, such as using regular expressions, extracting part of the text value, conditional logic, etc. If you have a scenario not documented above, please contact our support team and we’ll be happy to provide support in setting this up.
Advanced: Options
There are some additional options you can set for the mappings, though they are only available via the JSON editor currently.
"options": {
"hasHeaderRow": true,
"dateFormat": "dd/MM/yyyy"
},Options include:
hasHeaderRow – set to true by default, if changed to false, it will read data starting from the first row in the spreadsheet, as it will assume that there’s no header row in the spreadsheet.
dateFormat – when mapping a text value in the spreadsheet to a date field (e.g. trip date), this format will be used to parse the value and convert it to a valid date. In the example above, it expects dates to be in the English/Australian format. A sample US format might be MM/dd/yyyy.