Excel Import Tips & Tricks

How to minimize and identify errors in Excel import

Last updated on November 8th, 2021

ZIP Code Format

Sometimes ZIP codes starting with 0 are automatically removed from the cell and will lead to errors upon doing an import. In order to make sure that none of the data in the ZIP code is removed, please select the ZIP column and then choose the "TEXT" format before filling in the cells. 



Location ID Duplicates

It is not possible to upload locations that have duplicate location identifiers. In order to quickly Identify duplicate values. You can quickly highlight them by using the conditional formatting feature as seen in the picture below. Simply select the LocationID column and then perform the conditional formatting.

                                                 



Short & Long Description Length

The maximum amount of characters for Short & Long description are 200 & 1000 characters respectively. Should you exceed this limit, you will get an error upon importing the file.  

If you would like to identify which cells have more than 200 characters, you can use conditional formatting to highlight the cells by following the steps below:

  • select First cell in short description
  • Conditional formatting --> New Rule
  • Style:Classic  then Select "use a formula to determine which cell to format"
  • Enter following formula =len(A1)>200

                       

Now simply use format painter option to fill the complete column (all individual cells) with this formatting

       

Another option is to add a column and include the formula =len(A1). You can then drag & drop the formula on all the cells and then filter for values>200



Address Display & Auto-Sync

If these fields are blank upon import, you will got a notification message upon import. Please insert True or False in these columns always when doing the import.

True: Auto-sync On, Address Display

False: Auto-sync OFF, Hide Address

Another option would be to select "skip these column" while doing the import. 

           



Modify location's coordinates

If you need to change the coordinates, you will need to use the Import feature. Please follow these steps: 

  • Click on the Locations tab on the menu
  • Get the location data export file, by clicking Export
  • Update the latitude and longitude for the affected location(s) by updating the columns "lat" and "lng" in the export file and save it
  • Click Import and upload the updated fill

Please make sure that the dot " . "  is used when filling the coordinates



Opening Hours Formatting

To quickly identify/convert whether you are following the correct format for the opening hours etc: Mo=09:00-12:00&13:00-16:00; is to use the find&replace function in excel.  

  • Replace the hours: If your data is structured in AM/PM format- you can search for example 02:00PM and replace with 14:00 
  • Make sure "," are not there  Find: , replace with: ;
  • Make sure no "Empty" characters are there: Find: (enter space) , replace with: (Leave Blank)


Best Practices

Account Creation

With the import function, you have the possibility to directly create accounts in the platform. (No need for them to be created manually first in the platform)
However, it is important to note that the account will be named as the first location under that account, so it is recommended to enter the platform and change the name after importing to keep the account clean.

Photos

If you would like to choose the same logo or picture for all locations, you can add the photos manually in the platform for one location, do an export and then copy-paste the URL to all locations.






    

Was this article helpful?

Save as PDF