Table of Contents
Importing General Articles
Importing Stock Articles is required in the following situations
- Initial go live
- Taking on a new supplier with many products
- Supplier updates
The system has a standard layout that can be used to upload from excel
- Check your spreadsheet is in the layout attached (note column headings are important)
- Ensure all the following has been done already
- All the Article Categories in the spreadsheet have already been created in the system
- All suppliers have been created in the system - the supplier column contains the supplier code
- You have adhered to the field limit for all the fields being uploaded
Import to Test - before importing to live
To avoid a difficult process of removing the articles imported - first test the upload and the results you get in test.
Check some of the transactions you expect to make - sales orders, etc.
How to import
To import Articles to the system - note you will need the Secure Feature "Is Stock Manager"
Drag the spreadsheet over the Stock Articles Screen
Action - Import Stock Articles
Then check the results.
Importing Articles using supplier specific layouts
If you have suppliers who regularly send you new or updated Article definitions in a particular format - then talk to us. We can build a specific import routine for the spreadsheet they send you.
To import Articles to the system - note you will need a /wiki/spaces/SI/pages/33054890 with "Stock Manager" from Secure Features
- Navigate to "upload supplier Articles"
- Choose the supplier name (determines the spreadsheet expected layout and hence import routine to use)
- Drag the spreadsheet over the screen - choose load to server
- Action - Upload to supplier X
- Review the results
Column Headings for Standard Import Spreadsheets
Columns that do not match up with one of the import names will be ignored and the first line on the import result will be a list of the ignored columns
Useful Google Sheet calculations:
- Look for a material in the suppliers name (use named ranges) =INDEX(Materialresultlist,MATCH(TRUE,ISNUMBER(SEARCH(Materiallist,B2)),0))
- Note ensure the list of materials is sorted longest at the top - then "Shiny Red" will be found before "Red"
- Export from the item Profile for Material to find a list of possible values for that material
- If the supplier provides the name all in caps = Proper(a2)
- may need to adjust Ml to ml etc after
| General Information | Measures | supplier related | Pricing Related | Food Related | clothing | Warehouse related |
|---|---|---|---|---|---|---|
SKU
| packing factor
| supplier name item OR supplier name part
| costprice
| serving per pack OR ServingPerPack | size | |
Name
| pack size
| Supplier OR manufacturer
| av cost | ingredients | sizeseq | |
description
| DimUnit
| their part number OR supplier part number | sellprice
| certification OR lifestyle | gender | |
short description
| width | Brand | RRP
| Serving Size | fabric | |
category
| height | Range
| inner buy price
| Claims | manuid | |
pack size
| depth | Warranty Period
| inner qty OR inner quantitiy
| NipServe |
| |
UOM
| unit width | carton qty
| ||||
unit qty OR unit quantity
| unit depth | carton buy price
| ||||
Colour
| unit height | carton sell price
| ||||
storage
| massunit | pallet qty OR pallet quantity
| ||||
name pkg OR name package | netmass | pallet buy price
| ||||
Xref OR extref OR ext ref
| grossmass | pallet sell price
| ||||
| ean | measure | alt buy price
| ||||
| Barcode | measure unit | |||||
| custom name | ||||||
style group OR productgroup
| ||||||
assembly
| ||||||
| color | ||||||
webdescription
<ul style="line-height: 20.8px;">_x000D_ <li> Poly/cotton/Spandex</li>_x000D_ <li> Roll-up long-sleeve with 1-button tab</li>_x000D_ <li> Double back yoke</li>_x000D_ <li> Triple-needle stitching</li>_x000D_ <li> 2 front pockets with flaps</li>_x000D_ <li> Available in gray, white or black</li>_x000D_ <li> Sizes S-2XL</li>_x000D_ </ul>_x000D_ | ||||||
| WebFeatures | ||||||
ANY SIMPLE PROFILE TYPE = *Profilename
|
eg item profile
Importing Packs as a single row - both carton and each at the same time
Packs are a special type of Assembly - a SKU that breaks into a related smaller quantity.
- A carton/sleeve cannot be included in an 'updates only' import as this is only updating existing items
- Assembly must be "Pack"
- The line must have a carton qty.
- The SKU and the SKU suffixed with -S must not already be in the system.
- The name must be prefixed with INNER, SLEEVE, SINGLE or SINGLE UNIT.
- There will be no price breaks set up.
- If any of the above rules are not met then the line will be processed but not as a carton/sleeve.
All of the columns entered will be saved in the sleeve item except:
- Assembly will be No.
- SKU will be suffixed with -S, unless it already has a -S suffix.
- There will be no price breaks, i.e. inner, carton and pallet qty and buy and sell prices will be ignored.
The same information will be saved in the carton item except:
- Assembly will be Pack.
- SKU suffix of -S will be removed if present.
- Name prefix of INNER, SLEEVE, SINGLE or SINGLE UNIT will be removed.
- Default sell price will be carton sell price.
- Default buy price will be carton buy price.
- Average cost will av cost multiplied by carton quantity.
- Pack size will pack size multiplied by carton quantity.
- There will be no RRP or alternate buy price.
- Assembly component quantity will be carton qty.
- There will be no price breaks, i.e. inner and pallet qty and buy and sell prices will be ignored.
Price Breaks that are not a special type
Importing price breaks that are not a special type requires a separate sheet - import into prices tab
- SKU
- Qty
- sellprice
Importing Symptom Prices
Provide a spreadsheet with five columns
- SKU code of the Repair Item
- Symptom (must match the Symptom Description in the Symptom Master List)
- Likely Price - lower price
- Alternate Price - higher price
- Wholesale Price - price to charge the Stores
Drag the Excel File onto the Symptoms Page
Importing Stock Images
Provide a spreadsheet with two columns
- SKU code in the system
- URL for the related image
Importing Stock Min / Max levels by warehouse
The stock Articles must exist in the system (will get error messages for all articles that do not exist) + Users Must have secure feature "Is Stock Manager"
Columns
- SKU
- Min
- Max
Warehouse screen > choose the warehouse > Drag XLS over the warehouse
Action > Import spreadsheet
For those interested - this is what the code looks like.
if (c != null)
{
string col = c.ToLower();
if (col.StartsWith("*"))
{
string profile = col.Substring(1);
int? profileId = ItemProfile.GetByName(Session, profile);
if (profileId.HasValue)
{
iProfiles.Add(i, profileId.Value);
}
else
{
Session.TextLog.add("Unknown custom profile " + col);
}
}
else if (col == "category") iCat = i;
else if (col.StartsWith("manufactur") || col=="supplier") iSupplier = i;
else if (col == "brand") iBrand = i;
else if (col == "range") iRange = i;
else if (col == "name") iName = i;
else if (col == "uom") iUOM = i;
else if (col.Contains("name") && col.Contains("custom")) iCustomName = i;
else if (col.Contains("purchase") && col.Contains("multiple")) iPurchaseMultiple = i;
else if (col.Contains("unit") && (col.Contains("qty") || col.Contains("quantity"))) iQtyUnit = i;
else if (col == "sku") iSKU = i;
else if (col == "colour") iColour = i;
else if (col.Contains("storage")) iStorage = i;
else if ((col.Contains("style") && (col.Contains("group") || col.Length < 10)) || col=="productgroup") iStyleGroup = i;
else if (col == "description") iDesc = i;
else if (col.Contains("short") && col.Contains("description")) iShortDesc = i;
else if (col.Contains("sellprice")) iSellPrice = i;
else if (col == "costprice" || col== "defaultbuyprice") iCostPrice = i;
else if (col.Contains("cost") && col.StartsWith("av")) iavgCost = i;
else if (col.Contains("price") && col.StartsWith("alt") && col.Contains("buy")) iAltBuyPrice = i;
else if (col.Contains("rrp")) iRRP = i;
else if ((col.Contains("their") || col.Contains("supplier")) && col.Contains("part") && col.Contains("number")) iSupplierRef = i;
else if (col.Contains("supplier") && col.Contains("name") && (col.Contains("item") || col.Contains("part"))) iSupplierPartName = i;
else if (col == "extref" || col == "ext ref" || col=="xref" || col.StartsWith("import")) iExtRef = i;
else if (col == "dimunit") iDimUnit = i;
else if (col == "width") iWidth = i;
else if (col == "height") iHeight = i;
else if (col.Contains("depth") && col.Contains("unit")) iUnitDepth = i;
else if (col.Contains("width") && col.Contains("unit")) iUnitWidth = i;
else if (col.Contains("height") && col.Contains("unit")) iUnitHeight = i;
else if (col == "depth") iDepth = i;
else if (col == "massunit") iMassUnit = i;
else if (col == "netmass") iNetMass = i;
else if (col == "grossmass") iGrossMass = i;
else if (col.Contains("measure"))
{
if (col.Contains("unit")) iMeasureUnit = i; else iMeasure = i;
}
else if (col == "barcode") iBarcode = i;
else if (col == "assembly") iAssembly = i;
else if (col.Contains("serving") && col.Contains("size")) iServingSize = i;
else if (col.Contains("name") && (col.Contains("pkg") || col.Contains("package"))) iPackageName = i;
else if (col.Contains("size") && col.Contains("pack")) iPacksize = i;
else if (col.Contains("packing") && col.Contains("factor")) iPackingFactor = i;
else if (col.Contains("serving") && col.Contains("per") && col.Contains("pack")) iServPerPack = i;
else if (col.StartsWith("ingredients")) iIngredients = i;
else if (col.StartsWith("certification") || col.StartsWith("lifestyle"))
{
certifications.Add(i, col.Replace("certification", string.Empty));
}
else if (col.StartsWith("claims"))
{
dietaryClaims.Add(i, col.Replace("claims", string.Empty));
}
else if (col.StartsWith("nip") && !col.Contains("serve"))
{
int p = col.IndexOf("(");
if (p > 0)
{
col = col.Substring(0, p - 1);
}
string text = col.Replace("nip", string.Empty).Replace(":", string.Empty).Trim();
int nipId = ArticleNIPItem.IdForText(Session, text);
if (nipId > 0) iNIP.Add(i, nipId); else Session.TextLog.add("No NIP item found for " + text);
}
else if (col.StartsWith("inner"))
{
if(col.Contains("qty") || col.Contains("quantity"))
{
iInnerQty = i;
}
else
{
iInnerBuyPrice = i;
}
}
else if (col.StartsWith("carton"))
{
if (col.Contains("qty") || col.Contains("quantity"))
{
iCartonQty = i;
}
else
{
iCartonBuyPrice = i;
}
}
else if (col.StartsWith("pallet"))
{
if (col.Contains("qty") || col.Contains("quantity"))
{
iPalletQty = i;
}
else
{
iPalletBuyPrice = i;
}
}
else
{
Session.TextLog.add("Column ignored: " + c);
}
}
i++;