CSV Import & Export

Page Contents

The Import and Export area allows you to manage your listing data in bulk. You can mass-create listings with CSV imports, and you can mass-edit listings by doing a CSV export, editing the CSV file, and then re-importing the CSV file with the changes.

Depending on which operation you need to perform, you select the appropriate tab:

Import Tab settings
Export Tab settings
If your directory has any entries in Connecticut (CT), Massachusetts (MA), Maine (ME), New Hampshire (NH), New Jersey (NJ), Puerto Rico (PR), Rhode Island (RI), Vermont (VT), Virgin Islands (VI), APO Europe (AE), FPO Europe (AE), see this special note.

Import tab

IMPORTANT: Before you import ANY records, you should completely set up and configure ALL form fields you plan to use in your directory. This will save you any headaches from data you missed during mapping. A best practice is to create a full listing with every field filled out, then do an export of that listing. You can then format your CSV import file based on that template.

NOTE: Be sure to use the “See Sample CSV File” button at the top to get started with a template CSV file–it's WAY easier than typing it from scratch and helps avoid common errors.

FILE SIZE NOTE: There is no upper limit on the number of records that can be imported using the CSV file process, though your hosting plan may have some effect on the process. This is typically something that your hosting company can help you with, including reading any error logs that have been produced.

A CSV file of 25000 records isn't a problem for BD itself, but it will overflow the memory of most hosting services during import. We would recommend that you break up your import into files that are smaller — starting with about 1000 and see how that goes. You may need to adjust up or down, depending on your hosting plan.

How to Import Your Data

To import listings via CSV, you must do the following:

Provide a CSV file that is of the correct format. After you configure your form fields, there is a sample CSV file that Business Directory will create for you as a starting template. We highly recommend that you use this as the starting point to avoid column naming errors!.
If you want to import images within your ads as well, you must provide a separate ZIP file with all the images contained in it. The names of these images should be referenced in the “images” column of your CSV file. If you have multiple images for a single ad, you must separate them with the “Image Separator” character. (Semi-colon by default (;)). For example: image1.png;image2.gif;image3.jpg. Set the path to this file in the ZIP containing images file textbox.
NOTE The image file is often the reason for a failed import. Try to use the smallest size of image you think you will need, at most. Putting 100 2MB images into a ZIP file will only choke the uploader and cause timeouts during upload!
Decide how to handle categories for import. BD can do it two ways: Auto Create Categories or Generate error if Category not found. The Auto Create option is good for new installations where you have an existing set of categories you want to use. The Generate error option is good if you already manually set up your categories and want to make sure the import conforms to your existing structure. NOTE: BD can only handle single-level category creation on import (top level categories only). So if you want something more complex with children, you might want pre-define your categories and use the second option.
Imported ads can be assigned to a user by using the user_id column in the CSV file. The user_id must match a login name of an existing user in your WordPress site. If you want ads assigned to a particular user on import, you should check this box. Otherwise, leave it unchecked.
If you checked Assign to User, and an ad does not have a user_id defined in the CSV, you can assign it to a default user, such as admin. Select the user you wish to user as the default one. Only valid if you checked Assign to User in the step above.
Once you configure these options, you can try out your import and see if any errors will be generated by clicking “Test Import”. If all goes well on the test, you can perform the actual import by clicking “Import”, which will load the BD tables with the data you configured above.

Common Errors to Avoid During Import

Your CSV file should be based on the sample CSV file that is created under Directory → Import & Export, from clicking See a Sample CSV File. Generate that if you haven’t already.
Your CSV file must have every column from the sample file, named exactly the same as shown in your grid on this configuration page. Don’t delete any of them, especially the OPTIONAL ones.
Your CSV file should have data in every column that is required. Optional columns/fields can be left empty below the header, but you cannot remove the column!
Text fields can’t have single quotes in them without putting double quotes around the whole field (e.g. “Bob’s Market”)
Dates must be in the expected format of the CSV import settings
Imports work best after you’ve completed all field configuration, so make sure you do that first (especially naming)

PRO TIP: If you want to upload a URL that is replaced by text for your URL field, use the following format in your CSV file: “https://mysite.com,My Website” That will populate the link and the text into their proper places.

CSV Import Options

Here are the options you can configure for importing data. At the top:

CSV File (required): Path to the CSV file you will use for import. We recommend keeping the overall import size under 5MB to avoid timeouts

ZIP file containing images: Path to the ZIP file that contains the referenced images in the CSV (optional, unless you used images, in which case it’s required)

CSV File Settings
Column Separator (required): What separator should be searched for between columns? Default is comma (,). Do not re-use this separator value in the other separator values! It will make your file unreadable to the BD importer.

Image Separator (required): What separator should be searched for to separate multiple images in the image column? Default is semi-colon (;). Do not duplicate this separator with the column separator! It will make your file unreadable to the BD importer.

Category Separator (required): What separator should be searched for to separate multiple categories in the category column? Default is semi-colon (;). Do not duplicate this separator with the column separator! It will make your file unreadable to the BD importer.

Import settings

Post status of imported listings: After import, BD will use this status to assign to listings immediately upon creation. Default is Published, which means the listing will be immediately visible after import.

Missing categories handling (required): Should BD be allowed to auto-create categories or instead, generate errors when a category is not found. By default, we will auto-create categories (which is a good option for first time imports). If you have existing listings and categories, we recommend the second option so you can make sure your data is consistent and correct.

