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.
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.
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
Identifier – a unique location identifier from an external database that can be used to update existing locations in Store Locator Plus
Office Hours – long formatted text
Contact Address – long formatted text
Notes – long formatted text
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.
Introduction – long formatted text
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.
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.
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.
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 the record even if it already exists in the database.
Do not add the duplicate record, ignore all other data changes.
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.
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)
- 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.
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 '&quot;' 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:
This will import the primary fields and ignore the secondary data fields that you will want to have re-created automatically.
CSV Import Duplicates Handling