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".
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 import the CSV data in Excel and set the data columns as "Text" type instead of "General".
Click the "Data" tab in the ribbon
Click "Get Data" button
On Windows, select "Legacy Wizards"
To enable legacy wizards, go to File > Options (at bottom)
When pop up window opens, select Data tab in left sidebar.
Under "Show legacy data import wizards", select "From Text (Legacy)".
On Mac, select the down arrow icon next to the "Get Data" button
Then select "From Text (Legacy)
Choose file from file pop up dialog and click "Import"
Select "Delimited" under Original data type section
Click "Next"
Under Delimiters, uncheck all selected and only check "Comma"
Text qualifier should be double quote icon.
Click "Next"
At bottom "Data preview" panel, highlight first column, scroll all the way to the right, and highlight last column.
Under "Column data format", select "Text" to force all data to be text data type.
Click "Finish"
Choose worksheet if needed
Click "Ok"
You data should now be successfully imported as "Text" for all columns.
This will ensure leading and trailing zeros remain intact.
Let us know if you have any questions!