Import

Import for the Location manager has been updated to run faster with new tools for the Power Add-on  designed for Power users (for info see new SLP documentation site).

Bulk Data Import is the feature included with the Pro Pack add-on and the Power Add-on that allows you to upload multiple locations via a CSV file.    Many applications can export data in CSV format.  The most common methodology is to format the data using Excel.   However, we prefer Google Sheets to upload the excel export files.   Google Sheets is better equipped at handling special characters, converting them to proper UTF-8 characters which will import to WordPress with fewer problems.

Once the Pro Pack add-on is installed you will see an “Import” option  under the Store Locator Plus Locations tab.

4.4 ProPack import back end

ProPack-4.4.01-bulk-actions

Preparing For Uploads

In order to process bulk uploads you will need to install the Pro Pack add-on.   With Pro Pack installed you will see the bulk upload entry at the bottom of the add locations form.

In order to perform the upload you will need to have proper write permissions in the default WordPress uploads directory, typically at ./wp-content/uploads.    Store Locator Plus will need to create the sl-uploads sub-directory there so make sure your WordPress site has permission to create directories within the uploads folder.  This is the default setting on most installations.

Skip First Line

This option will ignore the first line of the CSV file, useful for spreadsheets with the column names in the first row.

First Line Has Field Names

This option allows the CSV file to have a more flexible format for the column order.    Columns can appear in any order when this box is checked, provided the first line of the file contains approved field names.

EXAMPLE of an excel sheet header rows:
excel or googlesheets format

Example of a CSV file format ready for upload:
csv file example

First Line has field names file setting
First Line has field names file setting

All fields are short text fields, up to 255 characters, unless otherwise noted.

The header text is NOT case sensitive.

The column names can be the full field name or a short version of the field name by dropping the leading sl_.

ID or sl_id – this should match an existing Store Locator Plus store ID (from a CSV export)

Store or sl_store

Address or sl_address

Address2 or sl_address2

City or sl_city

State or sl_state

Zip or sl_zip

Country or sl_country

Tags or sl_tags

Description or sl_description – long full text description

URL or sl_url

Hours or sl_hours – long full text description

Phone or sl_phone

Email or sl_email

Image or sl_image

Fax or sl_fax

Latitude or sl_latitude – a standard latitude floating-point number

Longitude or sl_longitude – a standard longitude floating-point number

Private or sl_private – a boolean field, valid values are “1” or “0” (zero)

Neat Title or sl_neat_title

Add On Pack Import Fields

Contact Extender

Identifier – a unique location identifier from an external database that can be used to update existing locations in Store Locator Plus

Contact

First Name

Last Name

Title

Department

Training

Facility Type

Office Phone

Mobile Phone

Contact Fax

Contact Email

Office Hours – long formatted text

Contact Address – long formatted text

Notes – long formatted text

Enhanced Results

Featured – valid values are “1” for featured and “0” (zero).

Rank – an integer from 1 (highest rank/top of list) to 999999.  0 = no rank.

Location Extender

Introduction – long formatted text

Year Established

County

District

Region

Territory

Tagalong

Category or categories – contains a comma-separated list of Tagalong/Store Pages category names or category slugs.  You define parent/child relationships for hierarchical categories using a double colon such as Facility::Showroom.

Skip Geocoding

If the latitude/longitude columns have a number in BOTH columns the built-in geocoding system will not be triggered during location loading for that location.

Load Data

If checked use the faster MySQL Load Data method of file processing. Only base plugin data can be loaded, see the approved field name list.

 

Duplicates Handling

The duplicate records processor has multiple options for determining how to handle duplicate records.   A record is considered a duplicate entry when either a valid location ID has been provided in the import file that matches an existing location in the Store Locator Plus database or when the name and complete address match an existing record in the database.    The address consists of the address + address 2 + city + state + zip + country fields.

When a duplicate address is found it can be handled one of three ways:

Add

Add the record even if it already exists in the database.

Skip

Do not add the duplicate record, ignore all other data changes.

Update

Update the existing record, updating secondary fields for the location.

If you are updating the store name or any of the address fields the location ID must be provided

If you have Contact Extender installed and the ID column is blank the Identifier column will be used in place of the Store Locator Plus ID field to location matching stores even if the name or address has changed.  This column is meant to assist in updating records imported from third party applications external to your WordPress Store Locator Plus location data.

 

File Import

This section provides more control over recurring imports from a full url where you CSV file resides and allows flexibility for scheduling the time for daily imports.
WordPress cron is not exact, it executes the next time a visitor comes to your site. WordPress times are UTC/GMT time NOT local time. Set to none and leave the URL blank to clear the cron job. Example: 14:25. (Default: empty = do not run daily cron)

Older screen shot of ProPack , Allows you to Schedule File Imports from a URL site where the CSV file resides
Allows you to Schedule File Imports from a URL site where the CSV file resides

