About CSV files and how they deliver product data to your shop

CSV stands for Comma Separated Values. It refers to the file that is used to store the data of your shop’s database. The easiest way to think of a My Shop CSV is that it represents all of the information entered for a product (description, pricing, etc.), written on a single line within a spreadsheet. CSV documents are typically edited using software such as Microsoft Excel® or suitable spreadsheet software.

If you’re adding or editing more than, say, 5-10 products at a time, then using your CSV file will save you time over the alternative of editing/adding via your website CMS Dashboard.

Save time by selecting only the data you need to update

Working with a CSV file can be challenging due to the large volume of data presented to you on your spreadsheet's screen. This is especially true when presented with an entire shop database worth of information, and the necessity to scroll horizontally across the screen to locate data buried deep inside!

But let's say that you only want to edit products within a specific product category rather than every single product in your shop. You have the choice to export specific columns of data across specific categories to save considerable time trying to otherwise navigate a fully-populated CSV file. Depending on how many columns of product data you need to edit at any one time, being able to choose specific data for export will reduce or eliminate the need to scroll horizontally across your spreadsheet to access data. Time saved can be considerable.

The CSV file can be as simple or as complex as you need . This section details its use, its data, and guides to follow, in order to maintain your shop's CSV file with minimal fuss.

Overview: Your Shop CSV File

To add details for more than 5-10 products at a time it is efficient to upload a CSV file (via a spreadsheet) containing all of the required information directly into My Shop. The advantage of this process is that it allows you to bulk-add or edit products in one step.

Each product field that you see within My Shop (i.e. price, product title, description, image references, etc.) gathers its data from information within your My Shop CSV file. To view your CSV file “in the raw”, you must first export the CSV file from My Shop before opening it on your computer.

Consideration: How much data do you need to export?

Your My Shop 's product data is compiled into rows and columns once it's exported from your shop and then opened inside your spreadsheet software for viewing. The shop's data fields such as 'price1', 'category_name' and so on, make up the CSV file's columns, with each product populating one row within the spreadsheet.

A typical CSV file, once exported from your shop in full, contains dozens of columns (see the full explanation of each column below). But what if you only want to make price changes to your shop's database, or price changes within a specific product category only? Instead of exporting your shop's entire database you have the option of selecting specific categories or sub-categories , together with specific columns such as 'price1' or 'weight', so that your resulting spreadsheet CSV file is simpler, and therefore easier, to work with. Because you're not having to scroll your spreadsheet horizontally across the screen to access columns buried deep withn the CSV file, you can work much more effectively.

You have two choices when exporting your My Shop CSV file: Full Store Export or Partial Store Export.

Full Store Export of CSV file from My Shop

Use this option when adding new products to your shop database or when you need to make wholesale changes to a volume of products and their various attributes at the same time.

1. Go to My Shop
2. Click the  Import/Export navigation tab
3. Click the Export navigation tab
4. Select the  Full Store Export radio button 
5. Click the  Export Products button. Your CSV file will now be compiled and automatically downloaded to your computer; depending on the size of your shop's database this could take a few minutes.
6. Save the CSV file to your computer
7. Open the file using any spreadsheet software such as Microsoft Excel®
8. Observe the column titles in the spreadsheet’s first row, then refer to the next section:

Top of page

Partial Store Export of CSV file from My Shop

1. Go to  My Shop
2. Click the  Import/Export  navigation tab
3. Click the  Export  navigation tab
4. Select the  Partial Store Export  radio button
5. Your My Shop product categories will now appear in a left-hand window pane on your screen, together with your database's Product Fields within a right-hand window pane 
6. Determine which product categories you wish to edit by selecting product categories and/or sub-categories from the  Select Categories list.
7. Now choose the Product Fields you need to edit by selecting the appropriate fields from the  Select Product Fields  list.
8. Click the  Export Products button. Your customised CSV file will now be compiled and automatically downloaded to your computer; depending on the volume of data you chose to export, this could take a few minutes.
9. Save the CSV file to your computer
10. Open the file using any spreadsheet software such as Microsoft Excel®

Top of page

Partial Store Import of an edited CSV file into My Shop

Having made the necessary edits to your partial CSV file, you now upload the amended file back into your My Shop database. The amended data within columns that you chose to export will now override the data within the same columns sitting back in the My Shop database.

1. After you complete necessary edits within your CSV file, save the file from within your spreadsheet and, when prompted, be sure to save the file back into its original CSV format (do not save the CSV file as a  Microsoft Excel® file type e.g. .xls if using Excel; similarly, the same rule applies to whatever spreadsheet software you use - you MUST save/export the spreadsheet as a CSV file).
   
