From data entered on Excel spreadsheets, use this program to:

• Create new versions of existing rate rule records (new effective date/time) with updated prices
• Create new rate products (both rules and rates)
• Run the program in "Report mode" to test if the spreadsheets were set up correctly and will merge as desired.

Limitations to this program

1. This program CANNOT be used to update fields on existing Rate Rule records. Such changes would result in retro-active changes which may affect existing reservations and RAs. If rule fields must be changed, either:

a. Use this program to create a new Rule record with a new Effective Date and make the field change in this new record.
b. Use the Edit Rental Rules and Rates program to make the retro-active change to an existing Rule record.

2. In all cases, both a Rule record and it's corresponding Rate price records must be imported together. This is true even if there are no changes to the Rule record and only the prices are to be updated. In this case both a Rule record with an updated Effective Date/Time and the prices must be imported together.

Note: The following material describes the current functionality of this program in the latest release of CARS+. Early releases may not contain all the functionality describe below. To use this program you must be on a current release of CARS+.

I. CREATING THE SPREADSHEET

When a rate product is created, there are two parts to it: the RULES (data entered on pages 1, 2 and 3) and the RATES (data entered on pages 4 and 5). The rules and the rates are saved in separate files. Therefore, to update them a separate Excel spreadsheet is used for updating each file.

A. Create the spreadsheet on a PC or computer that is also used to access CARS+.

Click here to download a rules template file with the header row already completed.

Click here to download a rates template file with the header row already completed.

B. Entering data in the spreadsheets

Although the link above provides a template, the columns for the spreadsheet do not need to be in any particular order, and all columns need not be present. For your own purposes, the spreadsheet can contain additional columns unknown to CARS+. What is required is that the columns that contain data to be merged MUST have the column header specified in the chart shown at the end of this chapter.

• Both files, the Rules and the Rates are required to do a file import. This is true even if only the prices on the Rates file will be changed.
• In the first spreadsheet (rules), create a row for each Rate Rules record that should be imported.
• In the second spreadsheet (rates), create a row for each Class Rate record that should be imported or updated.
• A “blank” cell on the spreadsheet does not override existing data.

User tip: Date and Time format can be either:

YYYYMMDD (Example: 20150927 for September 27, 2015)

The local date format of either: MM-DD-YY or DD-MM-YY (This is specified in Edit Misc Control Record.)

The local time format should be: hhmm (or in WebXG it should be hh:mm )

C. Naming the spreadsheet file.

When saving the spreadsheet, the name assigned to it should follow Unix file name conventions which are:

• For the Rules file = RULEFLnnnnnn.csv where nnnnnn is a unique number (it is suggested to use the date the file was created). Do not use spaces in the file name.
• For the Rate file = RATEFLnnnnnn.csv where nnnnnn is the same number that was used on the Rules file. Do not use spaces in the file name.
• No spaces or slashes or any other punctuation must appear in the name (except for the period before "csv").
• If the date is used for nnnnnn in the file name, simply entering the date (without spaces or punctuation) on the import screen will be all that is required on the screen to find the file to merge.

D. Saving the spreadsheet.

First save it in your spreadsheet program's native format (for example if using Microsoft's Excel program, save it as .xls).

Re-save a copy in .csv format.

If an error in the data is discovered by the merge program, go back to the file in the native format and easily make any corrections. Then re-save it again as a comma delimited file (.csv format).

It is the copy saved in .csv format that will be used for the import. The first row of column headers must be included.

II. UPLOADING THE FILES FROM THE PC TO CARS+

Create the spreadsheets on a PC or computer that is also used to access CARS+. Once the spreadsheets are saved in the csv format, sign into CARS+ using the same computer.

To access the Import Rental Rules/Rates program, type IMPRATE (RET) at any menu "OPTION:" field or the appropriate line number on the Import Data sub-menu of the System Management menu.

• With the cursor at the "OPTION" field, press F4 to upload the file.
• A browser window will appear. Select the csv files from your PC and click "Send". Both the RULEFL and the RATEFL .csv files must be selected.

III. RUNNING THE IMPORT PROGRAM

Once the files have been uploaded (using the above instructions), they can be imported.

To access the Import Rental Rates program, type IMPRATE (RET) at any menu "OPTION:" field or the appropriate line number on the Importing Data menu (off the System Management menu). The screen will then display:

OPTION: __ IMPORT RENTAL RULES/RATES 1. Rule/Rate File Date 2. Run Mode U R)eport Only, U)pdate 3. Print Rejects Only N NOTE: DATE FORMATS HAVE CHANGED. ACCEPTABLE FORMATS ARE: MM-DD-YY YYYYMMDD 1DO MERGE 2 3ERASE 4UPLOAD 5BACKUP 6 7HELP 8

Enter data as follows:

