H8.7.2.2a Import format for property data - LUPMISManual
Main menu:
8. LUPMIS Tools > 8.7 Revenue Tools > 8.7.2 Entry of revenue data
8.7.2.2a Import Format for Property Data
Level of expertise required for this Chapter: Expert; specifically for LUPMIS @ TCPD
| Data | Essential ? | Format / Remark | Example | Excel column |
| UPN | ESSENTIAL | xxx-yyyy-aaaa format, as string | 678-0789-0123 | A |
| SubUPN | xxx-yyyy-aaaaA format as string or blank or single/double/triple letter | 678-0789-0123F | B | |
| Locality code | as string, max length 15 | CBD | C | |
| Street name | as string, max length 50 | Oxford St | D | |
| Property number | as string, max length 30 | 3 | E | |
| Floor | ESSENTIAL if above ground floor | as number, 0 for ground floor | 0 | F |
| Coordinates: x | UTM 30, new | 678456 | G | |
| Coordinates: y | UTM 30, new | 789567 | H | |
| Owner | ESSENTIAL | as string, max length 50 | Kofi | I |
| Owner address | as string, max length 50 | 7 Oxford St | J | |
| Owner telephone | as string!, max length 15 | 0241234567 | K | |
| Owner email | as string!, max length 50 | kofi@yahoo.com | L | |
| Owner id | as string, max length 12, for compatibility with older systems | S667799 | M | |
| Number of rooms | as integer number | 5 | N | |
| Year of construction | as number, 1800- | 2005 | O | |
| Property type | as code (0-10) | 2 | P | |
| Property use | ESSENTIAL | as code: 100,101..., 200..., 300... | 101 | Q |
| Persons in building | as number | 4 | R | |
| Roofing | as code (0-6) | 2 | S | |
| Ownership type | as code (0-6) | 3 | T | |
| Construction material type | as code (0-6) | 1 | U | |
| Number of storeys | as integer number | 2 | V | |
| Rateable value | in GHc, as integer | 30000 | W | |
| Valuation code | Deprecated, not used anymore | (blank) | X | |
| Sub-Metro | as string, new | Northwest | Y | |
| SubUrb | as string, new | Left | Z | |
| Descriptor | as code, integer | - | AA | |
| Planning permit | as code (0-2) | 1 | AB | |
| Planning permit number | as string, max length 30 | 678-0789-0123PP | AC | |
| Building permit | as code (0-2) | 1 | AD | |
| Building permit number | as string, max length 30 | 678-0789-0123BP | AE | |
| 'Property' | ESSENTIAL | word 'Property' | Property | AF |
| Comments | as string, helpful if property use code not available | Blank | AG | |
| Previous reference code | as string , works also as password for online access | 12/43794 | AH | |
| Old address/house no. | as string | 12 A/B | AI | |
| Exempted | 1 for exempted, otherwise blank or 0 | (blank) | AJ | |
| -99 | ESSENTIAL | control variable, as number | -99 | AK |
In total, there should be 37 columns (last column in Excel: AK).
File name should contain '_field_data_' (e.g. STMA_field_data_property_area1.csv)
General requirements:
- Empty cells are allowed, except where indicated (UPN, collector id, amount).
- If SubUPN is entered, it has to be unique (UPN + 1 or 2 or 3 letters).
- If necessary, insert empty columns in your spreadsheet.
- Codes for pay status, business class, property use etc can be viewed at the Printing Tools > Classes
- Observe the number -99 at the right-most column as control variable.
- No commas or and-signs(&) or quotations (" or ') are allowed anywhere in your Excel data, as they work as delimiters in the CSV file. Delete or replace them in Excel.
- Data from the CSV file will overwrite data in the LUPMIS Revenue Database. If the cell in the Excel/csv file is blank, this will NOT overwrite/delete the LUPMIS revenue data.
General preparations of CSV file in Excel:
- 1. If numbers are set up with comma as 1000-separator, delete it: Select area > Format cells > Number > Number > No tick on 'Use 1000 Separator' > OK .
- 2. If numbers are entered as text, convert them to numbers, e.g. insert in a column: +(cell of the text number)+0, copy this formula down to the whole range, then copy-paste all of them as values to a new column. .
- 3. Once you are sure, there are no commas in the number columns, convert all commas to points (Control-F > Replace).
Special import options:
- 1. If you only update (!) owner's data, fill all columns relating to owners data (name, tel etc). You don't have to fill the other columns, except UPN (see also line 4 below), 'Property', -99.
- 2. If you only update (!) property use, fill the column 'Property Use'. You don't have to fill the other columns, except UPN (see also line 4 below), 'Property', -99.
- 3. If you only update (!) rateable values, fill the column 'Rateable value'. You don't have to fill the other columns, except UPN (see also line 4 below), 'Property', -99.
- 4. For option 1 or 2 or 3 above, reference can be made to the column of previous reference code (if imported earlier, e.g. valuation code) instead of UPN/SubUPN. An 'x' has to be entered in column A (instead of UPN).
These special import options can be called by ticking their boxes at the last selection screen.