2. Go to  My Shop
3. Click the Import/Export navigation tab; the Import screen will open as the default screen 
4. Select the Modify Existing Products radio button
5. To automatically backup your shop database, select the Perform a full catalogue backup first (recommended)
6. Click the Choose File button and select the saved CSV file from your computer
7. Once selected, click Upload Products

Top of page

Use the CSV file to edit existing products within your shop inventory

As your business evolves it's likely that you will need to edit existing products by updating prices, product titles, descriptions, weights or dimensions, etc. This option will upload your CSV file and replace existing products already in the database that have identical product codes.  Once you have edited the CSV file within your spreadsheet software, you then upload it to My Shop .

1. After you complete necessary edits within your CSV file, save the file from within your spreadsheet and, when prompted, be sure to save the file back into its original CSV format (do not save the CSV file as a  Microsoft Excel® file type e.g. .xls if using Excel; similarly, the same rule applies to whatever spreadsheet software you use - you MUST save/export the spreadsheet as a CSV file).
2. Go to My Shop > Import/Export 
3. Select the option Add products to your existing inventory, replacing products with the same product code
4. To automatically backup your shop database, select the Perform a full catalogue backup first (recommended)
5. Click the Choose File button
6. Locate your edited and saved CSV file
7. Click on the Upload Products button

• Replace entire inventory: This option will replace your entire current shop database with the products in the CSV. This option is best used when you have downloaded the current database and made extensive alterations/additions, or when you want to change your entire inventory.

Top of page

Use a CSV file to add new products to your shop inventory

