This guide assumes you use a commercial web host that has phpMyAdmin installed, and that you have some experience phpMyAdmin. Different versions of phpMyAdmin work differently, so these instructions may not work on every server (ie. some older versions do not let you add your own auto-increment numbers to a table). This guide assumes you are using the Deluxe Catalog system, though it does not utilize the options inventory. There are a lot of small details associated with file uploads, so it is NOT something you want to play with unless you know how to use phpMyAdmin and feel comfortable with the instructions. This goes without saying, but we'll say it anyway - if you do decide to do this, make backups of your original text files! The Small Biz Community holds no liabilities for the information provided below - this is just intended as a guide to assist you in learning how the table structure is set up so you can better add your own information.

Please do not email us to ask for additional help with text file uploads! This service is not included as part of the free support that is given with OptionCart, and we cannot give one on one assistance with the procedure below. However, if you have a spreadsheet or text file that you are having problems loading and would like someone to do it for you, we will be glad to do this for you for a fee.

1. If you have any items in your text file with a quote mark (") or an apostrophe ('), add a slash in front of them so that the PHP parses the data correctly. So this entry: 5" Grannie's Apple Pie Scented Candle would need to be reworked as: 5\" Grannie\'s Apple Pie Scented Candle. Make sure to use the \ slash, not the / under the question mark.

2. Familiarize yourself with the table structure. The following is the setup for your files:

Shop_Items: This is the table that stores your main product information. The fields within this table are:

• ID (an auto incremented file, unique to each item)
• Catalog (the catalog number)
• Item (the item's name)
• Description (the longer description of the item)
• SmImage (the item's smaller image in URL form)
• LgImage (the item's larger image in URL form)
• Category1, Category2, Category3 (the first three categories associated with this item - they must be a choice from items in the Shop_Categories table)
• Units (the shipping units for this item, a number)
• RegPrice (the item's regular price - should be in a decimal format, with no currency signs)
• SalePrice (the item's sale price - should be in a decimal format, with no currency signs)
• OutOfStock (either Yes or No, this field determines whether an item is out of stock)
• Active (either Yes or No, this field determines whether the item will appear in the catalog)
• Inventory (the total inventory quantity for this item)
• Keywords (the keywords associated with this item)
• Category4, Category5 (the last two categories associated with this item - they must be a choice from items in the Shop_Categories table)

Shop_Options: This is the table that stores the options (ie. colors, sizes, styles, etc.) for each item. It is not required to use this table if you don't have any items with options.

• ID (an auto incremented file, unique to each option)
• ItemID (this is the ID field of the item in Shop_Items that the options are associated with)
• OptionNum (each option for an item is numbered from 1 on - that number goes here)
• Name (the option name - ie. Colors or Fabrics)
• Type (Text Box, Drop Down, Drop Down with Prices, Radio Button, Radio Button with Prices or Memo Field)
• Attributes (the list of attributes under the option, which uses the same format as described in the instructions - ie. Red~Blue~Yellow or Denim:14.00:2~Velvet:16.00:2~Silk:15.00:2)
• Active (either Yes or No, this field determines whether the option will appear in the catalog)

Shop_Categories: This table lists all of your categories and subcategories.

• ID (an auto incremented file, unique to each item)
• Category (the category's name)
• Parent (if the category is a subcategory, the name of the category above this one)
• Image (the category's button image in URL form)
• Description (the longer description of the category)
• MetaDescription (the description of the category that is used in the meta tags)
• Keywords (the keywords associated with this category, to be used in the meta tags)
• Active (either Yes or No, this field determines whether the category will appear in the catalog)

3. Set up your categories table. This just lists all of your categories. Make sure to create an ID field that gives each category a number. This number must be unique - that is, you can't use the same number for more than one category. Leave out any information you don't want to include. Here is an example of a spreadsheet or text file that you might use:

4. Set up your item table. Make sure to create an ID field that gives each item a number. This number must be unique - that is, you can't use the same number for more than one product. If you don't have information for any of the fields, leave that field blank. Use the exact headings in the exact order as above. For example, your text file or spreadsheet for your items might look like:

5. Set up your options table. This table is a little trickier than the other two, because you need to use the ID number from your items table within your options, and you need to add the OptionNum field to count the options for each item. Plus you can only add price changing options at certain times. Let's look at an example:

In this example, we're dealing with two items - the Carrot Candle (ID #1) and the Scented Potpourri Bag (ID #4). Both of these have options associated with them. In the above table, we've added the "ItemID" to be the same as the "ID" number of the associated item.

The "OptionNum" field just simply "counts" the options that are associated with a particular item. So if you have five options for a single item, you'd have OptionNum 1-5. If you have eight options, you'll use OptionNum 1-8. This also determines the order in which your options will be displayed, so make sure to use that number appropriately to determine the order of your options. You can't have the same OptionNum for the same ItemID. So, for example, if you have five options under ItemID #9, one of those options will have an OptionNum = 1, one will have OptionNum = 2, etc. up through OptionNum = 5.

Now here's the tricky part. If you have a price changing option (ie. you're using "Drop Down with Prices" or "Radio Button with Prices"), you MUST assign the OptionNum to 1. And because you only have one OptionNum = 1 for any item, you can only have one price changing option per item. This is very important - if you don't follow this to a T, the system won't work.

Okay, for our examples: The Carrot Candle has a drop down with prices called "Select", that specifies whether the item is to be gift wrapped or not. Then it also has a selection called "Type" that determines what kind of candle it is. Since "Select" is a price changing option, we've set the OptionNum to "1" for that option, and the "Type" therefore must be "2".

The Scented Potpourri Bag has four different options - scents, bag color, ribbon color and material. None are price changing options, so it doesn't matter which one is placed as "1" in the OptionNum field.

If the way this option table is set up confuses you, your best bet is to either leave out all the options and add them manually. Or if you have many options add just a few manually through your administration area, see how the system is set up in phpMyAdmin, then use that as an example for your other items.

6. Finally, once all of your tables are completed, make sure the top rows with header information are gone, then upload them via phpMyAdmin. Please note that this step varies with different phpMyAdmin versions, and so you should check with your host for complete instructions. Below is a guide to uploading via phpMyAdmin using the general configuration:

• Double check your spreadsheet to make sure there are no quotes or apostrophes in your listings. If you need to use quotes, change " to \" and change ' to \'.
• Save your spreadsheet as a "tab delimited text file". This means that it will be saved as a straight text file, where fields are divided out by tabs.
• Access your control panel's phpMyAdmin area.
• Create a backup copy of your files. Important!
• On the left side, in the green bar, you'll see a list of tables that start with "Shop_...". These are your database tables.
• Select the table that you want to import into. For example, if you are adding products, select "Shop_Items". If you are uploading options, select "Shop_Options".
• Select the 'Structure' tab at the top of the page if available.
• Scroll to the bottom of the page and click the link called "Insert data from a text file into table".
• Use the Browse button to find your tab delimited text file.
• Change "Fields terminated by" from ; to \t
• Delete the " in "Fields enclosed by"
• Keep "Lines terminated by" as \r\n
• Keep "Fields escaped by" as \
• In the "Column names" field, list each column that you have included in your text file, separated by a comma. These column names must be the same name as the field name.
• Click Submit to upload.

We strongly recommend that you first backup your database, then add a text file with just a few items at first to test the system before adding your entire file. Also, you might only be able to add 100 records or so before the system blips out on you, so do this in batches wherever possible.

EXAMPLE

Here's an example of a merchant who is adding their products.

Mary sells 3 different bath gels. All of her items have a catalog number, an item name, a description, a small image and are in the category called "Bath Gels". All of her items have one option - each is sold either in 1 oz packages for $2.00 or in 4 oz jars for $6.00, and each can be in a white or black container. She wants to use drop-down boxes on her site to show these options.

1. First we need to create the items spreadsheet. Note that Mary doesn't need every single field.

2. Now, Mary saves this spreadsheet, and also saves a copy as a tab delimited text file. ("Delimited" means that each item is separated in some way. Since it's "tab delimited", that means that each item is separated by a tab character.)

3. Mary sees that she doesn't have any quotes or apostrophes to unescape, so she doesn't have to change her files.

4. Now, Mary goes into phpMyAdmin, chooses her Shop_Items table, and selects "Insert data from a text file into table".

5. She uses the browse button to find the tab delimited file she just created.

6. Now, she uses the following settings:

Replace table data with file:
Fields terminated by: \t
Fields enclosed by:
Fields escaped by: \
Lines terminated by: \r\n
Column names: ID,Catalog,Item,Description,SmImage,Category1

Note: \t means "tab", \r means "carriage return" and \n means "line feed". So \t in the files terminated by means that each field is separated by a tab, and \r\n means that each new item is on a new line. Leave the "Replace table data with file" box and "Fields enclosed by" field blank unless needed. Also note that because Mary is only using some fields, and not every single one in the Shop_Items table, she needs to list those fields, separated by commas, under "Column names".

7. Mary clicks Submit and her file is loaded!

8. If Mary didn't have any options for her files, she'd be done. But she does, so she has to first think about how this is going to be set in OptionCart.

9. First, she has one price changing option for each, to determine whether it's sold in one or four ounce containers. She knows that there can only be one price-changing option per item in OptionCart, and that it must be the first option on the list (so the Option Number must be 1). So she uses the following:

Option Number: 1
Option Name: Size
Option Type: Drop Down with Prices
Option Attributes: 1 oz package:2.00~4 oz jars:6.00

10. Now, Mary is going to work up the settings to let customers choose a white or black container. This isn't a price changing option, so she'll just use option number 2, since it's the second option for each product:

Option Number: 2
Option Name: Container
Option Type: Drop Down
Option Attributes: Black~White

11. Next, Mary needs to add these to a spreadsheet. She'll add one of each options to each of the three items. Note that she's going to use the item ID field that she set when she added the item to fit in the item id field.

12. Mary now repeats steps 2 through 7 above, loading these options into the Shop_Options file, and she's done.

Please note: Small Biz Community is not responsible for any damages or liabilities caused by using phpMyAdmin to upload files. Using your spreadsheets and or text files to enter items is the sole responsibility of the online store user.