Here is an example of correct category formatting for importing multiple categories:

Keep existing images?: Checking this option will make BD keep any existing images for a listing if you RE-import listings that previously existed. If you want to replace the images for a listing on import during some mass update, leave this option unchecked.

Assign listings to a user?: This column indicates whether you will be assigning listings to individual users or not. If you turn this on, and leave the assigned user column empty, it will be assigned to the admin by default.

User a default user for listings?: This user will be assigned to all listings if the data in the username column is not present for any row in the CSV file. If you check this option, the name of the default user appears as a setting:

The default user can be any valid username, but it is required if you select “Use a default user”.

Disable email notifications during import?: Checking this option will prevent any “new listing” or “edit listing” notifications from going out to the owner and admin of the listings. By default, this is on. We recommend keeping it that way unless you have some need to notify all users that their listings have been created.

At the bottom, you can either click on “Test Import” or “Import”. We strongly recommend trying a Test Import before running a full import to see if you will encounter problems with your file(s) or settings first!

IMPORTANT Be sure to use the “See Sample CSV File” button at the top to get started with a template CSV file.

How to label your fields correctly for import

The bottom of the page also contains a helpful full grid showing you all fields in your configuration, whether they are required and/or multi-valued (like select lists, checkboxes, and radio buttons) to help you understand any errors you may encounter. It looks like this:

This grid has a lot of valuable information, but the one that you should pay CAREFUL attention to is the far left column (Header name/label). These values are the EXACT values you must have in your CSV import file!

You must match them in spelling and case, BOTH. For example, if you wanted a ZIP Code field in your CSV, you must use “zip_code” as shown above (no quotes). It cannot be “ZIP_CODE” or “ZIP_code” or anything else.

Also, ALL COLUMNS SHOWN ARE REQUIRED TO BE PRESENT IN THE CSV FILE. You don't have to put data in any column where the data type is not “required”. But the column must be in the CSV file. Use the “Header name/label” field in the CSV file. Don't use the “Field” description as the header row in CSV.

NOTE: It's easy to avoid these errors if you use the Sample CSV file that you can generate on this page as a starting point. Generate your sample CSV file AFTER you've created ALL fields you plan to use in your listings.

Return to top

Export tab

To export ads via CSV, you must have data in your directory, either through direct entry OR via CSV Import.

IMPORTANT Please note that the export process is a resource-intensive task. If your export does not succeed try disabling other plugins first and/or increasing the values of the ‘memory_limit’ and ‘max_execution_time’ directives in your server’s php.ini configuration file.

CSV Export Options

Here are the options you can configure for exporting data.

IMPORTANT If you are doing a bulk edit of your listings in BD, you will want to re-import the data BACK into BD when you are done. Make sure you select the “Export Sequence ID” option when you are exporting to make sure you can re-import and match the listings back when you're done!

Export Settings

Which listings to export?: How to filter the exported listings: All, Active, or Active + Pending Renewal.

Export images?: Checkbox indicating whether you want to output the images with listings or not. If you’re doing bulk data edit, don’t check this option as it speeds up the export. You'll want to make sure that on import you have the option “Keep existing images” CHECKED to not accidentally remove your images during reload.

Additional metadata to export:: Checkboxes for optional columns to include in the export. If you are doing bulk data edit and plan to re-import the data, be sure to check the option “Include unique IDs for each listing (sequence_id column).” Other data available includes the listing owner (username) and the expiration date of the listing. These can be edited and re-imported if desired.

CSV File Settings

What operating system will be used to edit the CSV file?: CSV files are handled slightly differently on MacOS and Windows, so pick the one you plan to use for EDITING of the CSV. If you don't select the right version, your CSV may contain weird characters and may not be editable.

#imp

IMPORTANT NOTE: If your directory has any entries in Connecticut (CT), Massachusetts (MA), Maine (ME), New Hampshire (NH), New Jersey (NJ), Puerto Rico (PR), Rhode Island (RI), Vermont (VT), Virgin Islands (VI), APO Europe (AE), FPO Europe (AE), you will need to follow special instructions for importing listings with ZIP Codes beginning with zero. If you are using the text only format of a CSV file to import listings, there is no issue. But if you are using Excel or Numbers to edit the file, or are doing an EXPORT-CHANGE-IMPORT using one of those programs, the ZIP Code field must be changed to a TEXT field in order to avoid losing the leading zeroes that are what begin the ZIP codes for these locations. If you need access to a program that can do this for you, please visit this site.

Some Notes on Recurring Imports or Making Bulk Changes to Listings

For CSV imports, the main thing to keep in mind is that if you upload a new CSV each time you want to add listings, then new listings will be created. To UPDATE listings, you will need to have the Sequence ID for each listing.

The only way to get the sequence ID for a listing is to do an export from the current directory. So, as long as you can match up the new file to the old IDs you will be fine. You can choose this option when doing the export:

Depending on whether the directory will be continuously growing or whether the listings be stable and occasionally updated will make a difference for how you handle things — you may decide to do your imports with two files — one with existing listing updates and one with new listings.

You can change and update all kinds of information using the Export/Change/Re-import method. For example, you can change fee plans for a large number of listings. That process would look like this:

Export the listings with the Sequence ID
Change the fee plan in the CSV file
Re-import the listings.

Was this article helpful?

Yes No

CSV Import & Export