Sections:
Overview
Before you begin
What can be imported
Sample spreadsheets
Upload Pictures
SendSafe TAB Delimited File Format
Duplicate SKU Operation
Quantity Pricing
This document provides information on initial set up products in your database and bulk updating of existing databases. The only tables in the database that you need to work with are the Items table and ItemsExt table.
It is very strongly recommended that your read these links covering the SendSafe database and product browsing:
The items table contains descriptions of all the items in your store. One entry in the items table is made for every item you will have on your shelves. You can modify the items table in two different ways:
Initially it is often easier to create an items table using a file which is then imported. This file is imported into the database server by you or by CPrompt. It is recommend that CPrompt perform the initial import for you so that we can check for errors. Once this initial table is imported, it is easier to use the on-line admin pages to update a few records and the bulk import tools to update large numbers of records.
*See for more information on using the on-line admin pages.
Hint: Before you create a spreadsheet of items, it is a good idea to play with the existing items in the database to get an idea how things work. Add a few records, and play with changing the categories and subcategories to see how that effects product browsing. If you are not sure what to fill in for each column in the import file or what its effect is... then experiment on the test products using the items admin page, check the products out in the storefront, then when you are happy with what you see, just start Xeroxing your test product for all the other items in the import file.
WARNING! During importing, columns containing data which is longer than its given field will be silently truncated. with the exception of productType column (which can be as long as is setup in the DB with the exception of when quantity pricing is enabled. In the case of quantity pricing, the productType column cannot be made longer than 40 chars (which is the length of the keyword column in the storeconfig table).
Please verify the length of all fields before importing. See DB Table Definitions for field lengths.
General import rules:
The type of file being imported is identified by the file extension (*.txt, *.csv, etc.). You cannot mix file types at one time. All imports files must be the same type; i.e. carry the same file extension.
To setup your initial database you need to have a file which can be imported into the database server. Any import file must have columns which are identical in name, position, and datatype as required by the import tools. Here are samples of import file in Excel, SendSafe's TAB delimited import file format, and ASCII comma delimited text.
To setup your new database, start with one of the sample files:
Click here to download sample spreadsheets
See: Items table importing tool for more information on the different spreadsheet formats that can be imported. The SendSafe TAB Delimited File Format is the recommended format to work in for bulk updates of existing database. This may also be the best format to work in if you wish to set every column in the DB during your initial import (instead of using the simpler formats).
A sample database has been setup on your website. This database is not setup for production operations and is only provided to give you an idea of what your DB should be like.
Sample full Items table = "Items.xls"
Simplified Items table = "ItemsMinimum.xls"
Complete demo database = "e-framework demo DB.tab-delimited.txt"
RMA Adjustment codes = "RMAitemsAdjCodes.xls"
Quantity pricing = "QuantityPricing.xls"
See: Items table importing tool for more information on the different spreadsheet formats that can be imported.
With the simplified items table a number of Db fields are automatically filled in for you. This can make initial entry easier, but you will lose the ability to set every column manually. See: for information on how columns are automatically assigned with the simplified import option.
SendSafe can export and import a specially formatted TAB delimited file which contains database information. This TAB delimited file is readily importable back into SendSafe and can be an ideal means of updating a large number of records. This is the recommended format to use. Samples of this format can be found in the DB Sample File.
This TAB delimited format can be easily loaded into any spreadsheet. For example: in Excel you can use the "Excel file open" menu command which will open the file or for a shortcut: right click on the file name in a Windows folder and select open with Excel (see illustration below).
Dos and don'ts
WARNING! when loading TAB delimited or comma delimited files into Excel you must check the cell format of all columns after the load. Excel will often change DATE columns into TIME columns. At the very least, make sure you set the dateAdded columns. Failure to set the dateadded column will result in the date being invisible and the date will be lost when you "save as" to a TAB or COMMA delimited file.
Files containing over 65,536 records will have to be broken into smaller files before loading into Excel. Excel has a 65,536 record limit per file. When breaking the file up be sure to include "###########################" as the first line in every file.
General Rules:
(1) Embedded means: The character code is embedded in a string.
(2) Mapping means: Use a special "character string" instead of the actual character. For example: tabs are mapped to \t# THEREFORE if you want to use a tab instead of the tab key you would enter \t#.
The SendSafe system can be setup to allow duplicate SKUs. This is sometimes required when duplicate identical products are described differently (for example: The same partnumber, but with a different description and located in a different category or subcategory).
Duplicate SKUs should only be used if absolutely required. There may be customer interface and system complexities which occur from the use of duplicate SKUs. Click here for more information on using duplicate skus.
To enable this type of operation you must set ALLOW DUPSKUS = YES.
When importing items into the database for the 1st time, you will use a spreadsheet format that does not include itemcodeid numbers. On all subsequent FULL import formats, you SHOULD use a spreadsheet format that includes either of these datasets:
Minimum format imports and RMA Imports are only available for initial (first time) product importing and will fail on product update imports when using duplicate skus.
Some additional information is needed if your product line will use quantity pricing (see for information: How quantity pricing works).
To setup your quantity pricing tables you will need to either: (a) supply a spreadsheet which contains your quantity pricing information or (b) Setup your quantity pricing tables manually via the Storefront Config Admin page.
If you wish to have your tables imported from a spreadsheet, then create a spreadsheet following the example in the download. You can also import your quantity pricing information using the StoreConfig import tool. Illustrated below is an example of the spreadsheet for import using the Items import tool:
The first line must contain the name of the "Quantity Price Table" into which the records will be imported, followed by the item quantities for each column.
You must enter the complete productType (called the Pricing Key) as it is entered in the corresponding items record including certain productType reserved keywords prefixes.
Important: You need separate spreadsheets for each set of products and prices which have a different quantity break points. i.e. hammers, screwdrivers, and pliers which all have quantity price breaks at: 0-10 pieces, 11-50 pieces, and 51 and up pieces can all going in a single spreadsheet WHILE nails, screws, and nuts which all have quantity price breaks at: 0-100 pieces, 101-500 pieces, and 501 and up pieces will go into a second spreadsheet.
The import function will update matching records already in the DB and add records which are not present in the DB. A record matches when its ProductType and Quantity are the same; when this match happens the price is adjusted. The import function does not delete records.
After or before you upload your items into the DB, you must upload the pictures for your products as well as category icon images, subcategoryicon images, and brand logos (where relevant). Failure to upload these files will result in missing pictures on producr browsing pages and elsewhere.
More information on these drilldown pictures:
