Also available in these formats:
Welcome #
These guidelines have been written for a training context in which a training manual will be the main delivery source for the educational experience. However, if requested by the customer, this manual can be used by an instructor to train individuals on the subject. The option of downloading this manual from the internet will also be available.
Helpful Background #
This manual is written for an audience who may or may not be familiar with the Avetti Commerce software. Operation of the software requires the user to be comfortable with computers and how software tools are used. Understanding the Avetti Commerce Store Administration pages is very helpful knowledge to have prior to reading this manual.
Overview #
This document explains how exporting and importing spreadsheet works, and how useful using spreadsheets can become. Spreadsheets allows to view all information set in a certain topic and the affect a spreadsheet has over the admin pages.
Exporting Spreadsheets #
Some of the sections on the store,such as Shipping have their own Export buttons on their manage section page. For example, go to Settings | Shipping Rates, an Export button will be found at the bottom of the page which can be used to download the Shipping.xls file. All other important data such as Categories, Item information, Item properties, Attributes and Item SKU can be exported/ imported using the Import link next to the store link on the Sysadmin page.
The Import/Export Store Data From Spreadsheet page looks as in figure below. Click the sample link to see what the spreadsheet would look like for a specific functionality. However, for populating data, we recommend to Export the file rather than using the sample file.
Once known of which files need to be worked, the first step is to export the file. For example, to create the spreadsheet that holds information on all item prices, check the Export Item Price check box and click Export. The downloaded file would have just the column names once opened start populating data in the proper columns.
Note: Do not add/delete new columns on any of the spreadsheets. Some columns may have been deprecated, but never delete such columns. Just leave them blank.
In the following sections we will have a look at the files that need to be imported and what the content for these files should be like. We will also look at the order in which these files are to be imported.
Export Store Data #
Found near the bottom of the page is the Export Store Data. This section allows the ability to export information of a certain sections of the site allowing the ability to view any of the topics selected, multiple sections can be selected for a group download.
Export Item Information:
Exports all the items and their details in excel format.
Useful to see all the items in the store and the state of that item.
Export Multi Language For Items:
Exports all items and details in excel format each language is split into tabs.
Export Category Information:
Exports all categories and details in excel format.
Export Multi Language For Categories:
Exports all categories and details split into tabs for each language.
Export Item Price:
Exports all item prices in excel format and shows what items contain what price and how the price is behaving on the site.
Export Multi Language For Item Properties:
Exports all item properties each tab is in a different language.
Export Item Properties:
Exports all item properties and their details.
Export Multi Language For Item Attributes:
Export all attributes and the languages for each attribute.
Export Item Attributes:
Just the basic infomration on Item Options with only the main language and details. No other languages will appear here.
Export Multi Language For Item Options:
Exports all item options each tab is in a different language.
Export Item Taxrates:
Exports all tax rates and details.
Export Items In Froogle XLS Format:
?
Export Report:
Exports all reports and details in spreadsheet format. * Note the download will download as a zip file, containing the spreadsheet.
Export Retailers:
Export all retailers and details in spreadsheet format.
Export Customer Forum:
Button: Deletes the current customer forum data.
?
Export Related Items:
Export all items related to other items in spreadsheet format.
Export Store Template:
Export all store templates in zip format.
Export Bundle Items:
Export all bundle items and details in spreadsheet format
Export Add-Ons Items:
Export all item add-ons and details in spreadsheet format
Export External Giftcard Mapping:
?
Export Category Properties:
Export all category properties and details in spreadsheet format.
Export Item Sku:
Export all items containing other skus in spreadsheet format.
Export Question:
Export all questions and details in spreadsheet format.
Export Abandoned Products:
Export all product purchases that have been abandoned in spreadsheet format.
Item Reviews:
Export all reviews on items in spreadsheet format.
Export Order Approvers:
Export all orders that have been approved.
Multi Valued Properties:
Export all multi valued properties and details in spreadsheet format.
Once all files that are needed to be exported are selected click the Export button to export all files.
Importing Spreadsheets Overview #
This document explains how data can be imported to the Avetti system using spreadsheets. Spreadsheets are a great way to import large volumes of data such as item information, shiping configuration and payment set up on to the store. Just as in any system, when spreadsheets are used for data import, make sure to upload files in the right format with all the required information for a successful import. All spreadsheets must be in .xls (Microsoft Excel 97-2003 Worksheet) format. If any of the required fields are missing, the system would report an import error with specific details as to what information is missing. NOTE: After all spreadsheets are imported on the preview application, go to Advanced | Solr Items and click the Full Rebuild button. Also go to Advanced | Solr Categories and click the Rebuild button. Then the next time its published check the “Force Rebuild of Solr Items and Cats” checkbox on the Catalog | Publish to Shop – Publish/Publish Status page when to launch a publish to shop.
Importing Categories Spreadsheet #
This is the first spreadsheet that is needed to import to the store. The ‘items’ spreadsheet has a column that specifies what category an item belongs to and unless category.xls was already imported, item.xls import will generate an error. The first tab on category.xls looks like in figure below.
This tab is used to define the category structure. The system currently supports categories 5 levels deep. Use the columns appropriately to define categories and sub categories. In figure above, we have Home as the main category and Customer Service and Contact Us as sub categories.
The next tab is used to list all the categories on the store and its properties. ‘Category full name’ should be unique and cannot duplicate the name of an existing category. ‘Short Name’ is the display name of the category. ‘Meta keywords’ and ‘Meta Descriptions’ are used to provide search engines information regarding this category. ‘Page title’ is the page title for this category in the store pages. Thumbnail Image column is used to specify the location of the thumbnail image. Eg: /20070723002/assets/category/thumbnails/Camcorders.jpg
Similarly, Group Image column can be used to specify the path for Group (large) image.
We have 10 property-value pair columns that can be used to assign a property and corresponding value for a category. For example, we have a category property called ITEM COUNT that keeps track of the number of items in a category.
‘In Search?’ column hides the items of this category in search results if set to ‘No’. ‘Redirect if 1?’ column if set to ‘Yes’, redirects the click to the item page if there’s only one item in the category. Long Desc and ADDITIONAL DESC columns are used for category descriptions. Its possible to manually specify the SEO Url for the category using the SEO url column. However, if the Auto Generate SEO Url feature is active later on, these entries will be overwritten. To not overwrite, set the ‘SEO url locked’ column to ‘True’. Template column specifies the template to be used for this category.
The final tab on this spreadsheet is used to define content areas for categories. Specify the category name on the first column and the content area name goes on the second column. ‘Theme’ column is used to specify the theme used (Eg: Desktop_2) for this content. Set Active column to Y to activate the content area and also set the start and end dates using the next 2 columns. Columns H to O in figure have been deprecated. The content column is used to input the actual content in the content area.
Once all the tabs have been populated, import the spreadsheet on to the store. Go to the Import/Export Store Data From Spreadsheet from the Sysadmin page and click the Browse button next to Categories Spreadsheet File Name. Open the file from the hard drive and once Status says ‘Success’, Click Import to import the file.
Note: After importing the Category spreadsheet do a Solr update for categories. Go to Advanced | Solr Categories and click the Rebuild button.
Importing Items Spreadsheet #
Once the Categories have been imported, go ahead and import Item information. To download the Item.xls spreadsheet to work with, go to the Export Store Data section on the Import/Export Store Data From Spreadsheet page, check the Export Item Information check box and click the Export button. Use the Download link to save the file locally.
Item Information spreadsheet imports information such as itemcode,Item title, Price, descriptions, Image paths, keywords, content areas,item-category relation and so on.
Look at the first tab on the spreadsheet, pretty much all columns are self explanatory. we have columns for item code, title, short description, long description. taxable or not, list price, actual price and so on. Ship type defines which Shipping group will be used for this item. So if more than one shipping group is being set up on the store, make sure to specify the right group name here.
Note: Item code cannot have spaces or dots in it.
Columns J to M as in figure are used for long descriptions. Also have Manufacturer and Manufacture Part Number column to specify Manufacturer details. On Sale Image is used to specify the location of image that will be displayed if the item is included in the ‘On Sale’ section. Sort Hint column has been deprecated. Set Available to Yes to make the item available on the store. Set Hide in Search to Yes if the selected item doesnt want to show up on search results. Instock column is used to specify if the item is in stock or not. Column U to Z is used to define product weight, dimensions and their units.
Note: Product volume is calculated as Length× width× height
Cost column can be used to store a cost value for this item. Currently none of our reports use this, but to generate a Profit Report (Profit= Price- Cost), use this colum to set individual costs for each item.
Meta keywords and Meta descriptions columns can be used to provide search engines with relevant information regarding products. Page Title is the page title for the item page.Notes for Catalog Designer can be used to input any notes or comments about this item. This will not be visible to shoppers. Preorder Item column can be used to set if the item is on preorder. Create DTOs that use this value to show additional content for this item.
Columns AG to AZ are used to specify the paths for item images. The system supports up to 5 large, item, small and thumbnail images.
For large images, the path is /assets/items/largeimages/imagename.jpg if 2 large items are uploaded, upload the 2nd image in the directory/assets/items/largeimages/imagename-2.jpg. Up to 5 images for each size can be uploaded.
Note: Only the first image path column for a particular size needs to have path information.
Remaining columns can be left blank. So for example, only column AG needs to have the path. AH to AK can be left blank. The system does not check these additional columns, but instead assumes that remaining images are in the same directory with the name ‘imagename-2’,’imagename-3’ and so on.
Similarly, Image path for 1st item image should be /assets/items/images/imagename.jpg.
Image path for 1st thumbnail image should be /assets/items/thumbnails/imagename.jpg.
Image path for 1st small image should be /assets/items/small/imagename.jpg.
The Parent Item Code column is available to link this item to a parent item. Display Using Template column is used to specify the template used to display the item page. Availability column is used to set the date after which the item will be discontinued on the store. SeoURL column sets the Seo URLs for this item. This information will be later used to generate sitemaps.
The second tab on the Spreadsheet defines which category an item would belong to. To add an item to a category, leave the Action column as ‘add/edit’, specify the item code on the ‘Item Code’ column, specify the category name on the Category column and set the position of this item within this category.
Note: If the ‘Automatically Add Items To Parent Categories’ check box is selected in Site Admin Preferences, any item that is attached to a subcategory gets automatically attached to its parent categories as well. For example, consider the following directory structure:
A
B
C
In this case, if an item is attached to category C, the same item will automatically be attached to categories B and A as well.
To remove an item from a category set the action column value as ‘Delete’ for that particular entry and import the file back to the store.
The Content tab on this spreadsheet is similar to the content tab on Categories.xls except that here we are defining content areas for an item page.
Importing Shipping Data #
Once Categories and Items have been imported, Shipping data can be imported next. The Go to Settings | Shipping Rates and click the Export button at the bottom. On the status page, use the download link to download the Shipping.xls file locally on the hard drive.
The first tab is used to define provider information such as provider code, Provider name, courier used by the provider, currency used by the provider, Offer code if exists, Weight type (Pounds, Kilograms), Markup, Handling and so on.
Markup and handling are used for providers that use couriers that do not have a flat rate shipping option. In this case, the courier company will fix a base rate. And set the mark up by %. So if courier rate is $10 and the mark up % is 0.1, total shipping charge would be $11.So $1 is the profit.
Handling cost can be added to get more profit out of shipping. In the previous scenario, if handling is set to $2, total shipping charge would be $13. so the profit is $1 from mark up and $2 from handling.
Column Option 8 is used to specify types of customers for which the provider will be active, such as business invoiceable, business non invoiceable and so on. Column Available should be set to YES to make the provider Available and Default can be YES if the provider is the default provider.
The second tab is used to define the shipping groups available and which one of them would be the default group.
The third tab then associates shipping providers with shipping groups. Use the first column to input Shipping group name and the second column to specify a provider in that group. Positon determines the order in which the providers will be displayed within the group. Set default to YES if this provider is the default provider in the group.
The Method tab is used to define all the shipping methods available. Use the shipping provider code column to specify what provider this method belongs to. Option 1 column has been deprecated. Option 2 column specifies the Prompt text. This is what the shopper will see on the store when he selects this method. Position sets the position among other methods on the store.
The algorithm tab was included to define custom calculation methods. However, this feature has been deprecated.
Rates tab is used to define all the flat rates on the store. Use the first 2 columns to specify what provider and method this rate belongs to. The remaining entries are as in the ‘Add/Edit Shipping rates’ admin page.
Use the Courier package tab to define what packages are available to a courier. Input values the same as on the Manage Courier Package page.
The Shipping Package tab is used to associate packages with Shipping Providers. Property name and Property value columns can be used to set this package to be used only for shipping items with a particular property value ( Eg: Blue T-shirts).
Use the Shipping From tab to input Warehouse details the same as on the Manage Warehouse Location admin page.
The Ship To Tab is used to input information as on the Manage Ship To rules admin page.
Note: When defining multiple ship to rules for the same warehouse, create multiple entries with the same Warehouse code.
Use the Ship to Exceptions tab to define shipping exceptions same as on the Manage Ship to Exceptions admin page.
Importing Item Properties #
The Item Properties spreadsheet can be imported immediately after the Items spreadsheet. This spreadsheet defines all the item properties in the store, list of values they can have as well as properties/ values for each item. To start working on the file, first export the file from Import/Export Store Data From Spreadsheet page. Mark the checkbox that says and click Export and then Download to download the Properties.xls file.
The Property Definitions tab looks as in figure above. First column lists all the item properties on the store. Second column specifies the type of value it can have. Text Value lets the user input the value as text. List value lets the user select the value from a pre set list of values. Notice the values in columns H to K in figure. These columns specify the values in the list.
Column C sets the property number and D is for description. Note that Property number should be unique to a particular property. Column E (Auto create) if set to YES, automatically assigns this property to a new item when the item gets created. Columns F to Y are for specifying list values for list type properties.
The next tab ( Item Property) defines what properties an item has and what values it can have. The first column, leave it ‘add/edit’ when adding new entries. If to remove a property for an item , set this to ‘Delete’. Second column specifies the Item code and third column is the property number. So for items with several properties, multiple entries with the same item code but different property numbers. Column D specifies the property name and E is for Property Description. Use Column F, Property value to define the text value this property has for the item.
Once all entries have been made, use the Properties Spreadsheet File Name section on Import/Export Store Data From Spreadsheet page to import to store.
Importing Attributes #
Download Productoptions.xls from the Export Item Attributes section on Import/Export Store Data From Spreadsheet page. Spreadsheet looks as in figure below.
Item Attribute Map tab lets the mapping of attributes to items on the store. Leave the Action column as add/edit when adding entries. While detaching an option from an item, change this to Delete. Columns B and C associates an Item code to the Attribute. Order column specifies the order in which the options get displayed on the admin pages. So if only 2 options ( Color, Size) for an item are available. Set order for color as 1 and that for size as 2. Roll image column has been deprecated.
The Attributes tab lets the attributes been defined on the store. The Action column as usual lets the ability to add/delete attributes. Column B and C are for attribute name and type. If the attribute is of text type, where user inputs values as text, set the column to ‘Text’. If values are as per a pre configured list, set the column value as ‘Details’. Column D, Prompt text has been deprecated. Column E, Text Before is what the shopper sees on the item page as the option name. Drop name is what the shopper sees on the drop down menu list before they click it.
Column G, Sku Enabled, needs to be set to Y if this attribute is associated with parent and child items. Columns H and J are deprecated. Column I, Attribute code specifies the Attribute code for this attribute.
The Attribute options tab defines what values an attribute can have. The Action tab gives the ability to add or delete values. Column B should hold the attribute name and column E should have the option value. So for an attribute ( Color) with multiple values ( Red, Blue, Green), create multiple entries. Column C determines the order in which these options will be displayed to the shopper. Set Default column to Y if one of these values need to be the default value. Column F is for the attribute option code. Column G, Cart name is the actual option that shopper sees on the store. Column H can be left blank.
Once the spreadsheet has been populated, use the Attributes Spreadsheet File Name section on Import/Export Store Data From Spreadsheet page to import to store.
Importing Price #
This allows items to obtain certain prices using spreadsheets, and creating quantity price changes. The spreadsheet creates the full view of all the prices and the admin pages can do the same thing but isn’t as organized as spreadsheets, as everything is laid out. For more information on prices go to Catalog | Prices on the admin page.
Action: Add/Edit means if the price is already taken edit the product with this information, if the price doesn’t already exist add the product.
Shopper Group: If the product price belongs with a shopper group if so add the shopper group name to this column, if it applies to all groups leave blank.
Offer Code: If the offer code is needed to access this price, if not leave blank.
Product Code: The unique identifier of the product that is using the price
Sku: The sku of the price, if left blank the sku is the product code
Item Weight: The weight of the product this is mainly for shipping purposes.
Start Date: The date the product will be available
End: The last date the product will be available
Priority: Priority over other prices
List Price: The listed price of the product
Quantity_1: The quantity needed to cause a price change
Price_1: The original price.
Quantity_2: The second set of quantity needed to cause a price change
Price_2: The new price of the product after it surpasses the first set quantity needed for price change.
Quantity_3: The third set of quantity needed to cause a price change
Price_3: The new price of the product after it surpasses the second set quantity needed for price change.
Quantity_4: The forth set of quantity needed to cause a price change
Price_4: The new price of the product after it surpasses the third set quantity needed for price change.
Price_5: The final priceafter the quantity surpasses forth quantity set.
*Note the quantity price goes as followed, price 1 is the original price, the quantity 1 is the amount needed for price 2, quantity 2 is the amount needed for price 3, quantity 3 is the amount needed for price 4 and quantity 4 is the amount needed for price 5. Each quantity must be greater then the previous. Price is the price per item not total price.
Set Up Charge: The charge for setting up the product.
Currency: The currency the price is in.
Default: If price is the default price for the item.
Points: If the store gives points for item purchases.
Map: Sets the minimum price that item can have.
Distributor Code: The unique identifier of the distributor connected with the price, if there is no distributor needed leave blank.
Importing Question #
The question spreadsheet allows for questions to be created using spreadsheets, also allowing the ability to connect each question with an item code or check out order. For more information on questions go to Catalog | Personalization Elements on the admin page.
Action: Add/Edit means if the question is already taken edit the question with this information, if the question doesn’t already exist add the question.
Question Code: The unique identifier of the question.
Question Label: The label of the question to explain what it does
Question Type: The type the question is in (Edit box, Check Box, Radio button, Select Box)
Description: The text the shopper will see.
Template: The template being used for the question.
Question Page #
This tab allows the question to connect to an item and allows for the question to only be available between specified dates.
Question Code: The unique identifier of the question being used.
Item Code: The unique identifier of the product containing the question.
Page Type: The type of way the question will be displayed
Page Number: The order the question will appear on the item, and which the page the item will appear. Example:
A = 1
B = 1
C = 5
D = 2
E = 7
The order will go as followed:
A, B on page 1, D on page 2, C on page 3, E on page 4
Sequence: The order the question will appear over other questions on the same page.
Start Date: The date the question will be active.
End Date: The last date the question will be active.
Available: If the question is active or not, even if date says its active the Available must equal true for the question to appear.
Question Details #
The details about each question, allows for images to be added and well as a limit to the answer, and also allows the shopper a set amount of lines to use for the item.
Question Code: The unique identifier of the question
Question Label: The description of question.
Image Path: The image path of the image if an image is being added.
Description: The text the shopper sees
Alternative Description: The secondary text the shopper sees
Default Answer: The default answer for the question, for comparison reasons and or to input a default value for the item.
Default: If the question is the default question set over other questions.
Line Limit: If there is a certain amount of lines the user can write, helpful if the text is being put on the product, 0 being infinite.
Sequence: The priority of the question over other questions.
Start Date: The date the question will be active.
End Date: The last date the question will be active.
Available: If the question is active or not.
Question Price #
The question price allows a price to be added to the question when being used, this is helpful if the customisation for the item will cost more money.
Question Code: The unique identifier of the question
Question Label: The description of question.
Price Label: The description of the price (what the price is for).
Promotion Code: If a promotion code is needed to activate the price.
Price Type: The type of price it is.
Price: The price of the question, this is for if adding text to an item, costs more money
Start Date: The date the question will be active
End Date: The last date the question is active
Available: If its active or not.
Importing Tax Rate #
Creates new Tax rates for the store, that will apply based on country, city, province or postal code. For more information on tax rate go to Setting | Tax Rate on the admin page.
Action: Add if the tax isn’t already taken, edit if it is.
Tax Name: Name of the tax rate.
Rate Type: Where does it affect.
City: If the tax rate is set to a city
Country Name: The name of the country with the tax rate
State/Province: The province the tax rate is set in.
Postal/Zip Code: The postal code where the tax applies.
Tax Rate: The tax rate, 0.05 = 5%
Applies To: What type of items it applies to, shipping, items or both.
Priority: Priority over other tax rates.
Product Type: The type of product it applies to, leave blank for all.
Customer: The customer the tax applies to, leave blank for all.
Shopper Group: The shopper group the tax applies to.
Tax identification: If identification is needed for the tax rate.
Tax Included In Price: If tax is included in the items not the order.
Active: If the tax rate is active.
Distributor Code: If the tax is based on distributor.
Importing Retailer #
Creates new retailers for the site, allows them to have their own logo image, web link, phone number and provide their location, helpful if the retailer is creating their own spreadsheet information and can’t use the admin page, allowing the ability to just import the spreadsheet to the store. For more information on retailer go to Setting | Retail Location on the admin page.
Name: Name of the retailer
Description: Description of the retailer.
Logo Image: The logo image path of retailer.
Phone: Phone number of retailer
Web Link: Web link of retailer
Address: Address of the retailer
City: The city of the retailer
Province Code: Province code of the province, Ontario = ON
Postal: The postal of the retailer.
Country: The country of the retailer.
Latitude: The latitude of the retailer.
Longitude: The longitude of the retailer.
Importing Multi Language Items #
This is the language of each item, each tab being a different language allowing the ability to change the language of the item set within the spreadsheet. For more information on items go to Catalog | Item on the admin page.
Code: Item Code of the item
Title: The title of the item
Short Desc: The short description of the item, usually seen in store catalog.
Desc: The description of the item, in Html code or normal text
Desc 2: The second description of the item, in Html code or normal text
Desc 3: The third description of the item, in Html code or normal text
Desc 4: The forth description of the item, in Html code or normal text
Desc 5: The fifth description of the item, in Html code or normal text
*Note all description locations are based on the template using each one.
Meta Keyword: Meta keyword of the product, used for rating purposes.
Keyword: Keyword of the item, for rating and search.
SEOUrl: Url of the product.
Importing Multi Language Categories #
This is the language of each category, each tab being a different category allowing the ability to change the language of the category set with in the spreadsheet. For more information on categories go to Catalog | Categories on the admin page.
Name: The name of the category.
ML Name: The name of the category in the selected language.
Category Desc: Category description.
Meta Keyword: The meta keyword of the category, used for the search engine.
Meta Desc: The description of the meta keyword.
Keyword: For search purpose, isn’t commonly used.
Long Desc: Allows the template to contain this description or to change the description
Additional Desc: Additional description that can be added.
Prop Name: The property name that has already been created.
Prop Value: The value connected with the property name.
Prop Name 1-10 are the same as Prop Name
Prop Value 1-10 are the same as Prop Value
They are just there if the admin wants to add more properties to the category.
Importing Multi Language Properties #
This is the language of each category, each tab being a different category allowing the ability to change the language of the category set within the spreadsheet these changes will be for the language change within the store but the original name of the category will remain as the main language on the admin page. For more information on properties go to Setting | Store Properties on the admin page.
Action: Add if the property doesn’t exist but if the property does exist, edit that property with the columns.
Item Code: The item code of the item that contains the property.
Property Name: The name of the property
ML Property Name: The property name in the selected language.
ML Property Desc: The property description in the selected language.
Value: The default value of the property.
Importing Multi Languages Options #
Allows the ability to change or add languages for each dropdown value text. Each tab would be a different language for the dropdown values and not all the dropdowns need to be changed for the dropdown to work. For more information on Options go to Catalog | Item Options on the admin page.
Attribute: The code of the attribute connected to the item.
Item Code: The item code of the item that contains the product option.
Text Shopper Sees: The text the shopper sees in the dropdown.
Cart Description: The text the shopper sees in the cart.
Importing Report #
Report Name: The name of the report
Report Description: The report description to explain the report.
Source Type: ?
Class Name: ?
Input Template: ?
Active: ?
Require Category: ?
Importing Related Items #
Allows to add new types of views to a product page, with addons, you may also like and, other displays found in Marketing | Upsell/Cross-Sell in the admin pages. This spreadsheet creates new displays that will connect items to other items and choosing how each one will display within the item page. *Note that template changes may be needed to be changed to allow all the displays to work.
Item Code: The item code that will be containing the related items.
Related Value: The item code of the item connected to the main item.
Related Type: The way the item will be displayed.
Position: The position over other item codes.
Quantity: The amount of times that item code will appear on the item page.
Importing Template Zip #
???
Importing Bundle Items #
This spreadsheet allows for bundles to be created by creating the parent item code, containing other item codes that once the parent code is clicked all the connected items will be added and in the set quantity amount. *Note each row will be one child even if multiple child items are being added to one parent. For more information on bundles go to Marketing | Bundle on the admin page.
Item Code: Item code of the item that will activate the bundle.
Related Value: The item that is part of the bundle.
Position: The position over other bundle items.
Quantity: The amount of the item that will be added to the cart when the bundle item is clicked.
Importing Add On #
Add ons, are items that are added to the item when purchasing an item, this could be a promotional thing when buying the item or just a given item addon. For example the shopper buys a computer and recives a free mouse with the item or a shopper buys a laptop and gets the charger with it but the charger was made by a different company so the charger has to be added to the laptop purchase. The spreadsheet allows the ability to add addon items and connect them to their parent item that once clicked will add the items to the purchase and can give an over view of what items are where.
Item Code: Item code of the item that will contain the add on items.
Related Value: The item that is part of the main item.
Position: The position over other addon items.
Quantity: The amount of the item that will be added to the cart when the main item is added to the cart.
*Note items must be created before using this spreadsheet.
Importing Category Properties #
This spreadsheet allows properties to be added to categories already created in the site and set the value of that property for the category to display or use.
Category Name: The name of the category containing the property.
Property Number: Priority over other properties set in the same category
Property Name: The name of the property being used.
Property Value: The value for property value in the category
*Note that categories must be created and or imported before changing the category properties.
Importing External Gift Card Mapping #
Action: ?
External Item: ?
Local Item: ?
Gift Card Provider Name: ?
Importing Item Sku #
Allows for items to contain other items based on their sku which allows the ability to change item skus based on the product option selected for that item, if the item contains a dropdown. If no product options are available for that product then there is no need for any item skus beside the parent.
Parent Item: The code of the item that will contain the child skus.
Sku Item Code: The item code of the child skus connected to the parent
Attribute: The attribute being used with the item sku.
Option: The text the shopper will see to activate that sku.
Available: If the child sku is available or not.
*Note that the items must be created before creating item skus.
Importing Item Review #
Allows reviews to be created, deleted, or edited for products creating a login name and date for that review. If a review is deleted the spreadsheet allows the ability to put a reason for why the review was deleted for other admins. For more information on reviews go to Catalog | Item Review on the admin page.
Action: This allows 3 actions to happen to a review Add (Add the review), Delete (Delete a review) and Edit (Edit a review)
Item Code: The item the review is being put on.
Login Name: The email of the user putting the review.
Nick Name: The nickname of the user putting the review.
Rating: The rating given from the review 1-5.
Title: The title of the review the shopper will see.
Review: The review text that the shopper will see.
Review Date: The date the review was posted.
Status: If the review was the first one created for that item.
Reject Reason: The note explaining the reason why the review was removed.
Importing Item Video #
Allows videos to be added to the product page, by selecting the item code, the video code and posting the link url of the video into the spreadsheet allowing for a video to be added to the item. This is useful as the spreadsheet can create multiple videos and connect them to items in a simple and organized manner as for an item to contain a video on the admin page only a single item can be selected and changed to contain a video not multiple items using multiple videos.
Action: Add the video to the item, if it already exists edit the video with this information.
Item Code: The item code of the item containing the video.
Video Code: The unique identifier of the video.
Title: The title of the video for the page.
Link: The link of the video in url format.
Active: If the video is active or not on the page.
Importing Order Approvers #
Order Approves Group #
Action: Add if an order is not already existing or edit the already existing one.
Vendor ID: Vendor suppling the order.
Group Code: The group code of the group making the purchase.
Group Name: The name of the group making the purchases.
Limit to Offer: ?
Limit To Shopper: ?
Limit To Shopper Email: ?
Payment Method: The payment used to purchase the order.
Email Message: The template used to create the message
Checkout: The template used to create the checkout message.
Approved Message: The template used to send an approved message after the order.
Rejected Message: The template used to send a reject message after an order has been sent.
Resend Approver Email Hours: The time the shopper can receive a resend email.
Order Approves #
Action: Edit if already existing, add if not.
Vendor ID: Vendor suppling the order.
Name: The name of the shopper making the order
Email: The email of the shopper making the order
Approved Orders From: Minimum price of the order.
Approved Orders Up To: Maximum price of the order.
Active: If the order is active or not, is being sent or not.
Importing Multi Valued Properties #
Allows to add or edit multi valued properties for any item, and change its property values using spreadsheets.
Action: Add the multi value property if it doesn’t already exist if it does then edit the already created one with the sheet information.
Item Code: The item code of the item containing the multi valued property.
Multi Value Property Name: The name of the property being used.
Property Value: The value of the property in the item.
Importing Item Multi Valued Properties #
Missing
Importing Item Sku #
The Item Sku spreadsheet defines parent- child item relationships on the store. The spreadsheet will look similar to the figure below.
The first column should have the parent item code and the second column, the child item code. Column C defines what attribute connects parent and child and column D should have the attribute value for the child item. Set Available to Y to activate the Parent- child relationship.
Note: Separate entries for each child item and corresponding attribute. On the store, once at the parent item page, a drop down menus or radio buttons with values will appear as configured in Column D of this spreadsheet. Selecting the right values will then redirect to the corresponding child item.
Once the spreadsheet is complete, use the Item SKU File Name section on Import/Export Store Data From Spreadsheet page to import to the store.
The Publish Process #
Its important to note that spreadsheets that hold catalog information such as item prices, categories, item properties, attributes, payment gateway information, shipping and so on, are to be imported directly to preview and never to the shop. This information already gets copied to shop when published.
On the other hand, customer related/dependent tables such as customer.xls, item Inventory, orders.xls , Item reviews etc are to be imported directly to the shop. These spreadsheets don’t publish to shop even if they are on preview. Below is a table listing various spreadsheets and whether or not they are to be imported to Shop or preview.
| Spreadsheet | Preview | Shop |
| Categories | Yes | Never |
| Items | Yes | Never |
| Item Options | Yes | Never |
| Item Properties | Yes | Never |
| Shipping | Yes | Never |
| Item Sku | Yes | Never |
| Payment Gateways | Yes | Never |
| Customers | No* | Always |
| Inventory | Optional | Yes |
| Reviews | No** | Always |
*Its possible to import a customers spreadsheet with test customers only to Preview. It doesnt have to be a real customers on the preview application as preview is used for testing.
** Reviews can be imported to preview, however, since the customers that exist in preview are test customers, a different review spreadsheet is needed which will import to the shop.
NOTE: After all importing of spreadsheets is completed on the preview application, go to Advanced | Solr Items and click the Full Rebuild button this is a must option to do. Also go to Advanced | Solr Categories and click the Rebuild button. Then the next time its published check the “Force Rebuild of Solr Items and Cats” checkbox on the Catalog | Publish to Shop – Publish/Publish Status page when a publish is launched to the shop.
Once all spreadsheets are imported to preview and the Full Rebuild is completed, then Publish to store. The publish process then copies this information to the shop.
Note: When published after importing the spreadsheets, always check the “Force Rebuild of Solr Items and Cats” check box.
Importing Customer information #
All the data regarding customers such as customer login name, customer name, password, addresses, associated properties and properties value can be uploaded through the ‘exportcustomer.xls’ spreadsheet. To download the spreadsheet to work with, go to People | Customers and use the Export button at the bottom.
The first tab (customers) would look as in figure below. Column A stores Login Name and column B stores password. Note that if the password value is less than 10 characters will update the password to that value. If the data is more than 10 characters it is assumed this is an encrypted hash and will be used directly.
Column C is for customer code. Leave this blank as the system generates this automatically. Set Active (column D) to True to activate this account. Column E is to set the customer to a particular Shopper group. Columns F to I are for customer names. J and K are for Hint Question/Answer. Leave column L blank as values will be generated automatically when the file is imported.Column M stores e-mail address of the customer.
The second tab is where the customer addresses provider. Each customer needs 3 addresses (Shipping, Billing and Customer) configured. Column A is used to specify the Login name. Column B specifies what address is being set up. Columns D to H can be used to store customer phone number/ Extension/ Area code. Column I is for email address for this address. J stores the fax line. We have 2 columns L and M to store company name and type. Columns N to P corresponds to the 3 lines we have for address. Columns Q and R are for city and province. If a province is needed to be specified that’s not listed in the store, use column S. Columns T and U are for postal code and country. Leave column V (Ship zone) empty. Column W is for specifying customer salutation. Columns X to Z is used to store the customer names. Columns AA and AB have been deprecated. AC stores Address Nickname and AD needs to be set to True. In cases where multiple billing/shipping/customer addresses for a customer, set one entry to True and the rest to False.
The properties tab is used to map customer property/value pair to a customer. Column A specifies customer login name , column B is for Property and column C corresponds to the property value.
The final Tab( Property Definitions) will define all the existing customer properties on the store and what values they can assume. Column A has the property name and column B is used for a short description. Column C can be used to specify the default value this property can have.
Use columns D and E to specify if the property is editable(YES/NO) or viewable( YES/NO). Column F is used to determine if this property takes text values (set ‘Text Value’) or values from a predefined list( set ‘Preset List of Text Values’). Columns G and forward are for specifying the preset values for Preset List type properties.