Import society members from Excel

When the society roll comes to you as a spreadsheet, or has a hundred names, import it instead of typing. You download the fixed template (or export the members you already have), fill it in Excel, and bring it back. The app checks every row first and shows what it would add, update or reject; it writes nothing until the whole sheet is clean, so a half-imported roll can't happen.

The same dialog works from the society's own page and from the project's Society & Tenants tab. The dialog and the import rules are the members' version of the app-wide Importing and exporting Excel sheets.

Before you start

• The society exists: Record a society.
• You need both the Create and the Edit permission on Tenants; the Import button is hidden otherwise. Export and Download template need the View permission on Tenants. In the default roles the Administrator, Rehab Officer and Project Manager can import.
• The unit types you name in the sheet must exist under Settings > Lookups > Unit types; the template's dropdown lists them.
• The Tenant contact type must exist under Settings > Lookups > Contact types (it does in a new organization). Every added row creates a contact of that type.
• Excel (.xlsx) is the format; a .csv saved from an older template is still read.

Steps

1. Open the society's page (sidebar Projects > Societies, then click the society's name), or the project's Society & Tenants tab, and find the card "Members of <society>". Export and Import sit at the top right.

2. Click Import. The dialog Import members opens and explains: "The workbook has a fixed set of columns, and the closed ones are dropdowns fed by its Lists sheet. Export or download the template, fill it in, and bring it back." and "Rows with a tenant_id (or a membership number already here) update that allottee; the rest are added. Every row is a member of this society. Co-owners are not part of the sheet." The Import button is greyed out with "Choose a filled-in sheet first".

3. Get a sheet to fill:

   • Download template gives an empty workbook, "members-template.xlsx", with one example row (Ramesh Kulkarni) that the import skips; or
   • close the dialog and click Export on the card (hover "Download these members as an Excel sheet") for "<society> members.xlsx", every member already recorded, in the same layout. Use this to correct or complete the roll in bulk.

4. Fill the Data sheet in Excel, one row per member, without changing the header. The columns are:

   Column	What it takes
   tenant_id	"Leave blank for a new allottee. Filled in by the export; keep it to update that row."
   salutation	"Mr, Mrs, Ms, Dr… Optional." (a dropdown)
   first_name	"Required."
   last_name	"Optional."
   phone	"Required, 5 to 20 characters."
   alt_phone, email	Optional.
   pan_no	"Optional; upper-cased on save."
   membership_no	"Optional, unique within the society or project. Used to match a row that has no tenant_id."
   existing_unit_no	"The flat or shop held today. Optional."
   existing_floor	"Floor of that unit in the old building (3, Ground, Stilt ...). Optional."
   existing_unit_type	"One of the organization's unit types (Settings > Lookups). Optional." (a dropdown)
   existing_bedrooms	"Number, 0 to 20; halves allowed (2.5). Optional."
   existing_carpet_area_sqft	"Number. Optional."
   consent	"Yes / No. Society members only; ignored for landowners and PAP tenants." (a dropdown)
   consent_date	"YYYY-MM-DD. Society members only."

   The dropdown columns refuse other values inside Excel ("Pick one of the values listed on the Lists sheet."). The same text is under What each column takes at the bottom of the dialog. Save the workbook as .xlsx.

5. Back in Import members, click Choose Excel file and pick the workbook. "Checking…" runs straight away and nothing is written. You then see:

   • the counts: "<n> to add", "<n> to update", "<n> skipped", "<n> with errors";
   • a table of every row that has a note or an error, as "Row <n>" with the member's name. The template's example row is skipped with the note "Example row from the template", and blank rows with "Empty row";
   • "Checked, nothing written yet. Use Import to apply it." when every row is good, or "Nothing has been written. Fix the rows below in your sheet and choose the file again." when some aren't.

6. If any row is "with errors", correct those rows in Excel (each error names the column, for example "phone: required, 5 to 20 characters"), save, and click Choose another file to check again. Import stays greyed out with "Fix the <n> rows listed below, then choose the file again" until the sheet is clean. If every row is skipped it reads "This sheet has no rows to add or update".

7. Click Import <n> rows. The rows are written in one go and you see "<n> added, <n> updated." and "Imported into <society>." The button turns into Done; close the dialog and the grid shows the members.

What happens next

• Each added row becomes a member with a new contact of the Tenant type; the import never matches an existing contact by phone, so export first when the roll is already partly recorded, and keep the tenant_id column to update rather than duplicate.
• On an updated row, a blank cell leaves what is stored alone, except consent and consent_date, which are written as they stand on every member row: a blank consent cell records the member as not consented and clears the date.
• Co-owners aren't in the sheet: add them on the member's form or the member's page. See Enter society members, their consent and co-owners.
• The members now show on the project's Society & Tenants tab, ready for offers (see Society & Tenants). In the Project setup wizard, Existing members counts as done.
• Landowners and PAP tenants have their own sheet ("allottees-template.xlsx") on the project tab's Landowners & PAP tenants card, with a category column instead of consent. See Record landowners and PAP tenants.

If something goes wrong

• Row errors listed in red under a row, for example "first_name: required", "phone: required, 5 to 20 characters", “membership_no: "<x>" is already used here”, “membership_no: "<x>" also appears on row <n>”, “existing_unit_type: No unit type called "<x>". Known types: …”, "existing_bedrooms: <v> is out of range", “consent: "<v>" is not Yes or No”, “consent_date: "<v>" is not a date (use YYYY-MM-DD)” or "tenant_id: no allottee with this id here (was the sheet exported from another society or project?)": fix the cell and choose the file again. See Import rows rejected (trouble.societies.import-rows-rejected).
• A red alert about the whole file: "That file could not be read as an Excel workbook (<detail>)", "The file is not readable: save it as .xlsx from Excel", "The sheet is missing the column(s): <columns>. Download the template and fill that in.", "The sheet has a header but no rows", "The workbook has no rows", "The file has no header row" or "The file is empty". Start again from the template or the export and save as .xlsx. See Import file not readable (trouble.societies.import-file-not-readable).
• Import isn't shown on the card: your role lacks the Create or the Edit permission on Tenants. See No permission (trouble.common.no-permission).

Related

• Society
• Society & Tenants
• Importing and exporting Excel sheets
• Lookups
• Enter society members, their consent and co-owners
• Record a society