If you need to add a volume of new products to your inventory, adding these new products via your CSV file will save considerable time over adding each new product, one by one, via your My Shop interface (if, however, you're adding a small number of similar new products to products that already exist, consider using the Clone Product feature).

To add new products you must first export your shop inventory then open the exported CSV file in your spreadsheet software. To add new products all you require is the first row of header/column information (i.e. 'category_name', 'price', code', etc.) making sure that you NEVER delete or modify the header row data for each column.

When editing the CSV file you add the new products into their respective rows within the spreadsheet, copying and pasting any existing common data from the CSV export (such as product category or sub-category information) needed by the new products (or create new categories/sub-categories using the code guides below). When you're done adding new products and completed each required cell of information, you can choose to remove the remainder of existing products so that you're only uploading new products to your shop inventory.

1. After you complete necessary edits within your CSV file, save the file from within your spreadsheet and, when prompted, be sure to save the file back into its original CSV format (do not save the CSV file as a Microsoft Excel® file type e.g. .xls if using Excel; similarly, the same rule applies to whatever spreadsheet software you use - you MUST save/export the spreadsheet as a CSV file).
2. Go to  My Shop  >  Import/Export
3. Select the option  Add products to your existing inventory
4. To automatically backup your shop database, select the  Perform a full catalogue backup first (recommended)
5. Click the   Choose File  button
6. Locate your edited and saved CSV file
7. Click on the  Upload Products  button

Top of page

Learning to maintain your CSV File

Your shop’s CSV data file is a complex spreadsheet that requires mastering before it can be edited and re-uploaded to My Shop for your edits to take effect. Please observe the following precautions when working with your CSV file:

• Never add, edit or delete column titles at any time. Doing so will corrupt your CSV file.
• If for any reason there are columns unused by your shop, do  not delete them.
• Observe all protocols within this guide ( highlighted in orange ).
• Each spreadsheet row carries information for a single product.

Top of page

Getting to know Your CSV file’s columns

An Asterix denotes a required field.

• category_name * : The name of your product category. If you want to assign a product to a sub-category you must separate each category, without spaces using the pipe symbol: |
  e.g. categoryname | sub-categoryname
• category_name2 & 3: Use these columns if you want a product to appear across more than one category, e.g. in the “Books” category as well as the “On Sale” category.
• name: The descriptive name of the product.
• code*: Your unique identification code for this product.
• price1*: A price for the product in dollars (but without the currency symbol). 
• price2, 3, 4..: Unless you have multiple price breaks (e.g. wholesale, reseller, VIP club, etc.) these fields can remain blank.
• short_description: A single line description that appears under the product title on your product category pages.
• long_description: The main product description. Don't worry if it appears to be too long for the cell within your spreadsheet.
• status: This is the inventory-status of your product such as “Out of Stock”, “Inactive”, etc. The correct syntax for these cells is: out_of_stock , back_order , pre-order , inactive
• weight: Enter a numerical-value product-weight if you are using “shipping by weight” or by “Auspost” (Australia Post). Otherwise enter zero (0). Measured in kg amounts.
• height / width / depth: Only enter dimensions if you are using shipping-by-volume or by Auspost. Otherwise enter a zero (0). Measured in cms.
• apply_tax: Enter the numerical value 1 to apply tax to the product. Otherwise leave blank.
• free_shipping: Change this to a value of 1 if you want to apply free shipping to this product. Otherwise leave blank.
• shipping: Enter a numerical value (in dollars, without currency symbol) if you wish to add additional shipping to this product.
• handling: Enter a numerical value (in dollars, without currency symbol) if you wish to add a handling charge to this product.
• meta_keywords: Used for search engine optimisation (SEO). You can add approximately 5 keywords, separated by a comma, related to your product.
• meta_description: Add a brief description of your product no greater than 160 characters in length.
• random_upsell: To activate cross-sell product links on this product’s page, enter a value of 1 here. To deactivate cross-sell links on this product’s page, place a zero ( 0 ) within this cell.
• sort_order: This designates the product’s display position within its primary category . Leaving this cell blank will sort products in alphabetical order.
• visible: Numerical value of 1 makes the product visible in your store, 0 hides the product from all customer views but it will still be available for you to edit in My Shop.
• web_name: Leave this cell blank and the CMS will auto-generate this cell from the product’s name.
• stock_level: Available only if the optional Inventory Control function has been activated on your shop. Correct protocol is numerical values only.
• total_sold: This numerical value will indicate the quantity sold, and is auto-generated once this product has been sold. This value is used to populate a “most popular” list in your shop, so you can choose to add a numerical value to this cell in order to put the product on the “most popular” list, or if you would like a starting value from an old website. In most cases this cell is left blank.
• total_orders: This numerical value is similar to Total Sold , but it is the total number of times the product has appeared within an order (e.g. if a customer buys 10 items of the same product in one order it will show up as 10 in Total Sold and 1 in Total Orders ). In most cases this cell is left blank.
• rrp (recommended retail price) : If you have a product on sale, place the original price here and the discounted price in the Price1 field.
• new_until: Applies to the Product Flags feature (see Shop Products: Promotional Product Flags ). By adding a date in this field, a “New” promotional Product Flag will display superimposed over the product’s product category and Shopfront display thumbnail image.
• on_sale: Applies to the Product Flags feature (see Shop Products: Promotional Product Flags ). By adding a date in this field, an “On Sale” promotional Product Flag will display superimposed over the product’s product category and Shopfront display thumbnail image.
• featured: Applies to the Product Flags feature (see Shop Products: Promotional Product Flags ). By adding a date in this field, a “Featured” promotional Product Flag will display superimposed over the product’s product category and Shopfront display thumbnail image.
• extra_field: Ignore this cell.
• option_name1, 2, 3..: (see Shop Products: Focus, Product Options ) If you want to set up options for products, e.g. colour/size/ inclusions, they are listed as Product Options . Options can also affect price, i.e. add or subtract a dollar or percentage value from the Price1 field. This cell is used as the Label for the options list, e.g. “Colour”, “Size”, etc.
• option_description1, 2, 3..: Used to explain the difference between the options or why there are options to choose from, e.g. “Choose one of the following colours”.
• option_value1, 2, 3..: Used to list the options and price variances. This is where the product’s different options are entered; they will appear in a drop-down menu list on the product’s page. If the option involves adding or subtracting a dollar value from the price of the product, you can also do that here.

Product Options Example:
Entering data into the option_value cell can be tricky if you don’t closely observe the correct syntax. To better explain how data is entered into this field, we have provided the following detailed example:

• In this example we will illustrate a product option where customers can choose from one of 3 different garment sizes. A drop-down menu list will be placed on the product page for customers to select their “option” preference before adding the product to their shopping cart.
• The three options are small , medium and large . 
• A value of $5 has been added to the medium option, and $10 to the large (the small option is priced as the “base” or retail price of the product, so no variance is needed).
• Each option is given an identifying option code (e.g. “sm”, “med” and “lge”) so that your staff pick the correct product variation in your warehouse.
• One option string is shown here:  option-name | add-or-subtract? | numerical-value | dollar-or-percentage-variance? | option-id-code ;
• Now, in practice to illustrate our example:  small | + || $ | sm| | ;medium | + | 5.00 | $ | med || ;large | + | 10.00 | $ | lge |;
• Note the double use of "pipe" symbols. This is due to no value being placed within those pipes, i.e. no sort order.

• option_sort_order1, 2, 3..: This provides the order in which different product option-groups display on a product page.
• option_type1, 2, 3..: This cell instructs the option group to display as a selectable drop-down menu list or text-input field. Leave this field blank and allow the CMS to generate this information for you after creating product options.
• option_type_configure1, 2, 3..: Leave this cell blank unless you have been instructed otherwise by your account manager.
• option_required1, 2, 3..: Enter a numerical value of 0 for “no” and 1 for “yes”. Generally you will mark this with a value of 1 so that customers must choose an option before they attempt to add the product to their shopping cart.

• image_filename1, 2, 3..: The file name of the product’s primary image, e.g. bookimage01.jpg. Ensure that the full image name, including file extension, is used.
• image_title1, 2, 3..: Enter a short title for your image. Spaces are okay to use.
• image_description1, 2, 3..: Search engines index images so it’s good practice to enter a short description of your image. Spaces are okay to use. This description may be used on search engine results pages (SERPs).
• image_sort_order1, 2, 3..: Indicates the sort order of your images. A value of 0 will instruct your product page gallery to display this image as the primary image.
• description_label: This description will be located under your product description in its own text box. It is often for shipping details or specific instructions to the customer.
• description_content: The full description for the description_label .
• file_filename1, 2, 3..: If you provide a file for customers to download (such as product technical manuals, etc.), place the file name within this cell. The same protocols as used with images also apply here.  
• file_title, 2, 3..: Enter a short title for your file. Spaces are okay to use.
• file_description1, 2, 3..: Search engines also index files so it’s good practice to enter a short description of your file. Spaces are okay to use. This description may be used on search engine results pages (SERPs).
• file_sort_order1, 2, 3..: Indicates the sort order of your files if placing multiple files on the product page. A value of 0 will instruct your product page to display this file towards the top of the page.

Top of page

Create a Site-Wide Sale

If your shop is having a sale with, say, a 20% off all products, you can create a global (i.e. site-wide) discount that applies a set value percentage to all products within your shop.

To do this, go to  Preferences >  Product inventory.  Just look for the  apply global discount text input field and enter an appropriate value for your sale.

But what if your shop offers retail and wholesale prices (where wholesale customers log in to your shop to view wholesale discounts) and you only want to apply a set percentage discount to retail prices?

Top of page

Create a Retail Price Site-Wide Sale

To apply a site-wide percentage discount sale to your retail prices only you must first download and open your CSV file. See instructions for Opening your CSV file from My Shop located at the beginning of this chapter.

1. Before you do anything, first create a backup copy of your shop:
2. Go to  My Shop >  Upload Products and select the  Backup Catalogue button at the bottom of the screen. Give your backup a name such as the date and time. This backup is created in case you make an error when uploading your new CSV file containing the percentage discounts.
3. With the CSV file open in a spreadsheet, you should see a column marked  rrp (recommended retail price). Depending on the customisations present within your shop's CSV file, if any, you should see the  rrp column located somewhere near your spreadsheet's AE column.
4. Copy all cells within your CSV file's price1 column and place the copied cells into the AE column (or whichever column your  rrp  data resides).
5. Now go back to your price1 column and select the very first cell containing a price (the cell that you highlight will be in row 2, located directly beneath the column's price1 header).
6. Create a spreadsheet formula that will automatically deduct your percentage discount value from the prices now in your  rrp column (i.e your original retail prices).  

For example: to create a 20% discount, type into your spreadheet's formula window, the following formula:

=(AE2*0.8)

In this example, you are instructing your highlighted spreadsheet cell to multiple your RRP prices (in column AE) by 0.8 (to reduce the retail prices by 20%). Of course, to reduce the prices by a lesser or greater value, simply multiply the cell by the required value.

1. To apply your discount formula to your entire shop inventory’s retail prices, simply select the cell already containing your new formula, and drag the cell down the column until you reach the final row in your spreadsheet. Each cell will automatically update with your set value discount.
2. Now save your spreadsheet. Select yes or continue when prompted to save the file in its original (CSV) format.
3. Now go back to your website Dashboard and upload your spreadsheet:

• My Shop > Upload Products and select the Replace entire inventory option. Choose file then click  Upload Products when you're done.

Top of page

Reversing a Global Sale

To reverse the previous steps, simply download your CSV file as described at the start of the chapter, and open the CSV in your spreadsheet software. Your original RRP prices are still located in your  rrp column, so just cut the cells under that column and paste them back to the  retail1 column. Save the file then upload to your shop. Simple!

Top of page