1. RULE/RATE FILE DATE

The file can be loaded for processing by either: Typing the name of the RULEFL in this field (if the create date was used in the file name as suggested above, simply enter that date in the format nnnnnn) or Searching for it. To search, press F6 from this field. A search window will appear on the lower half of the screen. Select the appropriate RULEFL by typing the row number. The program will automatically locate the matching RATEFL to load. A sample search screen is shown below. EXAMPLE: Type 083115 (RET)

OPTION: __ IMPORT RENTAL RULES/RATES 1. Rule/Rate File Date 2. Run Mode U R)eport Only, U)pdate 3. Print Rejects Only N NOTE: DATE FORMATS HAVE CHANGED. ACCEPTABLE FORMATS ARE: MM-DD-YY YYYYMMDD Option: _ UPLOAD FILE SEARCH F2=NEXT 8 F4=PREV 8 File Name Create Date 1 RULEFL083115.csv Aug 31 2015 2 RATEFL083115.csv Aug 31 2015 ** No More Records **

2. RUN MODE	Enter: R = Run a test report. Do not make updates. U = Do the update and produce a report. Example: Type R (RET)
3. PRINT REJECTS ONLY	Enter: N = Produce a report of all records merged as well as those that were rejected. Y = Produce a report of only the rejected records. EXAMPLE: Press (RET)

Press F1 at the "OPTION" field to begin.

Report: Records that were merged successfully are shown on the report with the rate set, product, date, time and vehicle class. Records that are rejected from the merge will report an error message.


UPDATING RULES

The first four data columns (Rate Set, Rate Product, Effective Date, Effective Time) compose the "key" to the rules file. In other words, these four fields, taken together, are what make a record unique. Bearing this in mind:

1. If the data in all four of columns in the spreadsheet exactly match an existing record in the rules file, the new rules record will be rejected. (Retroactive changes to existing rules records are not allowed.)
   

2. If no match is found for the data in the first 4 columns, a logic check is done based on the fields that are dependent on another field. (See the "NOTES" column below.)
   

   1. If the dependence rule is broken, the new rules record will not be merged. (The dependence error will be noted on the error report.)
      

   2. If the dependence logic requirements are met, data from the import file will create a new rules record.

Click here to download a rules template file with the header row already completed.

The spreadsheet columns for the rules update, showing the required header field name and the relationship to the field displayed on the screen is shown below:

