Importing Batch Files

image_pdfimage_print

Collections MAX can batch import data from csv files. It is important, however,  to start the file as a Microsoft Excel file and then save it as a csv file.

There are a few steps that you must do in order to import your batch file into Collections MAX

  1. Format your Excel file and save it as a CSV file
  2. Open the CSV file in the Utilities / Import Mapping Utility inside of Collections MAX Administrator and map your column fields to the static fields in the database.
  3. At the conclusion of the Import Mapping Utility you will need to EXPORT a NEW CSV Data File that is created by the software by clicking the green Export as CSV Data File button.  The file is now in a format that Collections MAX can understand.
  4. You will need to go to Import Accounts/ Import Using CSV Data File and open the NEW file that you created in step 3 inside the Import Mapping Utility.  You will then click the TEST button to test the file and if the test doesn’t fail you will be able to import the accounts into the database.  Troubleshooting the Import screen will be mentioned later in this chapter.

NEVER OPEN ANY CSV FILE IN EXCEL UNLESS YOU ARE INSTRUCTED TO DO SO!

Excel will re-format all of your data and you will receive errors on import.

STEP 1 – FORMATTING YOUR EXCEL FILE

There are a few things that you must do before you save your Excel file to csv for it to be compatible with the software.

  1. Remove any non-alphanumeric characters from the top header row of each column.  If you have a column named First_Name for instance, change it to FirstName.
  2. Make sure there are no blank columns in between columns with data.  The parser is programmed to stop at the first blank column and much of your data will not be imported.
  3. Make sure that all of your dates are in mm/dd/yyyy format.  You may need to format the column in Excel.
  4. Check to make sure at the end of the file there is no extra lines of data for summing balance columns.  Remove these.

Save your file as a csv file in Excel.  Don’t include any non-alphanumeric characters in the name of the file.  Example:  If you named the file sep10.2010.portfolio.csv  the import will fail because the file name has periods (non-alphanumeric) characters in it.

After you saved your csv file, you can open it in our utility that converts your data into a format that Collections MAX can understand.  It is called the Import Mapping Utility.

NOTE: If your file requires mapping to custom fields not normally used by the software you will need to add them in Utilities / Custom Collector Screen before using the Import Mapping Utility.

STEP 2 – USE THE IMPORT MAPPING UTILITY

The Import Mapping Utility will help you batch import your collection accounts by producing a data import file for use in the Import screen.
This tool allows you to map column names from a csv file into the fields used by the database..

In Collections MAX Administrator click Utilities/Import Mapping Utility.

Click Open CSV Import file at the top.
Select your csv file that you would like to import.

A popup box will appear confirming the import file you selected, click OK.

Collections MAX will then fill in all of the columns of your csv file into the corresponding data fields of the database.  The bottom of the screen shows a data grid which represents the columns in your csv file.

Select the column that represents the data field in as many columns that you have data.  The custom screens will show the current label on each field.  After you have completed this for all tabs click on the Start Wizard button to start the Mapping utility wizard.

Click the NEXT button when you see the wizard logo.  If you did not map a filenumber in the filenumber field you will be prompted to do so.  If you want filenumbers that begin with a letter such as A123 then type in A in the alpha box and 123 in the number box.  Collections MAX will increment each number by one so you will end up with A123, A124, A125 etc….   If any duplicate filenumbers are found, you will be prompted to select a different filenumber.  Click the NEXT button.

The next screen will show some static information about the file that Collections MAX need to know.

Make sure all fields are filled out in this screen with the exception of the salesman if you do not need to credit salespeople.  Click the NEXT button.

Collections MAX will begin merging data from your csv file into a built in preformatted file.  It will then begin to format your data.  Any errors found such as missing fields or columns that wasn’t imported will be displayed in the error box.  Please take note of any errors.



STEP 3 – EXPORT YOUR NEW CSV DATA FILE
If everything looks good then click the Export as CSV Data File for use in the Import screen in the next step, otherwise you will need to fix your original Excel file, convert it to CSV again and start the process over.




You may choose to Export your data file in Hyper Mode.  Hyper Mode creates the data file using a multi threaded routine that cuts down your file creation time for large files.  Use this option when importing 100 accounts or more.  By Exporting a new CSV Data File, you have just created a file that the software can understand when you import your accounts in Step 3 of the process.


SCHEMAS

Schemas are text files that you can use if the format of your csv file rarely changes.  This function will automatically select the drop down boxes for you instead of you needing to manually select them.

To use the schema function you must first successfully map a file.  After you export your file and close the Format box you will see the Save Schema button.


