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.
RequiresSolicitor	Yes / No	N	N	Yes, No, Y, N, True, False are all valid values. Defaults to N if not specified. If yes, any user entered against this fee earner type as an Assigned User, must have the Is Practicing Solicitor right.

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 matter type, 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 `Appliance Internal` to store passwords on the Appliance, or an existing directory name to use an Active Directory service 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`
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
Email	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. List of all language codes
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.
TwoFactorActive	`Never` or `Always` or `OnlyUntrustedNetwork`	N	N		Determines if two factor authentication is enabled for this user. The default and allowed values are affected by the Appliance security mode. Offline defaults to `Never` and allows any option. Online defaults to `OnlyUntrustedNetwork` and disallows `Never`. An untrusted network is any network not listed in LAN Addresses in Security Settings. Generally, it means "the Internet".
TwoFactorDefault	`SMS Code` or `Smart Phone Code Generator`	N	N		Default way to perform two factor authentication. Defaults to `Smart Phone Code Generator` - an app which must be loaded onto any smart phone. Note: `SMS Code` is currently not implemented.
TwoFactorExpiresAfterDays	Whole Positive Number	N	N		Determines how often the user will need to re-enter their two factor code on a previously authenticated web browser. This defaults to forever (a very large number!). Any whole number of days is valid. Zero means the user must enter their code on every login.
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 `OtherContactType` and `OtherContactNumber` fields to list other companies or persons related to the matter.
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