FAQ Q240: How to use an external CSV file for pricing?

Pricing Manager is very flexible but sometimes (especially if you have hundreds of resources) you may prefer to work with separate CSV files, created in Excel or Google Drive/Docs. Planyo offers you the same flexibility as the Pricing Manager but with the convenience of using external pricing file(s). In such case you don't import the prices once but you always upload the latest pricing files anywhere on your server and you only specify the http location of the pricing CSV file in planyo. If you want to test your file's format after doing changes, you can do so by clicking on the Test CSV file button at the bottom of Pricing Manager. This way you can test your CSV before actually using it to avoid errors. The test page will give inform you of any errors or warnings and you'll be able to preview the prices, as you can in Pricing Manager.

Defining pricing in CSV files


You can define all your pricing in an external CSV file (exported from Excel or Google Docs), or you can have some rules defined in the Pricing Manager and others in the CSV. Of course the order of the rules is still important (please see how rules are executed in Q181) -- the imported rules are internally all inserted in place of the CSV rule. This way you can add your other rules before or after the CSV rule.

There are two ways of specifying the CSV file to be used. You can either enter its location (including http://...) in the column corresponding to specific resource, or you can enter the location in the rule itself, this mean that the CSV file will be used for all resources. In case the CSV location is specified in both these places, the resource-specific CSV will be the only one used.

Format of the CSV file


The format of the CSV file is very flexible. The first row in the CSV file is the header which defines the meaning of the columns. Only the first two columns are fixed: the first one includes the rule (e.g. number of persons) and the second one the rule conditions (e.g. 2-4). All the other columns can represent one of the following:
  • resource name or ID -- You can specify either the exact resource name (case is ignored) or the resource ID
  • group of resources -- If many of your resources have the same pricing rules, you can use the text all to include all resources from the planyo site in given column, or you can include a group of resources filtered by a custom resource property by using the filter property name = value.
  • additional condition rules -- You can add one or more additional conditions. The syntax here is the same as for the rule cells (see below).
  • comments -- Any column header which is not understood as resource name/ID or additional rule will be treated as a comments column and will be ignored

All the other rows in the CSV file specify resource pricing. The first two columns define the rule and the condition for the rule (if any). The exact rule names used in pricing manager will always work (you can also use short versions, see the table below), plus you can also directly specify a custom reservation form item and the value for which the rule should be applied (e.g. col1:Adults col2:4 would be the equivalent of: col1:Reservation form item col2:Adults=4). The format of the condition column depends on the rule. Some rules have no condition, e.g. Always true (leave it empty)., some have one, e.g. Additional persons present (specify a single value here) and others have two values, e.g. Date including year. In this case you can use one of the following formats to define the range:
  • A - B, A to B -- to specify the range, e.g. 2013-01-01 - 2013-06-01 or 3-4
  • >A, <A -- this will only work with numbers, e.g. >5 or < 3
  • >=A, <=A -- this will work with dates or numbers, e.g. >=2013-01-01
  • name=value -- this syntax works with the Custom property (user) and Reservation form item rules

In case of Duration and Time remaining until rental rules, which are by default specified in hours, you can also add days or nights after the number to specify a value in full days (you could otherwise multiple the number of days by 24).

Below are some example rules which require a small comment. Other rules should be straightforward, please refer to Q181 for the explanation or see the values which can be chosen on the Pricing Manager page.

Rule columnOther possible namesCondition column
Additional persons presentExtra persons, additional persons>2 or simply 2 in the condition column both mean more than 2 persons
Always trueAll names not understood by the import will be treated as this rule. This means you can apply your own texts for easier reference, e.g. you can type tourist office tax which is always applied as +X% of the pricenot used
Custom property (User)User propertyIn case you defined a custom user property which is always associated with given customer (e.g. membership ID), you can specify it by entering in the second column: property-name=value
Date including yearDate with year, PeriodThe shortcut here is simply period, and the second column can include both dates (e.g. 01.01.2013 - 15.03.2013), period starting with given date (e.g. >=June 1, 2013) or ending with given date (e.g. <December 1, 2013). The date formats are flexible, as elsewhere in planyo most commonly used formats will work. In case you use dashes to separate date components (e.g. 2013-02-03) you can use the word to to specify the range, e.g.: 2013-02-03 to 2012-02-13.
Date, arrival date, departure dateThese rules are applied to all years, please use Period (or the versions including with year) explained above if you want to limit the period to given year. The format here is: day-month to day-month, e.g. 20-01 to 15-02 means (20th of January until 15th of February)
Weekday, start weekday, end weekdayThe weekdays are numbers, where 0 is Sunday, 1 is Monday and so on until 6 which is Saturday. E.g. 2-4 means Tuesday until Thursday, and a single number like 0 means Sunday only


The remaining columns specify prices. Here you should refer to Q181 for the explanations on how to enter prices. The CSV uses the exact same syntax as what you see when looking at the Pricing Manager prices. E.g. all of the following will work: +10%, -5 EUR per person per day, 10.50 USD per day&person, +5x, +10 CHF per hour, 100 CHF per week. The format here is quite flexible, e.g. to specify a price per person and per day you could use any of the following: 10 USD per day&person, 10 USD per person&day, 10 USD per person per day, 10 USD per day and person, 10 USD per night/person, 10 USD/day/person. You can also use the special items: error: msg, text: msg, deposit: xxx. These correspond to the special items in Pricing Manager and let you output an error message, additional info text or set the pre-payment (deposit) amount.

Sample CSV file


Here's a sample CSV file which could be used to define pricing:

ABCDE
1Sample pricing CSV fileApartment BlueApartment RedPeriod
2100 CHF per night120 CHF per night
3120 CHF per night130 CHF per night01.06.2013-01.09.2013
4Low season discount80 CHF per night90 CHF per night01.01.2013-01.04.2013
5Duration>7 days-10%-10%
6Duration1-5 days+5%+5%
7Additional persons>2+20 CHF per person&night+20 per person&night
8Supplement new years eve+50 CHF+50 CHF31.12.2013-31.12.2013
9Airport transferYes+40 CHF/person+40 CHF/person
10Tourism office tax+8%+8%

Notes:
  • Cells A1 and B1 can be used for comments, they are not parsed by the import.
  • There's an additional column (E) called period which adds a second condition to all prices. When empty only the condition from column A is used.
  • Some of the rules in column A are empty or have invalid naming which will show you warnings in the test page (e.g. Tax, Low season discount, Supplement new years eve). They have no meaning in planyo and are interpreted as the Always true rule, meaning the rule is always applied (unless the period rule in column E is entered).
  • Since most of the prices use the per person and night unit, we could make this the default unit (it would then suffice to type 100 instead of 100 CHF per person and night)
  • This pricing CSV assumes there are already two resources called Apartment Blue and Apartment Red as well as reservation form items: at least one field of the type Number of persons or Number of adults, and a checkbox item called Airport transfer.
  • You can download the sample CSV here


Other formats


If this standard CSV format will not work for you, you can always create a custom pricing script which reads your own CSV files. Please see Q137 for more information, also in case you don't have developers on your team.
Back to Frequently Asked Questions