Click the Save Schema button to save your schema.  When you want to load the schema for the next time you use the import mapping utility, click the Load schema button after you have loaded your csv file.  Any errors that the utility finds will be highlighted in RED for you to correct before starting the wizard.


STEP 4 – OPEN YOUR NEW CSV FILE IN THE IMPORT SCREEN

Now that you have created your CSV file in the Import Mapping Utility, you will need to close out the Import Mapping Utility and the Utilities section of Collections MAX Administrator so you only see the control panel.  Click on the Import Accounts button and then select the Import using CSV Data File option.
Your screen will look like the screen below.

From here you will press the Open CSV Data File button and select the file that you created in the Import Mapping Utility.  DO NOT OPEN THE ORIGINAL CSV FILE YOU SAVED FROM EXCEL OR YOU WILL GET AN INVALID FORMAT ERROR!

Click the TEST button on the import screen. If you receive an error that the test failed.  Do not worry.

Collections MAX will highlight the column that contains the infraction and give you a description of the problem.  If the problem is simply that the field is missing, then find the corresponding field on the Pages 1-5 tabs, insert data into the corresponding text box and click the INSERT button next to it.  This will fill the columns with the missing data.  You need to press the TEST button again to start the testing process.

When you receive a popup that says “TEST PASSED!  ACCOUNTS READY FOR IMPORT!” then you are ready to import the accounts into the system.  To do that, click the red IMPORT TO DATABASE button.
Collections MAX will then re-test to insure that nothing was changed.  After the test, you will see the progress bar start moving.  Your accounts are being imported.

Step # 2
You will see a screen that will add all of the phone numbers and addresses to the corresponding address and phone logs.  Click OK to process this.

Step # 3:  This step will scan the database and link debtors that have duplicate accounts in the system.  Each box corresponds a point system and will link accounts together that score higher when the minimum points to mark as duplicate box has been exceeded. Click OK to start the utility.

Step # 4  This step let’s you notate the collectors log to give direction or say something important regarding these accounts.  Press OK when finished or you can simply press Cancel if you don’t want to leave any notes. Once this is completed, you can add more comments or simply click the close button.  Your accounts have successfully been imported to Collections MAX!

If you didn’t assign the accounts to your collectors on import you will now need to assign the accounts so the collectors can work them.  Please see our Assigning work section of Collections MAX Administrator.

NOTE FOR WEB CONNECT USERS
When importing accounts it is best to create your CSV data file on your local machine but instead of opening the file in the Import screen on your local machine you should send the file to yourself in an online email account and then RDP (Remote Desktop) directly to the remote server.  From there you should download your CSV data file and open it in Collections MAX Administrator locally.  This will result in a 50X speed improvement in your import speed because it will eliminate all latency from the server and internet connection.

Import Screen Functions
You will notice that there are six tabbed mini screens on the bottom half of the Import screen.  These tabs are used to assist you in populating your import file by having the ability to pre-fill out account information.



Format Page1

File Number: This function will allow you to assign file numbers or file id’s for your Collections MAX accounts.  No two file numbers can be identical in the system or Collections MAX will notify you of the error when you try to import.  To use this you can put a group of letters in the Alpha box (maybe something that helps you identify a client) and a starting number in the number box. When you click the insert button the Alpha and Number boxes will combine and increment by one for the entire record.  So for example say that you put in NTL in your Alpha box and 200 for your number box.  Your first records will be file number NTL200, NTL201, NTL202 and so on.

Assigned To + Collector Name: On the initial import Collections MAX requires accounts to be assigned.  You can re-assign accounts after import in the Assigning Work section.
To use this you will simply select the collector that you want to assign the account to and enter a number of accounts that you want assigned in the amount box.  Then click insert.  If you had 100 accounts and 5 collectors you would likely assign 20 for each and select five different collectors.
This is also important if you want to include collector names on dunning letters.

Full Name: If you have columns of first,middle, and last names then this will combine the two to create the full name column when you hit this insert button.
If you have a only a full name column and you want to extract the first, middle, and last names then click Format on the top menu and select Split name from full name.

Last Worked: The last worked field shows the collectors how long it has been since the account was worked to prevent accounts from being worked too often.  If you plan to work these accounts immediately then set this for five days behind the current date.  If you want to wait a few days then set this to the current date.

Original Creditor: If you are importing a batch of accounts with the same original creditor you can type it in the box here and have it populate for you when you hit the insert button.

Type Of Debt: This tells the collector what type of debt he is collecting.  It can be student loans, credit cards, payday loans, contracts, etc.  Enter the type of debt if all are going to be the same.

Original Balance: This is the original starting balance on the account.  Click the insert button to copy the current balance to the original balance field.

