Configuration‎ > ‎

CSV Configuration

 

 

Overview


 
The CSV file can seem confusing at first, but spend 5 minutes with it and you will understand how it works, and see the flexibility it offers. A CSV file is best opened with a spreadsheet application – I prefer OpenOffice as it will save in correct format.
 
Some facts about CSV configuration:
  • CSV tables are extensively used in our shipping extensions for creating rules
     
  • CSV files basically define the rules under which a shipping rate will show. Each row is a rule
     
  • If you don't understand your shipping requirements you have little chance to make a CSV file that works. Get the requirements understood first
     
  • A CSV configuration is only as good as the person that writes it. If you write a bad CSV file then expect bad results
     
  • In order to set up the If you have no experience of spreadsheets/csv files then go look this up first, it will help
     
  • If you are working on a Mac please ensure you save as Windows file type, else the csv will not upload
     
  • The shipping extension works on a "best match" basis. This means it will look for the tightest match first, and then work backwards if none can be found. If you have two rows in the table, one with country code "US" and one with "*", then a customer from the US will match on the US row, and a customer from UK will match on the "*" row.
     

With Shipping Extensions there is a need for our customers to translate business requirements into a configuration file and settings. This isnt like a standard plug and play extension, you are going to have to do some work to configure as you need.

WebShopApps can assist with configuration in many ways. For those of you looking at a cheaper solution checkout the examples, screencasts and documentation. For those in a hurry then take a look at our support packages, in terms of time saved they are definitely worth their money!

 

How do I start setting up a CSV file?


Before you setup a CSV file you need to understand the requirements. Some of the questions you should be asking yourself/your client are:
  1. What destination zones do I have (e.g. US 48, Europe, UK mainland)?
     
  2. Do I need different shipping based on weight?
     
  3. Do I need different shipping based on price?
     
  4. Do I need different shipping based on qty?
     
  5. How many different shipping options will I display?
     
  6. Do I want to restrict what options are shown under certain circumstances?
     
  7. If using product based extensions such as ProductMatrix or Shipping Override how am I going to segment my products into shipping groups?

Once you have all this information you can then start building the csv file. We recommend you firstly tackle a simple scenario, and then expand the csv file to meet the more complex needs. Test as you go along, there is no point constructing a large CSV file without testing progressively, I can almost guarantee it just wont work.

All paid extensions support the following filtering:

  1. By Destination
  2. By Weight
  3. By Price
  4. By Qty

Additionally many of the extensions support the additional filters:

  1. Shipping Group
  2. Customer Group

All CSV files have a delivery type column, where you specify either the delivery type to show or the delivery type to manipulate (e.g. UPS Ground).

Some extensions may also give extra columns to handle special scenarios.  This is done via the Algorithm and Rules columns.

 

How to Upload a CSV File


 
  1. Navigate to your Extension Configuration panel under System->Configuration, it will usually either be in Shipping Methods or Shipping settings
     
  2. Change 'Current Configuration Scope' to be the Main Website
     
     
     
     
  3. You should now see an import button box appear with a browse box
     
  4. Browse for your CSV file you have setup (or use an example)
     
  5. Save Config to import the table. Any errors in the CSV will be reported here
     
  6. You should then see the appropriate shipping rates appear on the front end upon checkout
 

How to Export a CSV File


For a full guide please see here:
 
       

Destination Filtering


 
The first 5 columns in the CSV file are used to define the destination filtering. Using this it's possible to set different rates for Hawaii and Utah, London and the Shetlands, Perth or Sydney, to name just a few.
 
Destination filtering can include a combination of one or more of country, state, city, or zip code.

Please note: Matrix Rate, our free table rates extension, does not support UK postcodes and only supports range-based filtering in the 'from' and 'to' fields – there is no support for ',' separations in between different ranges.
 

 Field Name

 Description

 Default (to match all rows)

 When to use

 Country

 ISO3 Country code this rate applies to.

 *

  If you need to change shipping prices based on country. For example you can enter USA on one row and define prices, then use * on next row to indicate prices for Rest of World

 Region/State

 Destination State

 *

  If you need to change/prevent shipping prices based on state. For example you may wish to exclude shipping to Alaska, Hawaii

 City

 Destination City (not used in most countries)

 *

  When you need to change shipping based on city, e.g. Sydney. This filter is rarely used.

 Zip/Postal code from

 Destination zip code. If using numeric ranges then set this to the number you wish the search to start from. If using pattern matching then set this to the pattern you require. See pattern matching for details

ShippingOverride, ProductMatrix and Premium MatrixRate additionally allow you to enter multiple ranges on one line. So you could enter something like: 10000-20000,58478,78548,35000-40000. You would leave zip to blank in this scenario and enable "Use numerical zip code ranges" in the admin panel.

 *

  When you need to change shipping prices based on zip/postcode. Commonly used in the UK to give different rates to the Highlands.

 Zip/Postal code to

 Only used with purely numeric zips.

 *

  When you need to enter a zipcode range,

 

Multiple Countries, States, Post Codes on one line


 

With all the paid WebShopApps exensions you can put any combination of multiple countries, states, or postcodes on one line. This feature is not supported in the free Matrixrates extension.

Examples where this is useful:

  • Specify rates for all of Europe in one set e.g. ITA, FRA, DEU ...
  • Specify exemption rates for Alaska, Hawaii e.g. AK, HI
  • Specify UK postcodes, e.g. BT%, IM%, GE%

An example of multiple countries on one line is shown in the example here

 

Country Codes 


The country codes in Magento should conform to ISO3166 (aka ISO3) standards  - see the list here
 
