Many StoreFront 2000 users have expressed a desire to import product information into the StoreFront database from an Excel© or Lotus© spreadsheet as a time-saving device when creating a product inventory for a StoreFront web. This article describes how to open the StoreFront database, export the product table in a spreadsheet format, populate the spreadsheet with product data, then reimport the data into the StoreFront database.
Note: You must own a copy of Microsoft Access 2000 and a recent version of Excel to follow the instructions detailed in this article.
Note: the procedures described in this article are only intended for use with StoreFront 2000. The database structure of StoreFront 5.0 makes importing data from a spreadsheet prohibitively difficult.
Note: enter a value in every field of the spreadsheet, even if it is only a space. If you do not, null values will inevitably cause problems for you in the future.
EXPORTING THE PRODUCT TABLE TO CREATE A SPREADSHEET
Open FrontPage 2000. Click on File, then select Open Web. In the Folder Name field, enter the URL to your web site.
Locate your web's database. By default, this will be the storefront.mdb file in the fpdb folder. Right-click your database and select Copy, then right-click anywhere in the fpdb folder and select Paste. This will create a backup copy of your database.
Double-click the backup copy of your database to open it in Access.
Once the database has downloaded and opened in Access, click on View and select Database Objects, then Tables.
Right-click the product table and select Export. Leave the file name set to product. Under Save as type, choose a file type corresponding to the version of Excel or Lotus that you have installed. Browse to your Desktop, then click Save. This will export the table to your Desktop as a file called product.
ENTERING PRODUCT DATA INTO THE SPREADSHEET
Minimize Access and go to your Desktop. Double-click the new product spreadsheet to open it. Once it is open, begin entering your data. The fields of the product table and the values that should be entered in each are described below. PRODUCT_ID: this field should contain a unique identifier for each product you enter. Ideally, it should be fairly short and contain no spaces or special characters such as commas, apostrophes, or ampersands. This field has a maximum length of 30 characters.
DESCRIPTION: this field should contain the product's name, or a brief description of up to 255 characters.
MESSAGE: this field should contain the confirmation message that will be displayed to customers when they add this item to their order. This field supports the use of HTML tags; for example, to create a link to a complementary product item. This field has a maximum length of 255 characters.
CATEGORY: this should be the product category the item falls into. For example: housewares, clothing, hardware. This field has a maximum length of 50 characters.
IMAGE_PATH: this should be the URL to an image you would like to display in conjunction with this product item. This field has a maximum length of 125 characters.
MFG: this should be the name of the manufacturer of this item. This field has a maximum length of 50 characters.
LINK: this should be the URL to a page that is associated with this product item. This field has a maximum length of 125 characters.
ShipSwitch: the value in this field determines whether or not shipping will be applied to orders of this product. A 1 is equivalent to "Yes, charge shipping". A 0 is equivalent to "No, do not charge shipping for orders of this product."
CTax: the value in this field determines whether or not Country Tax will ever be applied to orders of this product. A 1 is equivalent to "Yes, charge country tax." A 0 is equivalent to "No, do not charge country tax for orders of this product."
STax: the value in this field determines whether or not State Tax will ever be applied to orders of this product. A 1 is equivalent to "Yes, charge state tax." A 0 is equivalent to "No, do not charge state tax for orders of this product."
ATTRIBUTEA, ATTRIBUTEB and ATTRIBUTEC: these fields should contain any attributes that you would like to use for the product item you are creating. Each field should contain a separate category of attributes, and there is no limit of the number of attributes that can be entered in each field so long as the total length of the attribute entries in a field do not exceed 255 characters. Attributes should be entered in a comma-delimited string, with no spaces. For example: red,yellow,blue,green,black,gray,orange,purple
AttSwitch: the value in this field determines whether attributes for the product you are creating will be turned On or Off. A 1 is On, a 0 is Off.
PRICE: the value in this field will determing the price of the product you are creating. Do not enter currency symbols in this field; use numbers and decimals only.
WEIGHT: enter the weight of a single unit of the product item you are creating in this field. The weight should be entered in pounds, with up to 2 decimal places.
SHIP: the value in this field will determine the Ship Cost of a single unit of the product you are creating. If your site is using Product-Based shipping, this amount will be charged to the customer as the shipping cost of one unit of this product.
LONG_DESCRIPTION: this field should contain a lengthy description of the product item. Its length can not exceed 32,000 characters.
Save the changes to this spreadsheet and close it, then return to the database open in Access.
IMPORTING THE SPREADSHEET INTO ACCESS
Click File, then select Get External Data, and Import. Browse to your Desktop, and select Microsoft Excel for Files of type. . .. Doubleclick the product spreadsheet that contains your product data.
The spreadsheet containing your product data should have one worksheet or range. Make sure this is selected, then click Next.
The spreadsheet that was created by exporting the product table will contain column headings in the first row which correspond to the database field names. Check First row contains column headings then click Next.
Access will now ask where you would like to import the data from the spreadsheet to. Select In an Existing Table, then select the product table from the drop-down menu next to this option. Click Next
Click Finish to import the data into your database. Save the changes to your database and save a backup copy, close Access, then return to FrontPage to allow the database to upload. This should finish the process. If you were careful to enter a value in every field, and if you followed the instructions above carefully, you should not encounter any problems.
If you performed this procedure in a live web that is actively taking orders, check your sales reports and process any orders that were placed between the time you downloaded the backup copy of the database then uploaded it after adding products.
Rename your original database to StoreFront.old. Now, right-click the backup copy that contains the new product data and select Rename. Give this database the same name as the original copy before it was renamed to StoreFront.old. Once the database has been given the name of the original, it will become the web's active database. The new products will be made available to customers, and orders will be recorded in this database.