Spreadsheet Column	Page and Field	Field Description	Notes
Rule_disc_code	Page 3 Field 4	Disc Code
Rule_grp	Page 1 Field 1	Rate set	Data is required in this field.
Rule_prod	Page 1 Field 2	Product	Data is required in this field.
Rule_eff_date	Page 1 Field 3	Date	Data is required in this field. See note at beginning of this chapter regarding acceptable date formats.
Rule_eff_time	Page 1 Field 4	Time	Data is required in this field.
Rule_term_date	Page 1 Field 5	Must Pkup Before
Rule_comment	Page 1 Field 6	Comment
Rule_rate_type	Page 1 Field 7	Rate type
Rule_tier_days1	Page 1 field 8	Tier 1 Days
Rule_free_miles1	Page 1 Field 9	Tier 1/ Day Miles
Rule_free_meth1	Page 1 Field 10	Tier 1 Day Miles Method
Rule_tier_days2	Page 1 Field 11	Tier 2 Days
Rule_free_miles2	Page 1 Field 12	Tier 2/ Weekly Miles
Rule_free_meth2	Page 1 Field 13	Tier 2/ Weekly Miles Method
Rule_tier_days3	Page 1 Field 14	Tier 3/ Days
Rule_free_miles3	Page 1 Field 15	Tier 3/ Monthly Miles
Rule_free_meth3	Page 1 Field 16	Tier 3/ Monthly Miles Method
Rule_tier_days4	Page 1 Field 17	Tier 4 Days
Rule_free_miles4	Page 1 Field 18	Tier 4/ X-day Miles
Rule_free_meth4	Page 1 Field 19	Tier 4/ X-day Miles Method
Rule_free-hour	Page 1 Field 20	Free miles / hour
Rule_grace	Page 1 Field 21	Grace minutes
Rule_incl_grace	Page 1 Field 22	Charge for grace
Rule_discountable	Page 1 Field 23	Discountable
Rule_editable	Page 1 Field 24	Editable
Rule_incl_cdw	Page 1 Field 25	Include Cvg 1
Rule_incl_pai	Page 1 Field 25	Include Cvg 2
Rule_incl_pec	Page 1 Field 26	Include Cvg 3
Rule_incl_crg	Page 1 Field 26	Include Cvg 4
Rule_week_min	Page 1 Field 27	Min days for week	If data is entered in the Period Max Days field, data is required in this field.
Rule_period_max	Page 1 Field 28	Period Max Days
Rule_days_per_mth	Page 1 Field 29	Days per month
Rule_comm_code	Page 1 Field 30a	Comm Y/N
Rule_comm_cat	Page 1 Field 30b	Comm Cat
Rule_ra_type	Page 1 Field 31	Deflt RA Type
Rule_corp_std	Page 1 Field 32	Corp Std
Rule_searchable	Page 1 Field 33	Searchable
Rule_hide	Page 1 Field 34	Hide?
Rule_no_inv	Page 1 Field 35	No Invoice
Rule_incl_opset	Page 1 Field 36	Incl Opt Set
Rule_addon_optset	Page 1 Field 37	Addon Opt Set
Rule_incl_fpo	Page 1 Field 38	FPO Inc/Man
Rule_oneway_miles	Page 1 Field 39	One way Miles
Rule_alt_prod1	Page 1 Field 40	Alt Rate 1
Rule_alt_prod2	Page 1 Field 40	Alt Rate 2
Rule_currency	Page 1 Field 41	Currency
Rule_cal_calc	Page 1 Field 42	Calendar Day Calc
Rule_status	Page 1 Field 43	Status Flag
Rule_hour_pct	Page 1 Field 44	Hours Pct
Rule_yield_pct1	Page 1 Field 45	Yield Pct 1
Rule_yield_pct2	Page 1 Field 45	Yield Pct 2
Rule_yield_pct3	Page 1 Field 45	Yield Pct 3
Rule_yield_pct4	Page 1 Field 45	Yield Pct 4
Rule_yield_pct5	Page 1 Field 45	Yield Pct 5
Rule_t2_adj	Page 1 Field 46	Tier Pcts 2
Rule_t3_adj	Page 1 Field 46	Tier Pcts 3
Rule_t4_adj	Page 1 Field 46	Tier Pcts 4
Rule_allow_local	Page 1 Field 47	Allow Local
Rule_segment_code	Page 1 Field 48	Segment Code
Rule_hrs_to_day	Page 1 Field 49	Hrs Chg to Day
Rule_hrs_xhr_logic	Page 1 Field 50	Hrs Xhrs Logic
Rule_Miles_incl	Page 1 Field 51	Miles in Incl
Rule_exe_optset	Page 1 Field 52	Exempt Opt Set
Rule_disc_time	Page 1 Field 53	Disc. Only Time
Rule_seam_edit	Page 1 Field 54	Seamless Edits
[ Not available ]	Page 1 Field 55	Seam Edit Rate
[ Not Available ]	Page 1 Field 56	Exclude BillTo
Rule_prepaid	Page 1 Field 57	Rate Prepaid
Rule_paid_days	Page 1 Field 58a	paid Days
Rule_free_days	Page 1 Field 58b	Free Days
Rule_mod_empl	Page 1 Display fields	Modifying Employee user code, Date and Time
Rule_yield_code	Leave this column blank.	Reserved for future use
	PAGE 2
