Importing data records: Concepts and details

This is an overview of the general concepts and the process involved in importing or updating data records into your Zen Cart or osCommerce shopping cart.

Contents


Using the import wizard

  1. To begin, choose the “Import/update data” menu item from the Tools menu.
  2. The import wizard appears. Paste the CSV data into the box provided. You can also use a file containing your CSV data, if you wish. Choose the field separator (comma or tab) and click the “next” button.
  3. The second step of the import wizard appears, with fields matched against corresponding fields in ShopAdmin. This step lets you override the system’s choices of matching fields. You can choose to change the matching, or even to omit some columns from being imported. Click the “next” button.
  4. The system validates the data fields and shows a confirmation as step 3, just before beginning the import process. When you click the “Import” button, the records will be imported into your shopping cart’s database.


About data fields

Each table in the shopping cart database contains a number of fields, each of which can hold a value of a particular type. Here are the field types that ShopAdmin supports, along with examples of allowed formats. When importing data into a cell of a particular type, you can have the CSV data in any of the formats allowed for that cell.

Type name Description Allowed formats
Currency A number, possibly with a fraction part, treated as money $3,503.07
3503.37
Date A date item consisting of year, month and date MM/dd/yyyy (e.g., 5/13/2008)
yyyy-MM-dd (e.g., 2008-05-13)
dd-MMM-yyyy (e.g., 13-May-2008)
Date & time An instant in time, consisting of year, month, date, hour, minute and second Consists of a date part and a time part separated by a space.
Date part: Any of the valid date formats above
Time part:
  • HH:mm (e.g., 13:25)
  • HH.mm (e.g., 13.25)
  • HH:mm:ss (e.g., 13:25:12)
  • HH.mm.ss (e.g., 13.25.12)
  • hh:mm am or pm (e.g., 3:15 pm)
Email address student@university.edu
Fractional number A number with a fraction part 15.733
Key field Contains the unique value used to identify a record, e.g., the Product ID A number, also discussed below
Link (or Reference) A reference to another table A number, also discussed below
Phone number Any string containing numerals only.
Picklist A value chosen from a predetermined list of possible choices Any of the valid choices in the picklist (ShopAdmin ignores case when matching these values)
String A short string, shown in one line.
Text A long string, shown in multiple lines.
URL for image A URL that refers to an image, shown in the browser as the image
Web URL A URL that is shown in the browser as a link that opens in a new window
Whole number A number with no fraction part
Yes/No A value that is either “yes” or “no” The strings yes or no, or any of y, Y or 1 to denote “yes”, anything else to denote “no”
Zip code A valid US zip code, either 5-digit or 9-digit 02182 or 87324-3535

Obtaining info about fields: For each record type (Customer, Order, Product, Manufacturer, etc), you can get the list of available field names, their data types, and whether they are required:

Key fields: Each record type has a particular field designated as key field. For example, Customer ID is the key field of the Customer record. You can see the name of the key field in the table info view, or in the downloaded list of fields.

Link (or reference) fields: Some fields of one record type may link (or refer) to other record types. For example, the Customer ID field of the Order record is a link to the Customer record of the customer who placed the order.
Import vs update: You import or update data in batches of records. In any given batch, you can either import new data or update existing data, not both.


CSV file structure

When importing new records:

  • you must not include the key field in any of the records. (If your CSV file includes a column for the key field, simply map that column to “Do not import” in the second step of the import wizard.)
  • you must include every field that is marked as “required”. For example, when importing new Customer records, the customer’s name and email address are required fields, among others.

The system automatically generates the values for the key field of each new record.

When updating existing records: