H8.6.2.2b Import format for business data - LUPMISManual
Main menu:
8. LUPMIS Tools > 8.6 Revenue Tools > 8.6.2 Entry of revenue data
8.6.2.2b Import Format for Business 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 | |
| Business location | ESSENTIAL for BOP-5-factor | as %: 0,40,80,100 | 80 | I |
| Number of employees | ESSENTIAL for BOP-5-factor /b> | as %: 20,40,60,80,100 (not number of employees) | 20 | J |
| Impact on environment | ESSENTIAL for BOP-5-factor /b> | as %: 0,20,80,100 | 20 | K |
| Type of business office operation | ESSENTIAL for BOP-5-factor /b> | as %: 40,60,80,100 | 80 | L |
| Nature of business enterprises | ESSENTIAL for BOP-5-factor /b> | as %: 40,80,100 | 40 | M |
| DA assignment | as string, max length 30 | 12345ABC789 | N | |
| Business certificate | as string, max length 30 | 777GGG888 | O | |
| Employees | as number | 5 | P | |
| Business name | as string, max length 50 | LUPMIS Associates | Q | |
| Year of establishment | yyyy format, length 4 | 2011 | R | |
| Landmark 1 | as string, max length 50 | Behind church | S | |
| Landmark 2 | as string, max length 50 | Beside mosque | T | |
| Sub-Metro | as string, new | Northwest | U | |
| SubUrb | as string, new | Left | V | |
| Business owner | as string, max length 50 | Kofi | W | |
| Business owner address | as string, max length 50 | 9 Oxford St | X | |
| Business owner telephone | as string!, max length 15 | 0241234567 | Y | |
| Business owner email | as string!, max length 50 | Kofi@yahoo.com | Z | |
| Business owner id | as string, max length 12, for compatibility with older systems | S667799 | AA/i> | |
| Business class | ESSENTIAL | as code: 101,102,103... | 2101 | AB |
| 'Business' | ESSENTIAL | word 'Business' | Business | AC |
| Comments | as string, helpful is business class code not available | Blank | AD | |
| Previous reference code | as string | 12/456789 | AE | |
| Old address/house no. | as string | A12 A/B | AF | |
| Exempted | 1 for exempted, otherwise blank or 0 | (blank) | AG | |
| -99 | ESSENTIAL | control variable, as number | -99 | AH |
In total, there should be 34 columns (last column in Excel: AH).
File name should contain '_field_data_' (e.g. STMA_field_data_business_area1.csv)
General requirements:
- Empty cells are allowed, except where indicated (UPN, collector id, amount).
- If UPN subunit 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 3 below), 'Business', -99.
- 2. If you only update (!) Business Class and the 5 BOP factors, fill these 6 columns. You don't have to fill the other columns, except UPN (see also line 3 below), 'Business', -99.
- 3. For option 1 or 2 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.