Rule_out_st_day	Page 2 Field 1	Earliest pickup day	If data is entered in any of the pick up and return fields, it is required in all fields.
Rule_out_st_hour	Page 2 Field 2	Earliest pickup time	If data is entered in any of the pick up and return fields, it is required in all fields
Rule_out_end_day	Page 2 Field 3	Latest pickup day	If data is entered in any of the pick up and return fields, it is required in all fields
Rule_out_end_hour	Page 2 Field 4	Latest pickup time	If data is entered in any of the pick up and return fields, it is required in all fields
Rule_in_st_day	Page 2 Field 5	Earliest return day	If data is entered in any of the pick up and return fields, it is required in all fields
Rule_in_st_hour	Page 2 Field 6	Earliest return time	If data is entered in any of the pick up and return fields, it is required in all fields
Rule_in_end_day	Page 2 Field 7	Latest return day	If data is entered in any of the pick up and return fields, it is required in all fields
Rule_in_end_hour	Page 2 Field 8	Latest return time	If data is entered in any of the pick up and return fields, it is required in all fields
Rule_wknd_chgto	Page 2 Field 9	Problem return change to	If data is entered in any of the pick up and return fields, data is required in this field.
Rule_end_meth	Page 2 Field 10	End Method
Rule_early_late	Page 2 Field 11	Early-late
Rule_flat_rate	Page 2 Field 12	Package rate
Rule_wknd_max	Page 2 Field 13	Wknd Rate Max Days
Rule_wknd_options	Page 2 Field 14	Wknd Options
Rule_pkup_mon	Page 2 Field 15	Start Day Monday
Rule_pkup_tue	Page 2 Field 15	Start Day Tuesday
Rule_pkup_wed	Page 2 Field 15	Start Day Wednesday
Rule_pkup_thu	Page 2 Field 15	Start Day Thursday
Rule_pkup_fri	Page 2 Field 15	Start Day Friday
Rule_pkup_sat	Page 2 Field 15	Start Day Saturday
Rule_pkup_sun	Page 2 Field 15	Start Day Sunday
Rule_pkup_chgto	Page 2 Field 16	Start Day Chg-to
Rule_min_days	Page 2 Field 17	Minimum Days
Rule_min_chgto	Page 2 Field 18	Min. days Change to
Rule_max_days	Page 2 Field 19	Maximum Days
Rule_max_chgto	Page 2 Field 20	Max days Change to
Rule_ow_chgto	Page 2 Field 21	intercity change to
Rule_tax_incl	Leave this column blank.	Reserved for future use	This attribute now handled by the use of Option Sets
Rule_drvr_min_age	Page 2 Field 22	Drvr Min Age
Rule_allow_zero	Page 2 Field 23	Allow Zero Rates
Rule_gst_incl	Leave this column blank.	Reserved for future use.	This attribute now handled by the use of Option Sets
Rule_vlf_exe	Page 2 Field 24	VLF Exempt
Rule_age_exe	Page 2 Field 25	AGEnn Exempt
Rule_srchg_exe	Page 2 Field 26	Surchg exempt
Rule_no_ft	Page 2 Field 27	Block FT
[ Not Available ]	Page 2 Field 28	Cross-reference Product	This field is not a part of the Rules File.
Rule_seasonal	Page 2 Field 29	Seasonal? Y/N
Rule_multi_period	Page 2 Field 30	Multi Period? Y/N
Rule_report_iq	Page 2 Field 31	Report RQ
Rule_res_only	Page 2 Field 32	Res Only
Rule_advancd_days	Page 2 Field 33	Advance Days
Rule_schedule	Page 2 Field 34	Drop Schedule
Quick Price Edit Fields
[ Not Available ]	Page 2 Field 35	Tier1	This field is not a part of the Rules File.
[ Not Available ]	Page 2 Field 36	Tier2	This field is not a part of the Rules File.
[ Not Available ]	Page 2 Field 37	Tier3	This field is not a part of the Rules File.
[ Not Available ]	Page 2 Field 38	Tier4	This field is not a part of the Rules File.
[ Not Available ]	Page 2 Field 39	Miles	This field is not a part of the Rules File.
[ Not Available ]	Page 2 Field 40	Intercity	This field is not a part of the Rules File.
Rule_est_miles_day	Page 2 Field 41	Estimated Miles	This field is used only when the system is configured in "RV" mode.
	PAGE 3
Rule_toll_opt_in	Page 3 Field 1	Toll Opt In
Rule_fixed_tier	Page 3 Field 2	Fixed tier rate
Rule_requote_days	Page 3 Field 3	Requote Days

UPDATING RATES:

The first five data columns (Rate Set, Rate Product, Effective Date, Effective Time, Class) compose the "key" to the rate file. In other words, these five fields, taken together, are what make a record unique. Bearing this in mind:

1. The data in the first four columns of the spreadsheet must exactly match a record in rules file. The import is subject to the following rules:

   1. No changes to existing rates. The existing rates will not be changed in any way by the data from the import file. When there is a class in the import file that matches a class in the existing record, the record will be rejected and will be listed on the error report.

      New rates can be added. If there is no existing record for the class, a new row of rates for that class will be created. All dependency logic is checked (see "Notes" column.) If a dependency is broken, the rate will not be merged and will be listed on the error report.

   2. Additional logic checks between the rules and rates record will be made:
      
      • If the Earliest Pickup field has been altered (typically for a weekend rate), there must be a rate in the weekend column. The same is true for all levels of a yield managed rate.
      • If a number has been entered in the Period Max Days field, there must be a rate in the week column. The same is true for all levels of a yield managed rate.
      • If yield percents have been entered, there must be daily rates for the corresponding yield levels.
      • If the rate is a tiered rate, each defined tier must have a rate. The same is true for all levels of a yield managed rate.

2. If the data in the first four columns of the spreadsheet do not exactly match an existing rules record, the rate records in the import file will be rejected and will be listed on the error report.

CAUTION:

• The program will reject a record on the spreadsheet that has no matching rate set and rate product, and the program will immediately terminate. All records on the spreadsheet after that point are ignored and not processed.

• When a new record is created, only those classes with rows of data on the spreadsheet will be set up. For example, if a rate currently has 10 classes on it, and the spreadsheet has records for only 2 classes, the new record will have only the 2 classes from the spreadsheet.

Click here to download a rates template file with the header row already completed.

The spreadsheet columns for the rates update, showing the required header field name and the relationship to the field displayed on the screen is shown below:


After the file has been merged, the source file is moved to the directory imprat_doneXX (where XX is the Company Number).