Import Tips

  • Use a proper Comma Separated Value (CSV) file format :CSV File FormattingMake sure the data does not contain characters that are not supported by your selected character set.   Loading the CSV into Google Docs Sheets and making sure you can see all the data is a good first step.   Downloading as CSV from Google Docs is a great way to “cleanse” the data of unsupported characters.
  • Checking your server, PHP and MySQL especially, to ensure the character set is correct will go a long way toward importing special characters like registration characters.   I prefer to use UTF-8 throughout.

Large List Imports

Large lists, typically those with more than 25,000 locations, require special consideration.  Here are some highlights to maximizing the import performance.

  • Check your php.ini post_max_size setting if doing a direct file import versus a cron URL based import. post_max_size is typically set to 8 (MiB) on most servers.   This is typically enough for around 25,000 locations but it depends on how long your descriptions are and how many data fields you have filled out.   SLP 4.2.41 will warn you if you try to upload a file larger than your post_max_size limit.
  • Check your php.ini memory_limit setting and make sure it is large enough to handle the WordPress overhead plus the size of your CSV file times two.   The WordPress database interface and the CSV file processing will consume lots of memory.  The more plugins, widgets, and advanced theme features you have more more memory WordPress will use and the more PHP memory will leak over time. A setting of 256M is enough for approximately 15,000 locations.
  • Check your wp-config WP_MEMORY_LIMIT.   You may need to add this define to wp-config.php.  define(‘WP_MEMORY_LIMIT’ , ‘256M’).  The number needs to be equal-to or less-than the php.ini memory-limit.    It is the WordPress-specific memory limit and works with php.ini memory_limit.
  • Check your wp-config WP_MAX_MEMORY_LIMIT.   You may need to add this define to wp-config.php.  define(‘WP_MAX_MEMORY_LIMIT’ , ‘256M’).   This is the WordPress admin interface memory limit and works like WP_MEMORY_LIMIT for admin pages.
  • Set Duplicates Handling to Add especially if you know you do not have duplicate locations in your data.  SLP 4.2.41 further improves the performance when using ‘add’ mode by eliminating extra data reads from the database.
  • Set Server-To-Server speed to Fast under the General Settings tab unless you are on a shared host or experience a large number of uncoded locations during import.
  • Set the PHP Time Limit to 0 (unlimited) under the General Settings tab.   For hosting providers that allow your web apps to change this, the unlimited value will let the import run to completion.
  • Keep in mind Google limits you to 2500 latitude/longitude (geocoding) lookups per 24 hours per server IP address.  If you are on a shared host you share that limit with all other sites on that host.
  • If you purchased a Google Maps API Enterprise or Business license key ,  this is NOT the same thing as the “sign up for free” API service and pay for overages, enter your assigned Google Maps Enterprise client ID (gme-yourname typically) and your Google Private key.  This gives you 10 location geocodes per second and 100,000 per day and is FAR faster than the free API service.

Load Data

This feature uses the MySQL Load Data command is is 10-50x faster than WordPress / PHP CSV file parsing, with the typical Google Geocoding limitations on performance and record counts if you are not supplying the latitude/longitude data.   This feature will only import basic location data.  It does not import extended data fields or Tagalong category data.     If you use this along with having pre-entered latitude and longitude values you can import 100,000 locations in less than 10 minutes on a basic web server.   The column headers should be included in the file and should match the basic fields.  You do NOT need to include all columns.

You can build a CSV-import ready export directly from MySQL if your MySQL user account has the GRANT FILE privilege on the WordPress Store Locator Plus file.    You’ll typically need to login to MySQL from an administrative account to grant file to the WordPress or other user as follows:


GRANT FILE ON wordpress.* TO 'wpuser'@'localhost';

You will replace wpuser with your WordPress username and ‘localhost’ with your MySQL server hostname or IP address.

With the file privilege set you can then export a CSV file for import with the following MySQL command:


SELECT * INTO OUTFILE 'geocoded_locations.csv'

FIELDS TERMINATED BY ','

OPTIONALLY ENCLOSED BY '"'

LINES TERMINATED BY '\r\n'

FROM wp_store_locator;

This will create a geocoded_locations.csv file in the default MySQL WordPress files location, typically /var/lib/mysql/wordpress/ on a Linux system.    You can specify a fully-qualified URL for the output file.  The details on where the file is written by default and how to specify the full URL will depend on your web server settings.

You can now import the basic file using the Store Locator Plus Pro Pack by using the Load Data option with “first line has field names” and “skip geocoding” if you have the following CSV file header:


sl_id,sl_store,sl_address,sl_address2,sl_city,sl_state,sl_zip,sl_country,sl_latitude,sl_longitude,sl_tags,sl_description,sl_email,sl_url,sl_hours,sl_phone,sl_fax,sl_image,sl_private,sl_neat_title,ignore_sl_linked_postid,ignore_sl_pages_url,ignore_sl_pages_on,ignore_sl_option_value,ignore_sl_lastupdated

This will import the primary fields and ignore the secondary data fields that you will want to have re-created automatically.

Technical Details

CSV Import Duplicates Handling

 

Duplicates Handling Flow Chart
Duplicates Handling Flow Chart