Excel xlsx Format
This page details how to create an Excel xlsx spreadsheet to import into Wise Owl Legal
Each xlsx spreadsheet can contain multiple worksheets (the tabs at the bottom of Excel). You can import multiple types of records in the same spreadsheet by including them in different worksheets. You can even split records of the same type across multiple worksheets. Each record type is imported in the order listed below, then in the order of worksheets within a spreadsheet (left to right).
Record type import order:
- Countries
- Contact Type Tags
- Contact Address Types
- Contact Phone / Email Types
- Matter Archive Location
- Matter Contact Types
- Fee Earner Types
- Referral Sources
- Jurisdictions
- Matter Types
- Banks
- Forms of Funds
- Users
- Contacts
- Matters
Excel spreadsheets can be uploaded to an Appliance (generally, files are limited to ~10MB) or the Appliance can download a file from another web server (no size limitation).
Each worksheet has a unique key column to identify each record. This must never be duplicated or your import will fail. This includes records which may already be in the Wise Owl Appliance. The Wise Owl database automatically assigns each record a primary key as it imports them, but this is not directly visible to you.
All required columns must be present in a spreadsheet. Any optional columns can be omitted.
Some fields allow multiple values. These can be entered as comma separated (eg: First, Second, Third, and so on) or on separate rows in Excel. If using separate rows, you should duplicate non-repeating data on each row (in particular, the unique key, so each row can be identified). A single record must exist in adjacent rows (that is, you cannot have a row for user 'larry.lawyer', then 'rachel.reception' and then 'larry.lawyer'. Wise Owl will interpret the second Larry Lawyer as a third record and fail because a 'larry.lawyer' is already in the database).
Important: Worksheets must be named correctly so the importer can recognise the record type. The default names in Excel "Sheet1" will not work; you must rename your worksheets to import.
TODO: Karen, can you attach some of you examples here, for reference.
Drop Down Lists
The spreadsheet format for drop down lists is very similar. Most have a Name or Label column, and a numeric Sort order and a yes / no IsActive.
Contact Type Tags
Worksheet must start with ContactType
Unique key name: Name
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Name | Text, max 100 | Y | N | Name of the contact type, as shown on contact page. Must be unique. |
| Sort | Numeric | N | N | Sort order override. Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Countries
Worksheet must start with CountryList
Unique key name: Name
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Name | Text, max 100 | Y | N | Name of the country. Must be unique. |
| Code | Text, max 10 | Y | N | Short code for the country. Must be unique. |
| Sort | Numeric | N | N | Sort order override. Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Contact Address Types
Worksheet must start with ContactAddressType
Unique key name: Label
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Name | Text, max 50 | Y | N | Name of the address type, as shown on contact page. Must be unique. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Contact Phone, Email and other Contact Methods
Worksheet must start with ContactPhoneEmailType
Unique key name: Label
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Label | Text, max 50 | Y | N | Name of the contact method, as shown on contact page. Must be unique. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Matter Archive Locations
Worksheet must start with ArchiveLocation
Unique key name: Name
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Name | Text, max 100 | Y | N | Name of the archive location, as shown on matter page. Must be unique. |
| Details | Text, max 250 | N | N | Details of the archive location (eg: an address). |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Matter Contact Types
Worksheet must start with MatterContactType
Unique key name: Label
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Label | Text, max 100 | Y | N | Name of the contact type, as shown on matter page. Must be unique. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Fee Earner Types
Worksheet must start with FeeEarnerType
Unique key name: Label
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Label | Text, max 100 | Y | N | Name of the fee earner type, as shown on matter page. Must be unique. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Referral Sources
Worksheet must start with ReferralSource
Unique key name: Name
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Name | Text, max 100 | Y | N | Name of the referral source, as shown on matter page. Must be unique. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Jurisdictions
Worksheet must start with Jurisdiction
Unique key name: Name
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Name | Text, max 100 | Y | N | Name of the referral source, as shown on matter page. Must be unique. |
| CountryCode | Text, max 10 | N | N | The country and state codes together must be unique. The country shoul be left blank for an International jurisdiction. |
| StateCode | Text, max 10 | N | N | The country and state codes together must be unique. The state should be left blank for a Federal jurisdiction. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Matter Types
Worksheet must start with MatterType
Unique key name: Name
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Name | Text, max 80 | Y | N | Name of the referral source, as shown on matter page. Must be unique. |
| Code | Text, max 12 | Y | N | Must be unique |
| Description | Text, max 250 | N | N | |
| JurisdictionType | Jurisdiction Type | Y | N | One of International, Federal or State. The first letter is sufficient. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Banks
Worksheet must start with Bank
Unique key name: Label
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Label | Text, max 50 | Y | N | Name of the bank, as shown on trust and general accounting pages. Must be unique. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Forms of Funds
Worksheet must start with FormOfFunds
Unique key name: Label
| Field | Type of Data | Required | Multiple Values | Comments |
|---|---|---|---|---|
| Label | Text, max 100 | Y | N | Name of the form of funds, as shown on trust and general accounting pages. Must be unique. |
| Sort | Numeric | N | N | Sort order override Defaults to 1 if not specified. |
| IsActive | Yes / No | N | N | Yes, No, Y, N, True, False are all valid values. Defaults to Y if not specified. |
Users
Worksheet must start with User eg: Users, Users_Active
Unique key name: Username
| Field | Type of Data | Required | Multiple Values | Relates To | Comments |
|---|---|---|---|---|---|
| Username | Text, max 128 | Y | N | Unique key to identify users | |
| AuthenticateBy | Text | Y | N | External Directories | Use If you use an External Directory, the username must match exactly and already exist in the directory Using Appliance Internal means you must specify an |
| InitialPassword | Text | Maybe | N | Required if AuthenticateBy is Appliance Internal | |
| IsActive | Yes / No | Y | N | Yes, No, Y, N, True, False are all valid values | |
| FirstName | Text, max 128 | Y | N | First and last names must be specified | |
| LastName | Text, max 128 | Y | N | First and last names must be specified | |
| OtherNames | Text, max 128 | N | N | ||
| Initials | Text, max 8 | Y | N | Initials must be unique between all active users, as they are used to identify people in many circumstances | |
| Text, max 256 | Y | N | Email must be unique across all users, as it is used for automatic password recovery | ||
| PreferHtmlEmail | Yes / No | N | N | Defaults to Y if not specified | |
| HomePhone | Text, max 32 | Maybe | N | At least one phone number must be entered | |
| OfficePhone | Text, max 32 | Maybe | N | At least one phone number must be entered | |
| MobilePhone | Text, max 32 | Maybe | N | At least one phone number must be entered | |
| Language | Language code | N | N | Languages | Defaults to the Appliance language (usually en-AU). Only English dialects are supported. This affects how dates and currency symbols are displayed. |
| TimeZone | Time zone name | N | N | Time Zones | Defaults to the Appliance time zone. Affects how dates and times are interpreted. Generally, this is unimportant unless you have users in different time zones. Time zone names are taken from Windows; you can see a list of them in your Date and Time settings in Control Panel. Note: time zone codes like AEST are not supported. |
| DefaultSearchItemsPerPageDesktop | Whole Number | N | N | The number of records displayed on search pages when viewed from desktop / laptop browsers. Defaults to the Appliance Setting if not specified. Users can override this on their My Settings page. | |
| DefaultSearchItemsPerPageMobile | Whole Number | N | N | The number of records displayed on search pages when viewed from mobile / tablet browsers. Defaults to the Appliance Setting if not specified. Users can override this on their My Settings page. | |
| LoginActivityTimeout | Time | N | N | Defaults to 2 hours if not specified | |
| ShowProfiler | Yes / No | N | N | Shows the time for the Appliance to generate each page in the top right corner. Users must have the Can See Page Creation Time right for this to have any affect. | |
| ShowTutorialOnLogin | Yes / No | N | N | Directs the user to the tutorial page when they first login. Users can opt-out of this tutorial via their My Settings page. | |
| Roles | Role name | N | Y | Roles | No roles are required. However, none are assigned by default so a user without any roles will not even be able to log into Wise Owl Legal. |
| DenyRights | Right name | N | Y | Rights | Rights listed here are denied to the user, even if they are granted via a role. |
| GrantRights | Right name | N | Y | Rights | Rights listed here are granted to the user, even if they are denied via a role. |
Contacts / Clients
Worksheet must start with Contact eg: Contacts, Contacts_2010, Contacts1
Unique key name: ContactNumber
Important: contact auto-numbers are not changed when importing records. You must manually update the contact auto-number in Appliance Settings after import.
| Field | Type of Data | Required | Multiple Values | Relates To | Comments |
|---|---|---|---|---|---|
| ContactNumber | Text, max 32 | Y | N | Unique key to identify contacts and clients | |
| IsActive | Yes / No | Y | N | Yes, No, Y, N, True, False are all valid values | |
| IndividualOrCompany | I or C | Y | N | Determines if FirstName / LastName or CompanyName are required | |
| FirstName | Text, max 100 | Maybe | N | Required if IndividualOrCompany is I. Does not apply to company contacts. | |
| LastName | Text, max 100 | Maybe | N | Required if IndividualOrCompany is I. Does not apply to company contacts. This is always capitalised when displayed. | |
| OtherNames | Text, max 100 | N | N | Does not apply to company contacts. | |
| CompanyName | Text, max 200 | Maybe | N | Required if IndividualOrCompany is C. Does not apply to individual contacts. | |
| Title | Text, max 20 | N | N | Does not apply to company contacts. | |
| KnownAs | Text, max 200 | N | N | ||
| DateOfBirth | Date | N | N | Date of Incorporation for company contacts. | |
| ABN | Numeric | N | N | Australian Business Number. Spaces are allowed between digits for formatting. | |
| ACN | Numeric | N | N | Australian Company Number. Spaces are allowed between digits for formatting. | |
| TFN | Numeric | N | N | Tax File Number. Spaces are allowed between digits for formatting. | |
| Gender | Male / Female | N | N | Does not apply to company contacts. | |
| IsAustralianCitizen | Y / N | N | N | Is Australian Company for company contacts | |
| PowerOfAttorneySigned | Y / N | N | N | ||
| CountryOfBirth | Country | N | N | Country of Incorporation for company contacts. Can be either a 2 letter country code (eg: AU) or full country name. | |
| Occupation | Text, max 100 | N | N | Principal Business Activity for company contacts | |
| EmployerContactNumber | Contact | N | N | Contacts | References another contact's ContactNumber field. |
| AlternateContactNumber | Contact | N | N | Contacts | References another contact's ContactNumber field. |
| AlternateContactRelationship | Text, max 50 | N | N | Relationship of alternate contact to this one (eg: husband, wife, parent, secretary) | |
| Notes | Text | N | N | Free text notes. Can contain line breaks. | |
| ContactTypes | Contact Type tags | N | Y | Contact Type | |
| Address | Addresses must be listed across multiple rows in Excel. Each of the following address fields are taken to form a single address record, attached to the contact. | ||||
| AddressType | Address Type names | N | Y | Address Type | Multiple addresses can be listed, each with a different address type. |
| AddressStreetLine1 | Text, max 100 | N | Y | ||
| AddressStreetLine2 | Text, max 100 | N | Y | ||
| AddressSuburb | Text, max 100 | N | Y | ||
| AddressStateProvence | Text, max 20 | N | Y | ||
| AddressPostcodeZip | Text, max 20 | N | Y | ||
| AddressCountry | Country | N | Y | Can be either a 2 letter country code (eg: AU) or full country name. | |
| AddressFromDate | Date | N | Y | ||
| AddressToDate | Date | N | Y | ||
| Phone, Email, Web, Etc | The field names for phone numbers, email addresses, and other means of contacting people are dynamically determined based on the list in your Appliance. The following are common examples. | ||||
| WorkPhone | Text, max 250 | N | N | ||
| HomePhone | Text, max 250 | N | N | ||
| MobilePhone | Text, max 250 | N | N | ||
| Pager | Text, max 250 | N | N | ||
| Facsimile | Text, max 250 | N | N | ||
| WorkEmail | Text, max 250 | N | N | ||
| HomeEmail | Text, max 250 | N | N | ||
| WorkWebsite | Text, max 250 | N | N | ||
| Custom Fields | Custom fields are referred to via their number, not name. As many as are present in the Appliance Settings are supported. | ||||
| Custom1 | As per custom field definition | N | N | ||
| Custom2 | As per custom field definition | N | N |
Matters
Worksheet must start with Matter eg: Matters, Contacts_Archived, Matters_2012
Unique key name: MatterNumber
Important: mater auto-numbers are not changed when importing records. You must manually update the matter auto-number in Appliance Settings after import.
| Field | Type of Data | Required | Multiple Values | Relates To | Comments |
|---|---|---|---|---|---|
| MatterNumber | Text, max 32 | Y | N | Unique key to identify matters | |
| Type | Matter Type name or code | Y | N | Matter Type | Can refer to these via the short code or full name |
| Jurisdiction | Jurisdiction name | Y | N | Jurisdiction | |
| Status | Open, Closed or Archived | Y | N | Certain other fields will be required if Closed or Archived is entered here | |
| OpenDate | Date | Y | N | ||
| Name | Text, max 250 | Y | N | This is copied to the first Re Line if the Re Line is not entered | |
| AbbreviatedName | Text, max 50 | N | N | ||
| Clients | Client Numbers | Y | Y | Contact | The first listed will be designated as Primary Client, any subsequent ones will be listed as Clients. Use the |
| ReLine1 | Text, max 250 | N | N | If not entered this will be set to the Name field | |
| ReLine2 | Text, max 250 | N | N | ||
| ReLine3 | Text, max 250 | N | N | ||
| OtherSideRef | Text, max 40 | N | N | ||
| OtherContactType | Contact Types | N | Y | Contact Type | Contact Type and Number allow additional contacts to be listed against a matter. Multiple can be entered across multiple Excel rows, but not as a CSV in the one cell. |
| OtherContactNumber | Contact Numbers | N | Y | Contact | Do not list additional clients here, use the Clients field instead. |
| ReferralSource | Referral Source name | N | N | Referral Source | |
| LimitationDate | Date | N | N | ||
| ClosedDate | Date | Maybe | N | Required if Status is Closed | |
| ArchivedDate | Date | Maybe | N | Required if Status is Archived | |
| ArchiveLocation | Archive Location name | Maybe | N | Archive Location | Required if Status is Archived |
| TrustAuthoritySentBy | Username | N | N | User | Required if TrustAuthoritySentDate is entered |
| TrustAuthoritySentDate | Date | N | N | Required if TrustAuthoritySentBy is entered | |
| RelatedMatters | Matter Numbers | N | Y | Matter | |
| Fee Earners | Note that the field names are dynamically determined based on fee earner types listed in your Appliance Settings. The following are system fee earner types, and will be present in all Wise Owl Appliances. | ||||
| IntroducingLawyer | Username | N | N | User | |
| ResponsibleLawyer | Username | N | N | User | |
| SupervisingLawyer | Username | N | N | User | |
| AssignedLawyer | Username | N | N | User | |
| Paralegal | Username | N | N | User | |
| ConflictCheckFor | Username | Y | Y | User | |
| ConflictCheckEnteredBy | Username | Y | Y | User |