Overview
When you are working with one or more systems and migrating the data to ZenCase, there are a few tips and tricks that we will recommend to help you ensure the best migration experience with ZenCase.
Obtaining Data From Another System For Import
The easiest way to obtain the data you need from another system is to export the data via reports. If you are migrating from another system that has reporting functionality, then we recommend exporting the data with reports to CSV or Excel files if possible.
The other option will be to work with a consultant to extract the data as a SQL backup file in which they can work with a copy of the SQL database from a specific point in time. Data can be extracted from the database tables and formatted to fit into a ZenCase import template.
Cleaning Up Data
Contact addresses, phone numbers, and email addresses must be valid to import.
You can use create contact custom fields if you must support invalid data.
We validate addresses must have valid zip codes for corresponding states.
We validate phone numbers must have valid area codes and area codes must exist.
We validate email addresses must have "@" symbol.
Remove or replace all line breaks from all cells that have text data.
Examples: memos, descriptions, names.
Tip: Download Notepad++ on Windows to view csv files and see if there are any line breaks or carriage returns in columns.
How to view hidden characters in Notepad++
Open the text or code file in a Notepad++
Go to View Menu > Select Show Symbol > Select Show End of Line.
It displays all CR LF to LF characters in the opened file.
Also, there are other options to the Show Symbol menu item.
Show White spaces and TAB
Show End of Line
Show All Characters
Show Indent Guide
Show Wrap Symbol
Formatting Data
Ensure proper formatting is used for all template fields.
Validate your CSV file to make sure you have a valid CSV file.
You can install a CSV lint tool to Notepad++ for finding errors. With video instructions: CSV Lint plug-in for Notepad++, view and edit text data more easily
Ensure all data is formatted to fit into a ZenCase template column.
ZenCase reads CSV template files column headers, so the order of the headers will not affect the import. However, you must have the exact column header names as defined in our help articles and in our example template files that are available for download in each article.
Dates should be formatted as "MM-DD-YYYY" or "MM/DD/YYYY".
All Dates are imported with 12:00AM EST timestamp.
Use the format "YYYY-MM-DD HH:MM:ss" to override the timestamp on the dates imported into ZenCase.
This is useful for time zone differences, such subtracting 3 hours for PST.
Booleans must be from the following list below:
True values: true, 1, t, T, true, TRUE, on, ON, y, Y, yes, YES, Yes
False values: false, 0, 0, f, F, false, FALSE, off, OFF, n, N, no, NO, No
Addresses
State must be the two letter US state abbreviation or the full US state name. We currently do not support international addresses.
Zip code must be 5 digits minimum and 10 digits maximum.
Supports semicolon separated list.
Phone numbers
Numbers must be minimum 10 digits, area code is required.
Supports semicolon separated list.
Custom Fields
Contact and Matter custom fields should not be added as custom columns to the contact and matter template files.
Please refer to the following articles to import custom fields: Contact Custom Fields and Matter Custom Fields
Time entry bill rate is limited to 5 decimal places, so round accordingly.
Invoice numbers must be integers and unique.
Payment numbers must be integers and unique.
Trust transaction numbers must be integers and unique.
All amount must be positive values for billing data: time entry amounts, invoice amounts, payment amounts, trust amounts.
Tags support comma separated list.
Validating Data
We recommend performing a pre-check of all template files before you attempt to import any data into ZenCase.
Verify that you are not missing any dependent data and the math on invoices and time entries add up correctly. By validating beforehand, you will save yourself a lot of trouble determining why the data is not matching up correctly in ZenCase.
Example Recommendations:
Validate all required fields are filled out in all template files.
Validate all dependent items in one template exists in another template.
For example:
Validate the client numbers in the matters template file exists in the contact template file.
Validate the invoice numbers in the invoices template file exists in the charges template file.
Validate the payment numbers in the payments template file and invoice numbers in the invoices template file exist in the payment line (aka payment allocation) template file.
Validate time entries math is correct. We must take discount amount into account if discounts are applied to time entries as well.
Verify the following:
(Adjusted Hours x Bill Rate) - Discount Amount = Total Amount
Validate the sum of all charges on an invoice equals the total invoice amount.
All line item charges on an invoice should sum to the invoice amount.
We do not allow negative amounts for charges, payments, invoices, nor trust transactions.
Negative charge amounts are not supported.
These can be handled with either applying a discount or importing as a payment credit and applying the payment to the invoice.
Negative payment amounts should be converted to positive values to allow import.
If payment are to be issues as credits, then create them as "Credit Memo" type of payment methods when importing payments.
Negative invoices amounts are not supported.
These can be handled with either applying a discount or importing as a payment credit and applying the payment to the invoice.
Negative trust transactions are not supported.
These can be handled by setting Trust Transaction Type to the reverse such as "Deposit" or "Withdraw" and converting the negative amount to a positive value to allow import.
Validate payment lines/allocations with any duplicate payment number AND invoice number combination with different amounts should be combined into a single unique payment number and invoice number combination with total amount.
ZenCase will auto allocate payments to invoices with payment allocation importer and requires a single unique payment/invoice number combination.
If possible, validate import data with calculated client A/R data and compare to the system A/R report to confirm they match up before import.
Working With Data In Excel
Microsoft Excel will always strip leading and trailing zeros if you open a CSV file in Excel. In order to maintain leading and trailing zeros in Excel, you must disable the option in Automatic Conversion Data settings.
In Windows, Go to "File" tab and click "Preferences".
Click the "Data" tab.
In the "Automatic Data Conversion" options, uncheck "Remove leading zeroes and convert to a number".
You can also choose to disable other options from this section as well.
In Mac, Go to "Excel" and click "Preferences".
In the pop up window, click "Edit".
In the "Automatic Data Conversion" options, uncheck "Remove leading zeroes and convert to a number".
You can also choose to disable other options from this section as well.
UTF-8 Support
You may encounter scenarios where you need to support special characters.
This is common for clients that have international contacts and use special characters in the Contact names.
Set Compatibility Level to 150 or Higher
Microsoft introduced UTF-8 support with SQL Server 2019, see more details here: https://techcommunity.microsoft.com/t5/sql-server-blog/introducing-utf-8-support-for-sql-server/ba-p/734928
You may need to update the Compatibility Level on your database by right-clicking and going to Properties, then go to the Options in the menu and set the Compatibility Level to SQL Server 2019 (150) or higher.
Collation
Using collation when you query will help ensure the special characters are encoded in UTF-8 so they can be carried over when you export the data to CSV for importing into ZenCase. ZenCase supports UTF-8 encoding in the import process.
SELECT [Matter_Name] COLLATE Latin1_General_100_CI_AI_SC_UTF8
FROM [dbo].[matter]
Save with Encoding
If you choose to save the results of the grid results in SSMS to file, then make sure you choose "Save with Encoding..." and CSV options.
You will be prompted to choose the encoding type, make sure to choose UTF8.
Alternatively, you can copy and paste the results in Excel after you change the Data Type to "Text" to ensure it saves the data properly. When saving from Excel to CSV, then make sure you choose "CSV UTF-8 (Comma delimited) (*.csv)" as the file type.
Validate UTF-8 with BOM Encoding
You can use a Text Editor (i.e., Notepad++) to open the CSV file and validate the special characters look correct before importing.
You can also validate the Encoding is UTF-8 BOM in Notepad++ by going to Encoding in the menu and making sure the UTF-8 BOM is selected.
Let us know if you have any questions!