Paid to Date: Total amount paid to agency from account.  The insert button sets this to the default of 0.00

CoDebtor1 Full name: Combines CoDebtor1 First Name, CoDebtor1 Middlename, and CoDebtor1 Last name columns when insert is clicked.

CoDebtor2Full name: Combines CoDebtor2First Name, CoDebtor2Middlename, and CoDebtor2Last name columns when insert is clicked.

Date Placed: Date that account was placed with agency.

Order By Date: Date that account was last worked.  This date is used to shuffle accounts around in Collections MAX Collector to insure that the oldest accounts are worked first.  If you are working the accounts immediately set this date for five days behind current date.  If you are going to work these accounts in a few days then set this to the current date.




Page 2


Client + Portfolio: Before you import accounts you should have set up your client and created a portfolio for that client.  A portfolio is a group of accounts that you can track separate from other accounts.  To add your client and portfolio simply highlight the client and portfolio in the data grid and click the insert button.  It is recommended to only do this here inside Collections MAX to prevent any spelling errors that may lead to the system not being able to locate portfolios.

Status Name, Status Type, Status Color: This is the default status that you will import the account under.  There is a button inside collections MAX Collector that is reserved for new accounts and will show if the status is called NEWBIZ.  Select the status that you want to drop as the initial default status by selecting it in the data grid and click the insert button.




Page 3


Contingency Amount: This is the rate that your agency will get on each account.  For example, let’s say that these accounts pay a 33% contingency rate.  Simply enter 33 in the box (without the percent ‘%’ sign)  and click insert.  If the accounts pay 33.25% then enter 33.25 and click insert. You can also have different rates on a per account basis if you entered them in when you created your import template or directly through Collections MAX by entering it in the data grid.


Salesman: Click the enable button and select your salesman from the drop down list if he is being paid commission on these accounts.  Click the insert button to populate the accounts.


Salesman Percentage: If the salesman is receiving commission then enter the percentage here (without the percent ‘%’ sign).  The salesman report will use this field to calculate both fee and gross dollars.  Click the insert button to populate the accounts.


Customer Type: Select Individual if accounts are from individual people or select Business if they are commercial accounts.  Click the insert button to populate the accounts.


Authorized User:  If the debtor is the same person who was the authorized user than click the insert button to populate this field as the debtor name.


Make Payable To: There are 5 make payable to fields (name, address, city, state, zip) that you can use if you want your letters to reflect differently whom you want funds to be payable to.
Select the yellow select button and if you do not have a make payable to name stored you can do so with the green add button.  When you have selected a make payable to name click the OK button and the fields will populate into the text boxes.  Click the insert button one at a time to fill in your template.

Date Received: The date that the accounts was received by your agency.  click the insert button to populate the accounts.


Statute Of Limitations Date: This is the date that the statute of limitations expires.  This function will automatically calculate the statute of limitations date if an account has a state and a Date Of Charge Off.


Page 4


Charged Off Principal: This will populate the charged off principal column with the current balance if the insert button is clicked.


Principal: This will populate the principal column with the current balance if the insert button is clicked.


Fees: This function lets you calculate fees that are added to the original and current balance (you can add just to the current balance if check box is checked. You can select the fee type that you want to add to in the select fee drop down box.

Interest Percentage: This function will allow you to insert a set APR (Annual Percentage Rate) that Collections MAX uses to calculate interest if all will be the same.  Only use numbers i.e. for 5% just enter a 5 in the box.

Calculate Interest: You can also specify on a per account basis if you want Collections MAX to calculate interest.   YES indicates that interet will be calculated.  NO indicates that interest will not be calculated.

Combine accrued interest and current balance: This function adds accrued interest to your current balance.


Page 5

Names and Addresses: This function changes all name fields and addresses to Uppercase. This is a recommended action for the Collections MAX system.

Show Open CSV File Load Errors: If this box is checked BEFORE opening the csv file you will be able to see a verbose description of any errors when the file is loaded.

Generate Pure text schema.ini on file load: If this box is checked BEFORE opening the csv file the system will not format the data on import.

Tests: If any of the tests (Social Security Number, Phone Number, Duplicate Account Number, Duplicate File Number) are checked before testing the file they will be bypassed.

Format: If the Social Security Number or Phone Number option is checked then those fields will not be formatted by the system.  This is useful for international users.

Test Mode: If the default fast mode is selected the tests will commence without the screen rolling which increases the test speed because the screen does not need to refresh.  If the Regular mode is selected then the screen will “roll” which is slower but prevents the screen from freezing up during testing.

Comments are closed.