Importing an Excel Address File

A key feature of the Open Street application is the import of Excel files containing the addresses to be entered on the map. If it is possible to enter addresses using the keyboard, it is by importing files that you can save time and position on the map several tens or even hundreds of points at the same time.

First, it is necessary to create a customer account on the Open Street application. Here you can see the detailed steps of creating and connecting to a customer account.

The different methods of adding addresses

The Open Street application menu consists of several vertical icons. The first character icon allows access to the client space. The second open directory icon adds points to the map. Clicking on this second icon opens the menu «Import addresses » as indicated on the blue banner.

This menu consists of two subparts, which correspond to two methods of entering addresses.

• Keyboard input: enter an address in the text field and validate it with the «Enter» or the «Add» button.
• Import de fichier: three buttons are available.
  • Browse and Send: shortcut buttons to choose a file previously formatted according to the application’s standards (not recommended)
  • Avancé…: advanced window for selecting an Excel file and selecting columns

It is therefore recommended to click on the «Advanced … » button, because it allows to select the columns of the Excel file that constitute the address. In fact, the Excel file can contain different information like names, first names, phones, emails, and this information does not directly serve to position a point on the map. They will nevertheless be stored in memory for display on the card and in the calculation report.

Regardless of the method used to enter addresses, it is necessary to provide complete postal addresses with the street number, street name, postal code, city and country.

Advanced import to check columns

Compatible Formats

This button opens the advanced import window (below). Several formats are compatible:

• Excel xlsx: the default Excel format from Excel 2007 (recommended)
• Excel xls : the old Excel format up to the 2003 version
• LibreOffice/OpenOffice ods: an open spreadsheet format
• CSV: text format, attention, the choice of the field separator often poses problems (not recommended)

You can further consult the exhaustive list of compatible formats.

Creating the Excel file

As part of this article it is the xlsx Excel format that one will remember, it requires the Microsoft Excel software in version 2007 or later. The following is an example of an “OS.xlsx demo” file that was created with Microsoft Excel 2010.

Note that this file contains unnecessary information to position the point on the map, or redundant: INSEE Number, Department, City. Column D contains the addresses, this is the one to indicate to the Open Street application. Line 1 contains column labels and not address data.

Importing the file and selecting address columns

To do this, return to the «Import an address file» window and select your Excel file with the «Browse» or «Select a file» button. The «First-line titles» checkmark is important, indicating that the first line of the Excel file contains column headings and not an address to be positioned on the map. Then click «Preview». Here the Excel file appears in the window.

Note that the first line of the Excel file was detected as not containing an address (check mark «First-line titles») and it appears in bold.

An important phase is the checking in the correct order of the columns that make up the address (one or more). Here we check «Address». If your file is formatted differently and contains the separate address fields in column, then you will probably check something like «Street number», «Street name», «Postal code», «City», «Country» How you formatted your Excel columns. Under these checkboxes, a preview is displayed, it must correspond to a complete postal address, a sign that the application has correctly interpreted your file. If the preview is not satisfactory, uncheck the address fields, and re-check them in the correct order. Then you can click on «Send». The import window closes.

View the points on the map

The positioning of the addresses on the map is called geocoding, it is an operation that can take about 1 second per address, sometimes less. The operation of the geocoding is symbolized by a wheel that rotates in the menu on the left. When the process is finished, you can read «The import is finished» and the last address processed.

On the map we see the blue markers that symbolize the point of the map on which the address is positioned.

Conclusion

We have thus reached the goal of this article. Once the addresses are positioned on the map, you can add, remove, and perform route calculations and optimizations. To do so, please consult the other help pages. You can also send other file formats as explained in this article.