The Pre-Import Checklist gives you steps on cleaning up your import and/or update files so SimplicityCollect can read them properly. This walkthrough will discuss each item in more detail, and some will give examples.
1) FILE TYPE: Must be an Excel (.xls or .xlsx) OR Comma Separated Value (.csv) OR Fixed Width file.
2) EXCEL SHEET NAME: With .xlsx or .xls files, the sheet name (located in the bottom left hand corner) must be spelled exactly as: “Sheet1” (without quotes). Only the sheet named Sheet1 can be imported. Files that are .csv are exempt from this step.
The reason this is needed is because only the first sheet (Sheet1) can be imported. Files that are .xlsx or .xls have the capability of containing multiple sheets; if your file has data on these other sheets that you need, we recommend either 1) consolidating them into the first sheet or 2) moving them to a separate document that can be used to update the accounts later.
Files that are the .csv type are only one sheet, and do not have the capability to save multiple sheets. They also re-name “Sheet1” to the name of the file automatically. Because of these reasons, you do not need to worry about changing the sheet name for these types of files.
3) REQUIRED COLUMNS: Your spreadsheet must contain columns that have data which corresponds with: (1) “Client Name”, (2) “Debtor First Name” & (3) “Debtor Last Name” for CONSUMER accounts OR (1) “Client Name”, (2) “Debtor Company Name” for COMMERCIAL accounts.
If you forget to add these columns to your spreadsheet, you won’t be able to save your mapping.
4) For consumer accounts the debtor’s first and last name must be in two separate fields (E.x. “Debtor First Name” and “Debtor Last Name”).
5) Make sure every column you plan on mapping has correct data in each row. Delete columns which are blank and/or not relevant to your business. For columns you do keep and wish to map, verify that the data is present and accurate.
6) IMPORTING ACCOUNTS TO PRE-EXISTING CLIENTS: In order to import accounts to a pre-existing client, the name in the file must be identical to the client’s “Short Name” in Simplicity. It is case sensitive. (“Short Name” can be found by clicking Settings > Clients and choosing the desired client from the list on the left.)
If the client does not exist in Simplicity yet, they will be automatically created during import. However, setting them up beforehand gives you several advantages, including but not limited to:
- Specifying the commission rates between you and the client
- Setting up allocation/disbursement settings
- Setting automatic Account Number formatting
When these variables are filled in for a client beforehand, they will also apply to all of the accounts being imported from that moment on.
7) EXTRANEOUS SYMBOLS: We recommend searching for and removing certain special characters from your entire file prior to import, such as: less than (<) and greater than (>) symbols, as well as percentage (%) symbols from interest rates. We also recommend removing carriage returns.
You can use Microsoft Excel’s Find and Replace feature (CTRL+H or COMMAND+H) to remove these symbols.
To remove carriage returns, first open the Find and Replace box. In the pop-up window, click in the Find box and use the key command CTRL+J (COMMAND+J) to place the carriage return “symbol” (it will look like a small period in the box). In the Replace box, put what character you want to exchange it with, or leave it blank to remove them completely. Then click Replace All.
NOTE: Sometimes after using Find and Replace to remove carriage returns, the Find and/or Find and Replace functionality may become unusable and will not find other search criteria in the file. If this happens, save your changes and close Microsoft Excel, then re-open the file.
8) If there is a column for the debtor’s State, make sure it is formatted as a correct 2 digit state abbreviation.
Make sure that states are not spelled out in full, and that any placeholders are removed (such as ZZ, XX, 0 and NULL).
9) If there is a column for the debtor’s Social Security Number (SSN) double-check to make sure there are no “placeholder” numbers (e.g. 000-00-0000). It is very important to remove these, otherwise you may lose debtor information.
Some features in Simplicity require an accurate SSN to use, such as the Debtor Payment Portal and Credit Bureau Reporting. If the SSN information is not included in your file, consider looking into obtaining it.
10) CALCULATING INTEREST: To calculate interest, the spreadsheet must contain ALL THREE of the following data: Claim Amount, Interest Rate, and Claim Interest Start Date. Every cell in each of those three columns must have relevant data. If no value is present (for example, accounts with no interest), you can fill the cell with the value “0”.
11) CALCULATING INTEREST (Cont.): When importing an Interest Rate, keep the number as a whole value and remove the percentage (%) symbol. For example, 15.25% should be written as: 15.25 (not 0.1525).
12) ACCOUNT NUMBERS: If you import an Account Number column, it MUST contain unique values for each account. If you do not have an Account Number column to map (or choose not to map it), an Account Number will be automatically generated for each account. (These auto-generated Account Numbers are formatted in a style unique to each client. To check or change the format, please go to Settings > Clients).
The default auto-generated Account Number format is: YYYY-0000 (the four digit number at the end automatically increases to the next value for each account, starting with -0001).
13) CUSTOM FIELDS: You can first review a list of our stock fields by going to Help > Importer Help. For any columns you wish to import that do not have a stock field they can be mapped to, you can create a custom field for it prior to import. This is done in Settings > Custom Fields Admin.
Common custom fields we see created include (but are not limited to):
- TLO/Skip Trace Fields
- Driver’s License Number/State Information
- Relatives or Reference Names and/or Phone Numbers
- Pay Frequency
- Agreement Details and more
If you find you are adding a large number of custom fields, you can organize them into groups (Sub-Tab Groups) to keep the account screen clean and organized.
14) DATE FORMAT: All dates MUST be in a short date format (e.g. 12/31/2010) for Simplicity to recognize them. Partial, incomplete, alternative format and placeholder dates need to be fixed or removed prior to import.
Examples of dates that will not be importable include ones that have:
- Alphanumeric information (xx/xx/2001)
- Partial dates (01/20)
- Alternative format dates (Feb. 1st, 2001)
- Reverse order dates (2001/15/02) – NOTE: These can be reordered in Excel by going to Data > Text to Columns, and on step 3 choosing the Date format option.
15) COLUMN HEADERS: Each column header in your spreadsheet must be unique. You cannot import a spreadsheet with duplicate headers. For example, multiple columns named “Phone” should be renamed with unique values such as “Phone 1” and “Phone 2”.
16) SECURITY LIMIT: We recommend breaking up very large files into smaller batches for import. This helps speed up the importing, and allows your employees to begin working accounts sooner. Files larger than 20,000 accounts have a chance of timing out if they exceed the server security limit.
The exact “maximum” number of accounts per file can vary, as many variables can affect the size of file (for example, there are often size differences between file types like .xls, .xlsx, and .csv). We recommend first importing a small number of accounts, perhaps 50-100, to test your mapping and make sure everything is working fine.
After that, the remaining accounts can be split up either manually or using a tool such as an online (or downloaded) tool for file splitting. You want to make sure that the headers also carry over to each file, and that they all match one another.
NOTE: If, after an import or update, you find that the file only partially succeeded and you received a “Timed Out” error on the remaining accounts, you can either a) rollback/redo the import or update completely with smaller files, or b) copy the “Timed Out” accounts to a new spreadsheet to import/update separately.
17) ONLY AFTER working carefully through this checklist, you are ready to import your file.