Importing Data from CSV Files

When entering information into your Webvanta database, you can always enter it one record at a time using the automatically created admin forms. But if you have lots of existing information to enter, it may be easier to do so by uploading a file.

Creating the CSV File

To enter information into the database by uploading a file, you must create a CSV (comma-separated values) file. Any database or spreadsheet program should have an option to export this type of file. Text encoding should be UTF-8.

Each import affects a single database item type. The first line of the file must be a list of the field names, as defined in the item type to which you are importing data, in the same order in which they are presented in the subsequent lines of the file.

If you are generating the file from a spreadsheet, you can accomplish this by entering the field names on the first row of the spreadsheet. If you are exporting from a database program, you may need to add this line of text manually using a text editor.

Note that this list of names must use the field names (which are for use in your code), not field labels (which are how the fields are identified on forms). Look at your item type definition at Database > Item Types > Edit to find these.

For example, suppose you have created a custom item type for Customers. To keep this example simple, let's assume the fields you want to update are first name, last name, city, and state. The CSV file would look something like this:

name,last_name,city,state
George,Smith,San Francisco,CA
Tom,Jones,Miami,FL

You can also import the SEO fields, which are part of all database items. The fields are named seo-title, seo-keywords, and seo-description.

CSV File Details

As with any CSV file, there are some precautions you must observe to ensure that the file is valid:

• CSV respects white-space and it is considered part of the field. If you don't want leading or trailing space in your values, be sure to leave it out like the examples above.
• If any of the field includes a comma, then you must put the field in quotes so the comma within the field is not interpreted as the end of the field. (For example, if you put city and state in the same field, one record might be George,Smith,"San Francisco, CA")
• If you put quotes around a field, you must deal specially with any quotes within the field, or the quote within the field will be interpreted as the end of the field. You can either use single quotes inside the field, or escape the quotes by using double-double quotes. For example, George,Smith,"SF ""The City"", CA")
• If there are any characters beyond the standard ASCII set (such as curly quotes or apostrophes), it is essential that the encoding for the file be set to UTF-8. Excel does not seem to provide an option to set the encoding, so if you are exporting from Excel, you may need to open the file in a text editor that lets you set the character encoding, and then re-save.

For more information on CSV files, see the official RFC that defines the format as used in our software.

Uploading the CSV

To import the information from the CSV into your site's database, choose Database Setup > Import Database Items from your site's control panel. (You must first create the custom item type, if you have not already done so.)

On the Import page:

1. Choose the item type to which you want to import the data.
2. Check the Replace Content box if you want to delete any existing data for this item type.
3. Browse to the file you want to upload.
4. Click Import.

You'll then see the import dialog, which will display until the import is complete. It will then tell you how many records were imported, if the import was successful. If the import failed, it will display error messages that should help identify the problem.

Importing Special Field Types

You can currently import text, text area, WYSIWYG text area, categories, tags, date, checkbox, related item, related asset, and taxonomy fields.

For checkbox fields, if the field value is 'true', '1', 'yes', or 'on' (without the single quotes), the box will be checked (true); otherwise it will be unchecked (false).

For date (or date/time) fields, the importer supports most common formats.

For category or tag fields, you specify the name of the category or tag. If you want to associate more than one category or tag with an item, you can write more than one in that field, separated by commas, and surrounded by quotes: "category-1,category-2,category-3". There can be only one "categories" or "tags" header. If a category or tag term does not already exist in the database, it will be created. For hierarchical categories, you can specify the parent category followed by the subcategory, as follows:

pets > cats

For taxonomy fields, use the name of the taxonomy in the column header, and the term name in the row. If you want to associate more than term with an item, separat them by commas, and surrounded by quotes: "term-1,term-2,term-3". The taxonomy must exist before doing the import, but the terms do not necessarily need to exist. If a term does not already exist in the database, it will be created when the CSV is imported. For hierarchical taxonomies, you can specify the parent term followed by the subterm, with a > symbol between them (as described above for categories).

To import related item or related asset associations, the field contents must be comma-delimited lists of the exact names of the related items. Double quote the whole list. The items or assets must already exist in the database for the connection to be made (remember, you are importing only the association, not the item itself.

Importing Related Assets

If you have a related assets field, you can put the filename in the CSV, and the system will hook the file up to the database item. You do not need to include the full path (folder names) as long as the filename is unique.

It is essential that the files are uploaded before the database is imported. The connection between the database item and the file is made when the CSV is imported, and the file must already exist in the system for this to work. If you upload the file after the CSV import, you will need to edit the database item and choose the file manually, just as when creating a new database item.

If you have a related assets field that allows multiple assets, and you want to list these on separate rows of the CSV, then all except for the first one must have the text "add:" (without the quotes) immediately prior to the filename. For example:

add:/images/pretty_picture.jpg

You can also use the "add" keyword when importing updates to a database, so the related assets will be added to any that already exist. If you do not use the "add" keyword, any existing related assets would be disconnected from the database item.

Updating Existing Records

If you have existing records that you want to update, the importer will attempt to do so if you help it know which field to use to identify the record to be updated.

To identify the field to be used to identify records, precede that field name in the first row of the file with two asterisks (e.g., **name). Currently only the name, URL, and ID fields are supported. The value in the indicated field must uniquely identify a single record.

When a record is matched by name, it will be updated. If no-such record exists, a new record is created.

When using the update mechanism, do not select the checkbox to delete your database first. If you do, you will always generate new ID numbers.

Setting the Default Item Type

If you are repeatedly uploading information to the same custom item type, it is helpful to be able to set the default setting for the pop-up select list. For example, a site for a mortgage broker may use a "Rates" custom item type and upload a new CSV frequently.

To set the default value, create a global setting with the name importer.defaults.import.target, and set the value to the name of the desired custom item type.