Master Item Import

Owned by Former user (Deleted)
Last updated: Dec 31, 2018 by Kimber Morrison (Unlicensed)
6 min read
Overview

The item import can be done with as little as three entries (ItemID, Name of the product, and price). All other entries are optional. Use the headers provides or other headers can be mapped to those known by the system. After submitted the import, you will be prompted to enter an email address to receive the results of the import (the email will notify you of the number of successfully imports items as well as the failures).

To import master items into the database; this is a Power User function, that is using the same mandatory fields as the user interface to upload items directly into the CAS database. User should be aware that how data is entered into the spreadsheet and imported will be how it directly correlates to the CAS and POS. So if a UPC has a space before the numbers it will upload with that space and may not be readable from a barcode. User who aren't comfortable with creating an import should look at entering items through the Creating an Item

Tips for a Successful Import

On the csv import file - definitions of the field entry options:

Mandatory fields:

ItemID: Unique item ID should be entered here which are alpha numeric characters (do not use special characters or spaces).(Max Length 15 characters)

Name: Unique name for the item should be entered (no special characters such as -, *, &, etc) (Max Length 50 characters)

SellingPrice: Selling price for the items should be entered.


Not mandatory:

UPC: Unique UPC is entered here with alpha numeric characters (do not use special characters or spaces). The UPC can be an extension of the
Item ID or completely different. But it should be unique. (Max Length 15 characters). If not entered, default value is ItemID

Department: The department to which the item belongs should be entered (This is the name of the department, not the ID). If creating a new department, enter in a department that does not exist and the Import will automatically create it. (Max Length 50 characters). If not entered, default value is Default

Category: The category to which the item belongs should be entered (This is the name of the category, not the ID). If creating a new category, enter in a category that does not exist, the import will automatically create it.  (Max Length 50 characters) If not entered, Default value is Default

SubCategory: The sub category to which the item belongs should be entered (This is the name of the sub category, not the ID). If creating a new sub category that does not already exist, the import will automatically create it. (Max Length 50 characters) If not entered, Default value is Default

TypeID: If the item is a store item the ID is 1, if its a service item its 2, if its a package item its 3. (NOTE: If created a package (type 3) Status should be set to Discontinued - as the package becomes active once items are added to the package). If not entered, default value is 1

QuantityOnHand: Quantity on hand number is entered.  If not entered, default value is 0. If a quantity is entered without entering a source or source ID, the quantity will be placed in Store ID 1 as the default

Status: Enter the word "Active" or "Discontinued", depending on what the user wants as the status of the item. If not entered, default value is Active.

SupplierName: The Vendor Name associated to the item should be entered. If not entered, default value is Unassigned

Serialization Level: Enter 0 if no serialization is on the item, 1 if always, 2 if sometimes. If not entered, default value is 0

No Discount Flag: Enter 0 if no discount flag is set on the item, 1 if there is a no discount flag on the item. If not entered, default value is 0

Source ID: Enter the ID of the store or warehouse that will given the quantity of the items. That QTY can then be transferred to the store in lieu of receiving

Source Type: Enter S for Store or W for Warehouse

UnitOfMeasure: ID for PO unit of measure. If not entered, default value is blank

CostPrice: The cost price of the item is entered here, if its left empty then its taken as zero by the system.  If not entered, default value is 0

TaxID: The tax ID associated with the item is entered here.This tax will be processed during sales transactions. If not entered, default value is Not Tax

DiscountID: The discount ID attached to the item is entered here. This discount will be processed during sales transactions. If not entered, default value is blank

RoyaltyLevel: The royalty ID associated to the item is entered here. (Not utilized option)

Image: Enter the URL for the image. If not entered, default value is blank

ReorderLevel: The reorder level number is entered for the item, so that when it reaches this quantity, the item will show in the reorder report. If not entered, default value is blank

ReorderQuantity: The reorder quantity number is entered for the item, this is the quantity that is to be reordered.

MinimumLevel: The minimum level to which the quantity of the item can be reached is entered here.

MaximumLevel: The maximum level to which the quantity of the item can be reached is entered here. 

Size: The Size of the item is entered here (This is the name for the size, not the ID). (Max Length 50 characters). If not entered, default value is blank

Style: The style of the item is entered here (This is the name for the style, not the ID) (Max Length 50 characters). If not entered, default value is blank

Color: The color of the item is entered here (This is the name for the color, not the ID) (Max Length 50 characters). If not entered, default value is blank

Brand: The brand of the item is entered here (This is the name for the brand, not the ID) (Max Length 50 characters). If not entered, default value is blank

Season: The season of the item is entered here (This is the name for the season, not the ID) (Max Length 50 characters). If not entered, default value is blank

TaxInc: The tax inclusive item is indicated by filling this field with 1 or left blank for tax exclusive. If not entered, default value is blank

PriceBookID: If item will be part of a pricebook, enter pricebook ID. If not entered, default value is blank

Serializationlevel: If item will have serial numbers associated with it "Always" enter 1, "Sometimes" enter 2. If serial number will not be added to the item leave blank

NoDiscountFlag: If item will not be discountable enter 1. If the NoDiscount Flag does not apply leave blank

SourceID: If Inventory quantities are being added, enter the source Id (S for store or W for warehouse) depending on where you want the quantity added

Source Type: If Inventory quantities are being added, indicate which store or warehouse you want the inventory added to (normally Store ID 1)



Actions performed on successful import:

On Successful import of the master item are saved in the item table in the database, and the QOH updates to the corresponding Store/Warehouse

Attributes and Vendors

After you have selected you tax ID you can enter your department, categories, sub-categories, style, vendor, etc.  For the existing departments, cat, sub-cat, and other attributes, you must enter the name that you have saved in your data base. If any change or misspelled you will have added a new departments, cat, sub-cat, and other attributes. This can help if a new attribute/department is introduced to the merchant

Entering through the CAS: Vendors, Departments, Categories, Sub-Categories, Size, Color, and Style Creating Sizes

Imports: Vendors, Departments, Categories, Sub-Categories, Size, Color, and Style

 

To Download a Sample CSV

User can navigate to the Utilities>Advanced User Imports; and select the item management tab which will bring up the dialogue box in which you can download the sample was well as save the finished import

 

Entering Data

Once the sample is opened in Excel or another spreadsheet reader, the user should see an example item entered in the fields. This can be used as reference as to what is needed in the fields. Be aware that no changes should be made to the column headers as they are formatted to the database and are needed for a successful import. 

If numbers have leading zeros there are two options to retain that data, so Excel doesn't auto-format or place it in to scientific notation.

One is to set the entire sheet to read as text











The other option will be to set the field to the amount of digits needed



















Tip: When opening a CSV, in Excel or a spread sheet reader, to retain leading zeros the user must right click on the CSV file and open in note pad and save or edit in note pad. More information on opening/saving CSV files while preserving data.