Uploading Database Files

Merchant Solutions offers a powerful feature that lets you create or update items for your site in just 2 steps:

  • Generate a text file in CSV (comma-separated values) format as described below.
  • Upload the file to your store.

Although this page describes the file format for database uploads in detail, you usually won't have to think about it unless an error occurs. Most spreadsheet or database programs (such as Microsoft Excel) can generate a correct CSV file for you automatically; generally, you just need to go to the File menu, choose Save As..., then select CSV.

Creating Your CSV File

The file format for database uploads has two parts:

  • The first line should contain the names of the fields (name, code, price, etc.) in the rest of the file.
  • The rest of the file should consist of a series of records. Each record consists of several fields (those listed in the first line) that describe a single item.

Here is an example of a small database file:

path,code,name,price
Cookies,F23,Chocolate Chip Cookies,4.95
Cookies,G12,Oatmeal Raisin Cookies,3.95

The first line states that the rest of the file will contain records of four fields each: The first field will be the path (the name of the section(s) containing the item); the second will be the code (e.g. SKU); the third will be the name; and the fourth will be the price.

When uploaded, this file will yield a store with one section, called Cookies, containing two items: Chocolate Chip Cookies at 4.95, and Oatmeal Raisin Cookies at 3.95.

It does not matter what order you put the fields in, so long as the field names in the first line match the records in the remaining lines.

This file would have exactly the same effect as the preceding one:

code,price,path,name
F23,4.95,Cookies,Chocolate Chip Cookies
G12,3.95,Cookies,Oatmeal Raisin Cookies

Note that records don't have to contain values for all the fields in an item. The examples so far do not include the caption field, for example. Here is a version of the same file that does include captions:

code,price,path,caption,name
F23,4.95,Cookies,"These are the
best chocolate chip cookies you have ever tasted.", Chocolate Chip Cookies,
G12,3.95,Cookies, ,Oatmeal Raisin Cookies

This example shows us two things:

  1. It is ok to use linebreaks within records as long as they are within a quoted string.
  2. You can leave a field empty—the Oatmeal Raisin Cookies have no caption.

A quoted string is a series of characters beginning and ending with the double quote character ("). For example, when you edit an item by hand, you need to surround multiple-word options with double quotes, this can be seen in the example below where 'Sea Green' is the color option. Since double quotes are already meaningful in the CSV format, begin and end the options field with a double quote, and use two adjacent double quotes wherever you need to include double quotes for multiple-word options. In general, blank lines, commas, and other punctuation must be between quotation marks for the entry to be read correctly.

Notice in the following example how the options for the Polo Shirt begin with a double quote. This allows you to separate different options with blank lines (as with Size and Color below) and to include multiple-word options (Sea Green). Sea Green has three quotes following: two to signal the end of a multiple-word option, and one to end the quoted string.

path,code,name,options,price
Shirts,F23,Polo Shirt,"Size S M L XL Color Marigold Taupe ""Sea Green""",22.95Pants,P12,Chinos,"Waist 32 34 36 Inseam 30 32 34",34.95

This yields a site with two sections, Shirts and Pants, each with one item, and each item with two options. When uploading a CSV file with an options field, make sure that all of the options are enclosed in double quotes. Also, remember that if you use multiple-word option names or values, these strings must be wrapped in sets of two double quotes ("").

You don't have to create sections to contain the items described in your file. They will be created automatically from the names you give in the path field. If you want to create a site that is several sections deep, you can do it by separating section names by colons in the path field. For example:

path,code,name,price
Nonfiction:Computers:Web,0-201-42379-1,HTML for Bozos,19.95
Nonfiction:Cooking:Vegetarian,0-201-41379-1,Turnips Galore,29.95

The CSV file above will create a section called Nonfiction which has two sections called Computers and Cooking, with subsections called Web and Vegetarian respectively, each containing one book.

Before you upload CSV files, we suggest you check your data for these common mistakes:

  • A missing first line. Without field names, our system won't know what to do with the data.
  • Missing fields within records. If you want to leave a field empty, remember to include the comma; otherwise, the remaining fields will be misinterpreted.
  • Missing commas.
  • Colons within section names. These will be interpreted as several nested sections.

Uploading Your CSV File

Now that you have a completed CSV file, it's time to upload it to your store:

  1. Sign in to your Business Control Panel.
  2. Click the Store Control Panel link.
  3. Click the Store Editor link.
  4. Click the Database Upload button.
  5. Click the New Upload button.
  6. Click the Choose File button and locate the CSV file to upload. Note: Unless you are sure of what you are doing, do not make any changes to the pull-down menus on this page
  7. Click the Upload button.

Note: Depending on your internet connection and the size of your CSV file, the upload may take a few minutes.

When the file has been uploaded, you will see the Database Upload page again. It will tell you how many records were in the file, any warnings or errors that occurred, and the contents of the first record. You can check other records by entering a number in the Jump to record # box and clicking Go!; this will help you see any errors in context, as well as spot-checking any records you are unsure of.

To complete your upload, you must click the Add or Rebuild button:

  • Add will add to or alter the products that already exist in your store.
  • Rebuild will delete all of your current items and start from scratch with the contents of the CSV file.

If you find an error after going through all of the above, don't worry—you can still get your old site back. Just return to the Database Upload page and click the Revert button.

Warning: The Revert button only works once. You can't go back to how your site was two or more uploads ago, so make sure to check your site after each upload.

 

Frequently Asked Questions

This sounds complicated; what's the easiest way to get started? We recommend that you begin by generating a small CSV file with just a few records in it. Then look at the file to make sure it is correct; for example, check that the first line has the correct field names. When it looks correct, try uploading it as a test. Once you have uploads working for a small file, you can go ahead and upload your real data. Also, using a spreadsheet program such as Microsoft Excel to create your items is much easier than trying to write a CSV file by hand.

Why does it say "document contains no data" when I upload my CSV? The field names may be missing or not all on one line.

If I Add an item with the same name as an existing item, will the new one overwrite the old? No. Items are identified by their id fields, which can be specified in the CSV; if an id is not specified, one will be created based on the code. You can have two items with the same name; however, Adding an item with the same id as an existing item will update the matching fields on the existing one, and add any fields that aren't.

Is there a way to upload images en masse? Yes, you can upload a ZIP file containing multiple images.

Can I get the contents of my site back from the server in the same format? Not yet, but we plan to add that eventually.

Is there a limit on the size of file I can upload? Our software will work for files up to about 14 MB; that said, some browsers impose a lower limit than that. If you try uploading a large file and the browser seems to finish immediately, it was probably not sent. Try breaking your CSV file into multiple smaller files.

  • Was this article helpful?

  • Yes
  • No

Still need help?