Call us: 866-848-5919   7 AM - 4 PM, Mon - Fri, Pacific Time
OnTime Courier Software on Facebook OnTime Courier Software on Twitter OnTime Courier Solution on LinkedIn OnTime Courier Software on Google+

Importing Data

Make quick work of importing bulk data into OnTime with these tools.

Download video:  MP4 format


In this video, we're going to show you how to import data from a file into OnTime. There are many occasions where you may want to do this. For instance, if you're just starting with OnTime, you may need to move, in bulk, all of your data from another software into OnTime. Or if you have customers that are sending you orders in advance, an ASN or advanced ahipment label, you should be able to take that file and import it quickly into OnTime so that you don't have to enter those order in manually. In either case, OnTime has a utility called Import Data that quickly allows you to import data into the system. I'm going to show you how to do this in the OnTime Management Suite program. I should mention, though, that this same procedure is available in the OnTime Dispatch program. So, either of those programs will work and use the same steps to import the data.

From with OnTime Management Suite, I'm going to go to the Data tab at the top and choose Import Data on the left hand side. Once this loads up, the first thing it asks me for is the file that I want to import. OnTime does support a number of file formats: Excel files, both the older XLS and the newer XLSx, we also support comma separated value files, CSV. If you can get your data into either one of those formats, which are so common that they are almost standard these days, you wil lbe able to import that data into OnTime.

I'm going to choose a file that I have handy called OnTime Locations. This is an Excel file and it just has a list of addresses. Because this is an Excel file and Excel files can have multiple worksheets in them, it wants me to tell it which worksheet. I've only got one here, so I'll select that. The next thing I want to choose is where I want to import the contents of these files to. It could be Customers, Locations, Orders, Orders with Customers (that's handy in the case of an ASN), or Zones. In this situation, I just want to import Locations, so I'll choose that.

In Step 2, it's showing me a list of fields that I need to map. There's two columns in this grid: the left column shows the field as it exists in OnTime, the second column shows Imported Fields that are going to be coming from columns in my file. Notice what happens if I click on any one of these cells in the right hand column: I can do a drop-down and it shows me the column names of all of the fields in my imported file. What I need to do is map the corresponding field from my file to the field that it needs to go to in OnTime. For instance, the Name, we'll choose Name. I have a Contact, so go to Contact. And Address Line 1. In my file, I don't have Address Line 2, and that's ok, I'll just leave that blank. That means no information will be imported into that field. Continue to City and choose City, State and so on. If, in my Excel file, I have my column names match up with the OnTime field names, I can take a shortcut in this mapping and right-click anywhere in this right column and choose Auto Map Fields. That will go through my Excel file and see if it can find any matches there, making quick work of mapping these fields out.

Looks like I have done what I can here, so down below I am going to choose Import dates as. This is very important to tell OnTime how you want it to import dates. In the case of locations, this isn't too big a deal because I have no dates and times associated. But, if I were importing orders, those most certainly will have dates and times on them and I want to be able to import those correctly. If my customer sending me the ASN file is in a different timezone, I need to compensate for that correctly so that the dates are stored in the right timezone, the right format. In this case, I could choose to import those as UTC, Greenwich Mean Time, or my local timezone here. We'll just leave that UTC because it doesn't matter for locations.

I'm going to progress to Step 3, which is to import the items. I click on Import Now. It is going to tell me that not all fields have been mapped, as a result those fields will be empty in OnTime. What it's talking about is Address Line 2, Country, Phone, Email, Category. Those particular fields, I didn't have any information for in my Excel file. Those fields are going to be left blank in those records in OnTime. That's OK, so I'll click Yes to confirm that. It will now tell me that it's found 199 records within the Excel file to import. When I hit OK, it will import those.

When it's done, notice how quicky it went, it does tell us that all 199 records were imported and I can view a log of those. If there was any problem importing any of those records, the log would show why and how to fix it. Common problems might be that there was too much data in a particular field and, as a result, it skipped that record. Somethign to note is that this is done transactionally. What that means is, I had 199 records to import in this example. If even one of them had a problem and couldn't be imported, we roll back or cancel the entire import process. We don't want you to be left in a state where a few items weren't imported, so when you go back to import the second time, you get a bunch of duplicates. So, that doesn't happen here. In this case, what happens is OnTime rolls back the transaction so that you can fix the problem in your source file and then try to import again until it's successful. So, it's either all or nothing when it comes to importing your data.

If you have any questions about how to import your data or the best format to put it in, feel free to contact OnTime Customer Support. We're always there to help and answer those questions.

You guys rock!

Michel Charlebois | ProActif Transport