There are some anomolies, in particular Romania is ROU according to ISO3 but ROM in Magento pre-1.4. Magento have fixed this for 1.4 onwards (and Enterprise), so be careful if upgrading.  There are a handful of codes like ROU
 
If you look in phpmyadmin at the table directory_country you will get the full list of ISO3 codes used.
 
Please see here for a list of google country codes
 
 

Region Codes 


If you look in directory_country_region you will get the full list of region/state codes used.  If you want to say remove some of the military states then you can do by modifying this table.

This field can be left with a * which means include all.

Firstly check you have regions - these can be seen in the drop down on the cart shipping estimator.  If you don't then try downloading your countries locale extension. Otherwise you may need to define yourself (if you wish to use).

If you have region codes you need to use the short code in the CSV. This correlates to the 'code' field in the database table directory_country_region.

For example in the USA valid codes are AK, CA, NY, etc.
The values in the region/ state field (from csv) are converted into numeric values before they get save into the database by directory_country_region table. For example: 
  • If there is no value in region filed, it gets save as zero
  • If there is value say NSW or any region in that filed, then it will convert the value into its numeric value and saves into the database and when you try to retrieve it, then it gets convert back to the actual value
 

Post/Zip Code Filtering


 
In some circumstances it may be necessary to filter rates based on the postcode or zipcode a customer is in. Our extensions support 3 different methods for filtering based on zipcode. The method you choose depends on your country location and postcode format in place:
  1. Range based filtering - with this you specify zipcode ranges e.g. 6000-8000,9000-1000 to match on
  2. UK based filtering - this was specifically developed for the UK market and allows you to specify the start of the postcode e.g. PA25, E1, etc
  3. Pattern based filtering - Where the above 2 methods are not appropriate you can use pattern matched filtering. We havent found a country that doesnt support this!
Note: UK based filtering is not supported in the free Matrix Rates extension. It is supported in all paid extensions, and we strongly recommend customers upgrade to the Premium Matrix Rate extension if they need to filter based on postcodes; otherwise, their CSV file will be large and difficult to maintain.
 
 
 

Weight Based Filtering


 
The shipping extension looks for possible matches in the table in order to display the applicable shipping rates. You can set weight ranges on which to match.
 
Let's take an example:
 
 Weight From  Weight <=  Price
 0  30  10
 30  1000  25
 
Let's assume the unit of measurement is lbs.
 
Here if the cart is between 0 and 30lbs then the shipping rate is $10. If the cart is higher than 30lb then the rate is $25.
 
With Matrix Rate and Premium Matrix Rate the weight from is weight >=, all other extensions it is weight >. So in the above example on Matrix Rate and Premium Matrix Rate the weight to should be 29.99, otherwise a weight of 30 will match on both rows.
 
In Matrix Rate the weight to/from must always have a value - '*' cannot be used for the default.
 

Price Based Filtering


 
This is very similar to weight based filtering, but based on price. You can obviously combine, so for example you can say a cart <30lbs and less $500 cost x, over $500 costs y.
 
In Matrix Rate the price to/from must always have a value - '*' cannot be used for the default.
 

Qty Based Filtering


 
Also known as item based filtering. Here you are filtering on the number of items in the cart. So, for example. you can say upto 10 items in the cart charge x, over charge y.
In Matrixrate the qty/item to/from must always have a value - '*' cannot be used for the default.
 

Customer Group Filtering


Unless you have a requirement to control rates for different customer groups (e.g. retail, general), then you will not need this feature. Set to the default “*” in csv file.

Shipping rates can be specified based on the customer group they apply to. For example:

  • Charge $10 to Wholesale Customers for shipping
  • Charge $20 to Retail Customers for shipping

 This can be done by having 2 lines in the csv with the customer group set to Wholesale, Retail respectively.

The customer group names are taken directly from the Customer Groups defined in Magento, and are case sensitive.

Customer group * means all customer groups.

Multiple customer groups can be specified on one csv row, e.g. "Retailer,NOT LOGGED IN,General"

 

Shipping Group or Handling Group Filtering

Product Matrix, Shipping Override and Handling Fees Matrix create a magento attribute called Shipping Group (attribute id = package_id, special_shipping_group and handling_id respectively).
 
Shipping groups are used to group together products that have the same shipping price rules. 
 
When the extension is installed there should be a Shipping tab in your product listing, and the Shipping Group should be available. If it is not then please see the Attribute configuration section to resolve.
 
 
 
For example, if you have mainly standard items and a few bulky items you would create a shipping group called BULKY, and then assign these to all your bulky products. With the standard shipping group you have the option to either create a shipping group called standard, or because this is used for most of your products you can choose to not assign items to the standard group, but use the * value in the shipping group column in the csv configuration to say '*' = all items that are not in the bulky group.
 
Shipping Groups need to be added to the drop down values for the group attribute, as you do for other drop-down attributes in Magento. The Attribute configuration section contains further info on how to assign these values.
 
Shipping Groups are case sensitive, and must match exactly with the vales in the shipping group column in the csv file.
 

What is the '*' Shipping Group?


The use of a * in shipping group within the table can provide different results, depending on how you want to use it.

 The two options for the meaning of "*" are:

  • Include all items that do not have a shipping group assigned

  • Include all items in the cart

You can change how shipping group '*' is handled in the extension configuration in admin. There is a switch '* includes all items in the cart', which can be set to yes or no.

As a general rule, if most of your products use the same shipping rules, with just a few that are using special pricing then the advice is to say that the * shipping group applies to all items that are not defined by a shipping group. This means you don't need to worry about assigned most of your products to a shipping group.

If on the other hand you have a shipping rate such as pickup only which applies to all products regardless of shipping group then it's advisable to say the * shipping group applies to all products in the cart.