Export And Import Products And Prices From Excel
The export feature is available in the Prices list, where the system allows exporting the results of a search performed in the list.
The exported Excel file can be used for the Import feature as it has the same structure that the import requires.
The import feature is located in the main left menu, under Products and Prices list, accessible by clicking on the edit sign.
Excel File With Explanations For Fields
See file here
Excel File Details
For each Product detail and Price detail, a column is defined, and the file to be imported must maintain the same structure as in the attached sample.
You may import multiple products at once, each with multiple prices.
For importing a product with more than one price:
- On the 1st row (which is actually the 2nd row in the Excel file, because the 1st row is the header), you must fill in the product details and the details for the 1st price of the product.
- On the following rows, to enter 10 prices for the same product, you must copy and paste the same information for all 13 Product columns in 10 lines and then fill out the Price-related columns for each line separately.
When you have completed the details for the 1st product with all its prices, as explained above, you may add other products with related prices, in the same Excel file.
Important Mention!
For the columns of the Excel file that require completion with TRUE or FALSE values, the column must be formatted as Boolean Value.
First, apply the correct format to the column cells, and after that, type in TRUE or FALSE.
All columns requiring TRUE or FALSE must be completed with one of these two values, without exception.
Each product must have at least 1 price for which Season Begin/Season End, Book Begin/Book End dates must be filled in, otherwise, the import will result in an error.
Columns of the Excel File Explained
Product ID
For new products, leave this column empty or fill it with 0 (zero) as the value.
For updating an existing product, include the product’s ID from Victoury in this field.
If an ID is entered and not found in Victoury, this row will be ignored.
Product ProductCode
If the Product ID column is already filled in, then the product code of the entry found will not be updated; only the rest of the data will be.
If no ID is entered, a new product is created with this Product Code.
If no ID is entered, and the Product Code already exists in Victoury, an error will be returned.
Product-related data update does not occur based on product code identification.
NOTE: Victoury does not allow duplicate Product Codes, thus your codes must be unique.
Product ProfitCenter (Brand)
Enter the brand code as defined in the Brands Choice List.
Product VisitedAreas
Enter the Visited Areas codes as defined in the Visited_Areas Choice List.
Product Supplier Id
Enter the Supplier ID.
Product Supplier CompanyName1
If no Supplier ID is entered, but a name is provided in the Supplier Name column, a new supplier will be created, and the new supplier’s ID will be assigned to the product.
If both the ID and the Name of the supplier are entered – the Supplier ID will be assigned to the product, and the Name will be ignored.
If neither Supplier ID nor Name is entered – the supplier ID will be NULL for this product.
Product SupplierProductCode
Alphanumeric values and symbols
Product Name
Alphanumeric values and symbols
Product Category
Enter the Choice List code from the Product_Category Choice List.
Product Country Area
Enter the Choice List code from the Country_Area Choice List.
Product ChargeNights
The values for this column are true or false.
Product Rating
Enter the Choice List code from the Product_Rating Choice List.
Product Type
This can be one of the following values: ARRANGEMENT, ITINERARY, or BOTH.
Product DurationDays
Numeric values
Product DurationNights
Numeric values
Product BookDaily
The values for this column are true or false.
Product WeekDays
Before editing, change the cell format to TEXT instead of NUMBER. The value needs to be as 0123456, where 0 = Sunday, 1 = Monday, 2 = Tuesday, 3 = Wednesday, 4 = Thursday, 5 = Friday, 6 = Saturday.
Product HasOccupancy
The values for this column are true or false.
Product MaxChildAge
Numeric values representing the maximum age for children.
Product MaxInfantAge
Numeric values representing the maximum age for infants.
Product MaxBabyAge
Numeric values representing the maximum age for babies.
Product MinSeniorAge
Numeric values representing the minimum age for seniors.
Product EmbarkAddress Id and Product EmbarkLocation
When the Embark Address ID is provided, Victoury searches for the address:
- If the address is found, it is set for both Embark Address and Embark Location (product embark address name).
- If not found, then the product’s embark Address is not set, and the product’s embark Location will be the value from column X (Embark location).
When the Embark Address ID is not provided:
- The product’s embark Address is not set, and the product’s embark Location = column Y (Embark location).
Product DisembarkAddress Id and Product DisembarkLocation
When the Disembark Address ID is provided, Victoury searches for the address:
- If found, it sets both the product disembark Address and product disembark Location (product disembark location address name).
- If not found, then the product’s disembark Address will not be set, and the product’s disembark Location will be the value from column Z (Disembark location).
When the Disembark Address ID is not provided:
- The product’s disembark Address will not be set, and the product’s disembark Location = column AA (Disembark location).
Product CreateVoucher
The values for this column are true or false.
Product OrderText
Alphanumeric values and symbols (text)
Product OrderRemarks
Alphanumeric values and symbols (text)
Product NumberOfSeats
Numeric value
Product MaxPersons
Numeric value
Product GroupCode
Alphanumeric values and symbols
Product HasAllotments
The values for this column are true or false.
Product ShowAvailabilityWhenBooking
The values for this column are true or false.
Product ExcludeFromTotal
The values for this column are true or false.
Product Remarks
Alphanumeric values and symbols (text)
Product CatalogPage
Numeric value
Product Flag
Alphanumeric values and symbols (text)
Product Budget
Alphanumeric values and symbols
Product PublishOnWeb
The values for this column are true or false.
Product GpsLocation
Alphanumeric values and symbols
Product VoucherText
Alphanumeric values and symbols (text)
Product VoucherRemarks
Alphanumeric values and symbols (text)
Product TravelRemarks
Alphanumeric values and symbols (text)
Product Source
Alphanumeric values and symbols (text)
Product BackofficeReference
Alphanumeric values and symbols (text) (This is a unique value. Victoury does not allow duplicated Backoffice Reference).
Product Host2host
The values for this column are true or false.
Product Print
The values for this column are true or false.
Product SeoFriendlyName
Alphanumeric values and symbols (text) (This value is unique and will be generated automatically from the name of the product when the product is saved if the field’s value is not present).
Product Locked
The values for this column are true or false.
Price ID
The value to be filled in here is 0 (zero) or empty for a new price
- Price ID for existing price to be updated; if no price is found, ignore the row.
- The ID of the price must be related to the product (to not belong to another product other than the one set on the same row).
Otherwise, an exception is thrown by the system as the PriceID does not belong to the Product.
Price SeasonBegin
The correct format is YYYY-MM-DD (2021-01-31).
Price SeasonEnd
The correct format is YYYY-MM-DD (2021-01-31).
Price BookBegin
The correct format is YYYY-MM-DD (2021-01-31).
Price BookEnd
The correct format is YYYY-MM-DD (2021-01-31).
Price PriceType1
The values for these columns are the Choice List codes for the entries under Price_Type_1.
Price PriceType2
The values for these columns are the Choice List codes for the entries under Price_Type_2.
Price PriceType3
The values for these columns are the Choice List codes for the entries under Price_Type_3.
Price PriceType4
The values for these columns are the Choice List codes for the entries under Price_Type_4.
Price Description
Alphanumeric values and symbols (text)
Price TypeDescription
Alphanumeric values and symbols
Price IsPerPerson
The values for this column are true or false.
Price OneTimeCharge
The values for this column are true or false.
Price BuyUnitPrice
Numeric value – buy amount in native currecy
Price BuyTax Name and Price BuyTax Percentage
Search for Tax by name (code), percentage, and type contains ‘B’; if no Tax is found, a new tax is saved with type ‘B’.
Price BuyCommissionPercentage
Numeric value
Price BuyCurrencyCode
Important: add a valid currency code here as the system does not search in the Currency entity to find a match, it just saves the value added.
Price BuyExchangeRate
Numeric value – exchange rate between buy and main currency
Price BuyTotalPrice
Numeric value – this is the total sum of the Buy price including tax, with buy commission applied and Other cost including tax, with buy commission applied
To calculate the Buy Inclusive Tax use the below formula:
Price BuyUnitPrice * (1 + Price BuyTax Percentage / 100)
To calculate the Buy with commission applied use the below formula:
Price BuyUnitPrice * (1 – BuyCommissionPercentage / 100)
To calculate the Other Inclusive Tax use the below formula:
Price OtherCostUnitPrice * (1 + Price OtherCostTax Percentage / 100)
To calculate the Buy with commission applied use the below formula:
Price OtherCostUnitPrice * (1 – OtherCostCommissionPercentage / 100)
Price OtherCostUnitPrice
Numeric value
Price OtherCostTax Name and Price OtherCostTax Percentage
Search for Tax by name (code), percentage, and type contains ‘B’; if no Tax is found, a new tax is saved with type ‘B’.
Price OtherCostCommissionPercentage
Numeric value (represents percentage value)
Price CommissionPaidOnPriceExTax
The values for this column are true or false.
Price SellUnitPrice
Numeric value – this may be calculated from the Buy Total Price using the formula: 100 * Buy price with commission applied transformed in Sell currency / (100 – Price Profit)
Price SellTax Name and Price SellTax Percentage
Search for Tax by name (code), percentage, and type contains ‘S’; if no Tax is found, a new tax is saved with type ‘S’.
Price SellCommissionPercentage
Numeric value (represents percentage value)
Price SellCurrencyCode
Important: add a valid currency code here as the system does not search in the Currency entity to find a match, it just saves the value added.
Price SellExchangeRate
Numeric value – exchange rate between sell and main currency
Price SellTotalPrice
Numeric value – this may be calculated from Sell Unit Price using the formula: Sell Unit Price * (1 + Sell Tax Percentage / 100)
Price Profit
Numeric value (represents percentage value)
Price Profit = ((Sell Unit Price * Sell Exchange Rate) – Buy Total Price transformed in Sell Currency) / (Sell Unit Price * Sell Exchange Rate) * 100
Price Margin
Numeric value (represents percentage value)
Price Margin = (Price Profit / (100 – Price Profit )) * 100
The Price Profit and Price Margin calculation is dependent of the Price CalculateMarginWithBuyExTax field value, meaning:
- if Price CalculateMarginWithBuyExTax is true – the formula for Price Profit = ((Sell Unit Price * Sell Exchange Rate) – Buy Total Price WITHOUT Tax transformed in Sell Currency) / (Sell Unit Price * Sell Exchange Rate) * 100
- if Price CalculateMarginWithBuyExTax is false – the formula for Price Profit = ((Sell Unit Price * Sell Exchange Rate) – Buy Total Price WITH Tax transformed in Sell Currency) / (Sell Unit Price * Sell Exchange Rate) * 100
Price Remarks
Alphanumeric values and symbols (Price remarks text)
Price MinPersons and Price MaxPersons
(minimum/maximum number of persons)
Numeric value
Price MinAdults and Price MaxAdults
(minimum/maximum number of adults)
Numeric value
Price MaxChildren
(maximum number of children)
Numeric value
Price MaxInfants
(maximum number of infants)
Numeric value
Price MaxBabies
(maximum number of babies)
Numeric value
Price SupplierPriceCode
Alphanumeric values and symbols
Price Duration
Numeric value
Price FreeSales
The values for this column are true or false.
Price EmbarkAddress Id and Price EmbarkLocation
When the Embark Address ID is provided, Victoury searches for the address:
- If the address is found, it is set for Embark Address and for the Embark Location (price embark address name).
- If not found, then the price embark Address is not set and the price embark Location will be the value from column DB (Embark location).
When the Embark Address ID is not provided:
- The price embark Address is not set and the price embark Location = column DB (Embark location).
Price DisembarkAddress Id and Price DisembarkLocation
When the Disembark Address ID is provided, Victoury searches for the address:
- If found, it sets both the price disembark Address and price disembark Location (price disembark location address name).
- If not found, then the price disembark Address will not be set and the price disembark Location will be the value from column DD (Disembark location).
When the Disembark Address ID is not provided:
- The price disembark Address will not be set and the price disembark Location = column DD (Disembark location).
Price CreateAutomaticOrder
The values for this column are true or false.
Price CalculateMarginWithBuyExTax
The values for this column are true or false.
Price CataloguePrice
Numeric value
Price PublishOnWeb
The values for this column are true or false.
TypPrice Typee
ENUM field where the value must be one of the following: V (variable), B (Buy), C (Variable/Buy).
Price Position
Numeric value
Price BackofficeReference
Alphanumeric values and symbols (This is a unique value. Victoury does not allow duplicate Backoffice Reference codes).
Price Source
ENUM field where the value must be one of the following: 0 – for Local or 1 for Host2Host.
Price AutoSellTax
The values for this column are true or false.
Price StartTime
00:00
Price EndTime
23:59
Price SourceName
Alphanumeric values and symbols
Price MinBookingChangeQuantity
The values for this column are true or false.
Price MinBookingQuantity
Numeric value
Price BuyExchangeRateSC
Numeric value – exchange rate between buy and sell currency – in case of sell prices added in foreign currency this is usually different from Buy Exchange Rate field value
Price ChargeByGracePeriod
The values for this column are true or false.
Price Rules related fields
These fields may be used to edit Rules #43; #34 and #30. For optimal usage, it is recommended that a price having these Rules set is exported to have the system populate the fields accordingly.
Unsupported Fields
Export/Import data from Excel (import Product/Prices) does not support the following fields:
For Product:
- Booking Restrictions
- Allotment Type
- Asset – by asset ID
- Address – by supplier Address ID
- Trip – by trip ID
For Price:
- Supplier – by supplier ID
- Source
