CSV Import & Export

CSV Import & Export

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

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 setup 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 Admin->CSV 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)

CSV Import Options

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

Import Files

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.

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 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!

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.

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.

CSV Export Options

Here are the options you can configure for exporting data.

Export Setting
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.

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!

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!

#imp

Some Notes on Recurring Imports or Making Bulk Changes to Listings