Import roomMaster Data
roomMaster allows you to import Guest Profiles, Travel Agent, Company Master/City Ledger, Folio Transactions and Reservation information. This could be from your existing Property Management System during a conversion.
The import procedure will read all records from a ASCII file delimited by either commas or tabs (ASCII 44 or ASCII 9), or by Excel (XLS/XLSX).
IT IS VERY IMPORTANT TO DEFORMAT ANY DATE or TIME VALUE THAT IS IMPORTED INTO roomMaster! An example would be if the date is in mm/dd/yy format, then you must enter @D1 as the Deformat Picture. Usually use @D17 for date fields so the dates are converted from your local country.
Creating a Translation
Map the data from your file to the database.
Map to Field
Highlight a field to fill from your file and press the Map Field button. Then, select the corresponding field in your file.
-
Case Convert - Same case will map the data as-is from your file. Capitalize (Mixed) will convert "123 main street" and "123 MAIN STREET" to "123 Main Street". Upper Case will convert the entire string to all uppercase letters.
-
Special Formatting - You can strip all data except for numeric's which will happen before any data picture. Example: If the data in your file is (203) 555-1212 for phone number, by checking this box, the data will be converted to 203555112. This is helpful for phone numbers (but not for zipcodes!).
-
Select Convert to Decimal if you wish to strip all data except for currency digits (such as 0 through 9, negative sign and decimal point). If the currency value that you are bringing in has a value such as $(1,123.44) which is the same as -1123.44, you would select this option so the system will bring this formatted value in to the system properly as unformatted regular decimal data.
-
Map Pictures - Pictures allow you to format the data before placing it in the file. There are three types of pictures (@s which are string, @n which are numeric and @p which are formatted pictures) - Pictures are not required and if you do not enter a picture, the data is mapped to the file unchanged.
For string pictures, use @Snn where n is the maximum number of characters the string will ever hold.
For numeric pictures, use @Nii.dd where i is the digits and d is the decimal places (@n10.2 will be 10 digits with 2 decimal places)
| Map | Example | Description |
|---|---|---|
| @N9 | 4,550,000 | Nine digits, group with commas (default) |
| @N_9B | 4550000 | Nine digits, no grouping, leading blanks if zero |
| @N09 | 004550000 | Nine digits, leading zero |
| @N*9 | ***45,000 | |
| @N9_ | 4 550 000 | Nine digits, group with spaces |
| @N9. | 4.550.000 | Nine digits, group with periods |
| @N9.2 | 4,550.75 | Two decimal places, period decimal separator |
| @N_9'2 | 4550,75 | Two decimal places, comma decimal separator |
| @N9.'2 | 4.550,75 | Comma decimal separator, group with periods |
| @N9_'2 | 4 550,75 | Comma decimal separator, group with spaces, |
| @N-9.2B | -2,347.25 | Leading minus sign, blank if zero |
| @N9.2- | 2,347.25- | Trailing minus sign |
| @N(10.2) | (2,347.25) | Enclosed in parens when negative |
Pattern Pictures @P[<][#][x]P[B]
| @P | All pattern pictures begin with the @P delimiter and end with the P delimiter. The case of the delimiters must be the same. |
| < | Specifies an integer position that is blank for leading zeroes. |
| # | Specifies an integer position. |
| x | Represents optional display characters. These characters appear in the final result string. |
| P | All pattern pictures must end with P. If a lower case @p delimiter is used, the ending P delimiter must also be lower case. |
| B | Specifies that the format displays as blank when the value is zero. |
Pattern pictures contain optional integer positions and optional edit characters. Any character other than < or # is considered an edit character which will appear in the formatted picture string. The @P and P delimiters are case sensitive. Therefore, an upper case "P" can be included as an edit character if the delimiters are both lower case "p" and vice versa. Pattern pictures do not recognize decimal points, in order to permit the period to be used as an edit character. Therefore, the value formatted by a pattern picture should be an integer. If a floating point value is formatted by a pattern picture, only the integer portion of the number will appear in the result.
Example:
| Pattern | ||
|---|---|---|
@P###-##-####P | 215846377 | 215-84-6377 |
@P<#/##/##P | 103159 | 10/31/59 |
@P(###)###-####P | 3057854555 | (305)785-4555 |
@P###/###-####P | 112 | 411A-2 |
@PA##.C#P | 312.45 | A31.C2 |
Phone numbers should be @P###-##-####P if numeric and @s20 if non-numeric.
- Deformat Picture - This function removes formatting characters from a numeric string. If you import a Date, Time, or Currency value, you must deformat your value so it is imported properly. Dates for example can be in MMDDYY, or MM/DD/YY or MM/DD/YYYY or YYYY/MM/DD format and the system needs to know the format to properly bring it in to roomMaster. This is also true with amounts. You may import a credit as -123.00 or 123.00- or (123.00) and the system needs to know how to expect the data.
By deformattting, the imported value removes formatting characters from a numeric string, returning only the numbers contained in the string. When used with a date or time picture (except those containing alphabetic characters), it returns the property roomMaster date and time format.
For more information about pictures, see above as well as Date, Time and Currency Pictures.
Value If field is blank - If the field you are mapping is blank, you can enter a literal value which will be mapped in its place.
Literal - Enter a constant value to be placed in the field in the database. This can be any letters and/or numbers. An example would be you want a 10% commission on all travel agent records imported, so you would enter 10.00 for Commission Amount.
Check/UnCheck - If the field is True/False or Checked/Unchecked, you can use this button to set the value properly.
Formula - Once all the mapping has completed (or no field used), a Clarion Formula can be used to further convert the value.
Example of using the first three characters only of a mapped field would be: SUB(ThisField,1,3)
Example of using the date from yesterday: FORMAT(TODAY() -1 ,@D017)
Example of converting a corresponding value 1 through 4: CHOOSE(ThisField,'Blue','Green','Red','Yellow')
Clear Field - Clears the mapping contents of a field whether it be by "Map Field or "Literal".
Save/Open - You can save and call the translation format that you have created.
Clear All - Clears all the Update Values and Formulas
Debug - Executes the Import process but does not actually update any files. You can use this to see what values are mapped to each field before actually running the process and having it effect the database.