Collect! Credit and Collection Software™

  Page Table of Contents Related 'How To' Tutorials

How To Create An Import Map

Collect!'s import module is a very powerful tool for importing data into Collect! from other databases, instantly giving you all that valuable information at your fingertips within the Collect! program. This process requires an import map. To get you started, this document steps through the creation of a simple single record import map. It is necessary that you are familiar with the use of Collect!'s Client and Debtor screens.

Collect! can import a variety of file formats, as long as they are saved in an ASCII text file. The following formats are the most common:

  • Delimited
  • Fixed Length

Setup A Client

If your importing Debtor accounts, then you need to create a Client first, if one does not already exist. If you are not importing accounts, then proceed to the next section.

Please refer to the Help topic How To Enter A Client for more information on adding a client.

If the Client already exists, and has accounts on it, we recommend creating a TEST Client that mirrors the settings of the existing Client. You can do this in your production or test database. Once testing is complete, you can delete the TEST Client.

Top of page.

Prepare The Data File

Before doing anything, make a copy of the data file. ASCII files can be opened in MS Excel, but Excel will alter the data, so you want an untouched copy of the file first.

CSV files can be opened directly with MS Excel. Other delimters and Fixed Length Files need to be imported. Please refer to Microsoft Excel documentation on how to import Text files.

Once you have the COPY of the file open in Excel, insert 2 rows at the top of the file. In row 1, you are going to track the name of the record/form that the data will be stored in (EX: Debtor, Cosigner, Debtor Detail, etc.). In row 2, you are going to track the name of the field that the data will be stored in (EX: Name, Address, etc.).

Save the copy with your notes. Open the original file with a TEXT editor. We recommend Notepad++.

Make note of any columns with the following:

  • Leading zeros
  • Numbers longer than 15 characters
  • Date formats

    • Useful Note Typically files from the same source will have the same format for all dates, but don't rely on this. Check all date columns for consistency.

  • Phone number formats
  • SSN number formats

Top of page.

Delimited File Format

Delimited Files are file types where each record of data is on its own row. Each field of data is separated by a delimiter. The most common delimiter is comma (CSV), but Collect! supports other delimiters like Pipe or Tab.

Example Delimited File

Name,Address,City,State,Zip,Balance "Doe, John","123 Main Street",Los Angeles,CA,90210,"15,000.00"


CSV Sample File Viewed In Notepad


CSV Sample File Viewed In MS Excel

Dynamic Field Indexing

When importing a delimited file, you are simply creating Import Field Specifications without referencing an Offsets or Lengths, as you would with Fixed Length.

Fields can be created with or without the use of Dynamic Field Indexing.

Without Dynamic Indexing: Each field is assumed to represent a column. The first field will be column A, the second field will be column B, and so on. The issue with this design is if you are skipping a column, you need to create blank field references. If you are importing multiple record types (EX: Cosigners), you need to load the blank fields for any columns that don't belong on the second record type (EX: all the Debtor fields). Another issue with this method is you cannot reference the same column multiple times for the same record (EX: Transaction Payment Date and Posted Date both needing to reference the date in column O).

With Dynamic Indexing: You can specify the column in the data file that you want to use. This method is the preferred method as it doesn't require blank Field Specification records and you can reuse columns for different fields.

Top of page.

Fixed Length Format

Fixed Length Files are file types that don't use delimiters to separate data. Each piece of information is stored in the same location for each record. There are 2 types of Fixed Length Files:

  • Each record is on its own line. Each field is stored in specific offsets and lengths.
  • Each record is multiple lines with a record identifier. Each field is stored in specific line numbers from the Record Identifier at specific offsets and lengths.

Example Fixed Length File - Single Line

Doe, John 123 Main Street Los Angeles CA 90210 15000.00 Smith, Jack 12 5th Avenue Los Angeles CA 90211 6000.00

Example Fixed Length File - Multiple Line

RECORD Doe, John 123 Main Street, CA 90210 15000.00 RECORD Smith, Jack 12 5th Avenue, CA 90211 6000.00


Fixed Length Invoice Text File

Below the identifier *****, there are several lines of information.

  • Each piece of data is located at a specific character position on one of these lines. This position is called an Offset and is a key in determining where the piece of data begins on the specific line within the block of data.
  • Each piece of data has a character length called Length which is the number of character spaces allotted to the piece of data in the block of text.
  • Each specific line of data has a value called Lines After Identifier which is the line number where the piece of data is located.


Field Specifications

The import map that we will create will state the line, length and position of each piece of data that is going to be pulled into the database from the fixed length text file. There will be no blank Import Field Specification records. As the data uses offsets and lengths, the same data can be used multiple times.

Top of page.

Setting Date Formats

You must always make sure that the Date Options are set correctly when importing dates.

Date Formats must be set prior to importing, as these have a bearing on how the Date information is formatted. As mentioned above, you want to set the global format for the Import Date Options, then, if needed, set the Import Field Date Format.


Date Options Settings

Collect! supports:

  • Month Day Year
  • Year Month Day
  • Day Month Year
  • Year, then Number of Days into the Year (1-366)
  • Number of Days into the Current Year (1-366)
  • Day Month Year, where the Month is the 3 Letter abbreviation (EX: JAN)

Once selected, set whether the Day and Month have leading zeros. Then select whether the year us 2 or 4 digits. Finally, set the date separator.

Top of page.

Importing Notes

When importing notes, you must build a proper date/time stamp. The proper format is:

DATE TIME STATUS OPERATOR : TEXT (Example: 01/01/2029 15:30 NEW OWN : Review New Account )

For detailed instructions on how to import a note line, please refer to the TEXT CONCATENATION WITH APPENDING section below.

Useful Note The status is optional when importing Client notes.

You can build the date/time stamp by putting of the elements into the import map using the Text field and 'append to existing data.' If the date/time is in the spreadsheet, then you can reference the spreadsheet when building the map.

Useful Note The notes field is considered a text field, so special steps are required to indicate that you are importing a date into the notes. On the Import Field Specification form, click the Date Options buttons to open the date options for this field. Set the date format to match the format in the data file.


Importing Full Note Line

Alternatively, if the information is not available and you want to put in the current date, time, status, and operator, then all you have to import is just the text. In the event that you only import the note text, or if you don't build a proper date/time stamp, Collect! will not automatically create the date/time stamp, so the test will be appended to the bottom of the note file.


Importing Partial Note Line

Multi-Line Notes

In order to import notes that have multiple lines, you can place @x0A (case sensitive) in the following places:

  • Default Value
  • Fill Value
  • Text File being Imported

Collect! will replace the @x0A string with proper carraige returns in the database.

Useful Note This only works with in a single Import Record Definition for a note line. If you have multiple import record definitions, they will be treated as separate entries in the database (same behaviour as contacts, transactions, etc.).

Top of page.

Importing Financials

Usually money amounts already exist with their decimal placements, as you will see in our sample. If they do not, you would set a value using the Multiple by field on the Import Field Specification for the money field.

When importing money amounts, we have to be careful of where we are placing them in Collect!. Values in certain fields may affect other data fields. For this sample, we are using Principal. You may think that it is correct to choose Principal from the Field Type list to put this value in the Principal field on the Debtor form. This is correct in a sense. However, Collect! actually gets the Principal amount from the 196 transaction on the Transactions tab.

Method 1: Automatically Create Transactions

This should be used if you only have 1 Principal balance to load for an account. This method will automatically create the applicable transactions after the Debtor is created. It is the same behavior as creating an account manually.

  1. In the Debtor Import Record Definition, add Fields for importing the Original Principal, Original Interest, Fees, etc.
  2. On the Import Options screen, check the boxes for Apply Client Settings and Include Transaction Posting.

Method 2: Manually Create Transactions

This method is for importing individual transactions yourself. This gives you more control over the process, but add steps.

  1. Click OK to go to the list of Import Record Definitions. Create a new record definition and select Transaction from the list in the Record field.
  2. Create a new Field and leave the field blank. Put 'A - Account Number' into the note field.

    3. Useful Note Reminder, with Dynamic Indexing, blank fields are not required.

  3. Create a new Field and select TO US from the field list.

    4. Useful Note As the values in the to us field on transactions are negative for transactions that should increase the balance, you need to put a value of -1.0 in the multiply by field to convert the principal into a negative number.

  4. If your *.csv file had the decimal stripped from the money amount, you would also fill in 0.01 in the field labeled Multiply By. However, this is not needed in this exercise.
  5. Create and new field and leave the field blank and put '196' into either the Default Value or Fill Value fields.

    6. Useful Note By selecting "use transaction type - after" in the import options, Collect! will automatically fill in the rest of the transaction details like the description and financial type. There is a time and place to use either the transaction type - before or transaction type - after. Please press F1 on those fields for more information.

  6. You should now have a field in the map for each field/column in MS Excel, in the same order.

Useful Note To make sure you have all the fields, you can select the << or >> button to go forward or back through fields, or select the OK button to view the Import Field Specification list. Use your Up/Down arrow keys to scroll through this list.

In addition to these fields, we will set a few default values in our import map.

Top of page.

Set Commission Rates And Tax During Import

Typically, you would use the Apply Client Settings box on the Import Options form to automatically load the Commissions and Taxes, but in some cases, you may need to set specific values.

Several hidden fields on the Client, Debtor and Transaction forms enable you to access tax and commission rate fields directly within the import field specifications. This enables you to easily set commission and tax percentages and their breakdown settings during an import.

Commission Rate Field Names for Client

The field definitions for the fields on the Client's Commission Rates form are:

Commission Rate
Commission Rate 1
Commission Rate 2
Commission Rate 3

The field definition for the commission breakdown check boxes is:

Comm Rate Options

The Comm Rate Option value is actually a combination of bit values for all the check boxes on the Commission Rates form. It is easy to figure out the value and then set it in the Default for the field specification. How to find this value is described later in this document.

Commission Rate Field Names for Debtor

The field definitions for the fields on the Debtor's Commission Rates form are:

Commission Rate 1
Commission Rate 2
Commission Rate 3
Commission Rate 4

The field definition for the commission breakdown check boxes is:

Comm Rate Option

Commission Rate Field Names for Transaction

The field definitions for the fields on the Transaction's Commission Rates form are:

Commission Rate
Commission Rate 1
Commission Rate 2
Commission Rate 3

The field definition for the commission breakdown check boxes is:

Comm Rate Option

Tax Rate Field Names for Client

The field definitions for the fields on the Client's Tax form are:

Tax Rate
Tax Rate 1
Tax Rate 2
Tax Rate 3

The field definition for the tax breakdown check boxes is:

Tax Rate Options

The Tax Rate Options value is actually a combination of bit values for all the check boxes on the Tax form. It is easy to figure out the value and then set it in the Default for the field specification. How to find this value is described later in this document.

Tax Rate Field Names for Debtor

The field definitions for the fields on the Debtor's Tax form are:

Tax Rate 1
Tax Rate 2
Tax Rate 3
Tax Rate 4

The field definition for the tax breakdown check boxes is:

Tax Rate Option

Tax Rate Field Names for Transaction

The field definitions for the fields on the Transaction's Tax form are:

Tax Rate
Tax Rate 1
Tax Rate 2
Tax Rate 3

The field definition for the tax breakdown check boxes is:

Tax Rate Options

Accessing the Bit Values for Check Boxes

Before you can set the value for Commission or Tax Rate Options, you need to obtain the bit values for the options. Once you know what the values are, you can just use this value as the Default in the import field specification for the Comm Rate Option or the Tax Rate Option.

To determine the value that sets the switches you need, go to one debtor and set up the Commission Rates and Tax exactly as you want them to be, switching ON check boxes with a check mark as needed. Then, go off the Debtor form to commit the settings. Then print to screen @de.cro and @de.tro. This will give you two numbers, for example. 3843 and 290 or 16386 and 258. The numbers won't seem to make any logical sense, but actually they are a combination of switch values and Collect! knows exactly what they mean. In your import field specifications, you can simply put these values in to the Default field and Collect! will set the switches when the import runs.

You can do the same for the Client Comm Rate Options and Tax Rate Options, and the Transaction Comm Rate and Tax Rate Options. If you are setting the same values at the Client or the Transaction level, you can use the same values that you obtained above. They are the same behind the scenes. The same number sets the same switches in each case.

Top of page.

File Format Specification Form

An Import Map ( File Format Specification) has 3 levels:

  1. File Format Specification: Global Settings for the Map
  2. Import Record Definition: Record that is being imported (EX: Debtor, Cosigner, Attachment, etc.)
  3. Import Field Specification: Field data that is being imported (EX: Name, Address, etc.)

Here are the related help pages for more information on each form.

Click Here to View this Form.

Top of page.

Import Options Form

Click Here to View this Form.

Top of page.

Import Date Options Form

Click Here to View this Form.

Top of page.

Import Record Definition Form

Click Here to View this Form.

Top of page.

Import Field Specification Form

Click Here to View this Form.

Top of page.

Import Field Options Form

Click Here to View this Form.

Top of page.

Creating The Import Map - File Format Specification

  1. Sign into Collect! and stop at the Main Menu. Select File from the top menu bar and a drop-down list of choices will display. Select Import/Export from the list. A sub-menu of choices will be displayed.
  2. From the Import/Export sub-menu, select Customize Import/Export and then select Edit File Format. This will display a list of import map examples that are already in your database.
  3. Press F3 or select the NEW button to open a new File Format Specification form to use for our import map.
  4. Fill in the Name field.
  5. The File field is optional. If the file will always be in the same location with the same filename, you can fill in the path and name here; otherwise, leave blank.
  6. The Note field is optional and for you reference only.
  7. Select the Fixed Length, Comma Separated, or Other radio, depending on your data file.

    8. Useful Note Collect! supports any plain text ASCII file, so if you need to import a file with another delimiter (; | {TAB}), select the 'Other' radio button and fill in the HEX codes for the field delimiter. You will also have to fill in the record delimiter (line feed, carriage return, etc.).

  8. If your file has multi-lines notes that use use Line Feeds, not Carriage Returns, then check the CRLF Line Endings box; otherwise, leave unchecked.
  9. Select the Import Options button to display the screen control panel where you may make selections for preferences on how you want the import to pre-treat or post-treat the incoming data. Key items to select for standard Debtor Imports:
    • Client/Number: if this map is for a specific Client
    • Apply Client Settings: if you would like the commission and tax information copied to the Debtors
    • Include Transaction Posting: if you are not importing the Transactions as separate Import Record Definitions and would like the applicable Transactions created automatically
    • First Line Contains Labels: if there is a header row in the data file
    • Last Line Contains Footer: if there is a footer row in the data file
    • Recalculate Accounts: if you are importing interest bearing accounts
    • Manage Promises: if you are importing payments
    • Use Transaction Type - After: if you are importing Transactions and want the default information from the Transaction Type record to be populated on the Transaction
    • Auto-Create Phone, Email, and Address Records: if you want to automatically create the sub-records for these record types


    10. Import Options Dialog

  10. Select the OK button to close the form and return to the File Format Specification form.


    11. File Format Specification Form

  11. Click the Date Format Options button. Select the default date format and separator. Please refer to the SETTING DATE FORMATS section above. Click OK.
  12. Click your mouse in the Import Record Definition sub-form to activate it.
  13. Click NEW to create a new record.

Top of page.

Creating The Import Map - Import Record Definition

Record Hierarchy

In a Collect! database, records have "owners." This is very important to consider when you are creating an import routine. Owners are imported before the records that they own. For instance, Debtors are owned by Clients, so you must create or import a client first. Transactions and Cosigners are owned by Debtors, so the Debtors must be imported first.

Fill in the Record

  1. Select the Down arrow next to the field labeled Record. You will see the list of all types of records that you can import using the import module.
  2. Choose the first record that you need to import. In most cases this will be Debtor.


    3. Record Definition Form

  3. Fixed Length Files:
    • Identifier: Enter the Record Identifier (EX: RECORD or ***** in the examples above)
    • Offset: If the Identifier is not on the left margin of the file, enter the number of characters from the left margin where the Identifier starts. Blank means 0.
    • The remaining fields and the Options button are too complex for this tutorial. Please contact Support for more information.
  4. Click your mouse in the Import Field Specification sub-form to activate it.
  5. Click NEW to create a new record.

Top of page.

Creating The Import Map - Import Field Specification

We are going to import information into fields in the applicable form. We will need a Field Specification for each field.

  1. Select the Down arrow next to the field labeled Field. This will display a list of all the fields on the record's form that you could include in your import map. Scroll through the Field Type list and select the field for column A (or the first segment if fixed length).

    2. Warning Note WARNING: The list of fields will display all fields on the record AND related records. For example, the Debtor record will also display fields for Debtor Detail, Misc, Principal Detail, Financial Summary Totals, and more. Pay special attention to the value in the Form Name column. Some fields are in the list multiple times like Name, which appears on the Debtor form and the Group Member Setup form twice. If you are unsure on which one you should select, go into Collect!, click in to the field that you want to load the data in to, select the System menu, go to Layout and Languages, and click Form Properties. This will tell you the Form Name. You should also select Field Properties under Layout and Languages. This will tell you the actual Field Name without any Aliases.


    Field Specifications List

  2. Note:
    • Delimited Format without Dynamic Indexing: What you enter here does not get imported. It is for your reference only. We typically recommend putting in the column letter to make it easier to read.
    • Delimited Format with Dynamic Indexing: Enter the @ symbol followed by the column letter or number. After that, you can add a # symbol with a comment following it. This helps to make the file readable.

      • Useful Note For Delimited files, Dynamic Indexing is the preferred method (EX: @A #Account Number). Collect! also support the numeric value (EX: @0 #Account Number).


      Dynamic CSV Import Indexing

      Useful Note @[space] does not result in any output but allows the user to enter static data prior to the data being imported.


      Inserting Static Data Prior to Column Data

      Useful Note The @[>] symbol instructs the CSV parser to advance to the next CSV column after processing this Dynamic CSV field. You would need to use this if you are placing static text in the middle of an import, such as Debtor Mode or Address OK.


      Forcing Collect! to Read the Next CSV Column

    • Fixed Length: What you enter here does not get imported. It is for your reference only.
  3. Offset: This only applies to Fixed Length files. Enter the number of characters from the left margin of the file where the data starts. For example, 0 (or blank) means that the data segment starts on the left side of the file. 7 means that there are 7 characters to the left of the segment.
  4. Length: This only applies to Fixed Length files. Enter the number of characters that the segment is in length. Form example, 40 means that there can be up to 40 characters in the data segment. Collect! will not import any white-space after the end of the last text character.


    5. Debtor Acct Field Specification

  5. Lines After Identifier: This only applies to multi-line Fixed Length files. Enter the number of lines after the Identifier specified on the Import Record Definition form.
  6. For the remaining fields, please refer to the Import Field Specification form section above. Some key fields to be aware of:
    • Use Key to Find Existing: This section of fields can be used if you are expecting accounts to already be in your database. For example, skip trace return files, payment imports, or dialer results.
    • Preserve Existing Data: Don't overwrite data in the field, if it is already populated.
    • Disable Log to Notes: If you are overwriting existing data in the field, you can disable the Log to Notes functionality.
    • Append to Existing Data: This section of fields can be used to concatenate multiple columns. For example, Last Name in column B, then a comma, then a space, then First Name in column C.
    • Change Case: This radio selection allows you to indicate the case for text.
    • Multiply By: If you are importing Financials and you need to do math on the data, you can enter a multiplication value here. For example, Principal and other charges need to be entered in Transactions as negative value, so you can enter -1.000. If your data file has an interest rate as a decimal (EX: 0.1255), you can enter 100 to convert it into a rate that Collect! will recognize (EX: 12.55). Please refer to the IMPORTING FINANCIALS section above.
    • Default Value and Fill Value: Without any conditional checks, Collect! will populate the data directly into the field as it is typed. These 2 fields can also be used to do comparisons. For example, you can put a value in the Default Value field, check the "Must Equal Default Value" radio on the Import Field Options form, then if the data in the file matched the value in the Default Value field, then the value in the Fill Value will be written to the field on the record.
    • Scan For: You can use the ScanF functionality to parse data. Please refer to the Help topic How To Use Scanf for more information.
  7. Click the Other Options button:
    • Conditional Criteria: As already mentioned with the Default Value field, you can use the bottom 4 radio options to compare against the Default Value field. If true, the value in the Fill Value will be written to the record. The compare against zero only works for numeric data. Blank permitted and Must Be filled apply to all data types, but keep in mind that a value of 0.00 in the data field is still technically filled. Select the applicable value. Typically, this is left as Blank Permitted. If a condition is set and it fails, the entire record is skipped, UNLESS you have the Fail Cancels Field Only box checked.

      • Useful Note You should set the Must Be Filled on one field in each record that should always be populated. For example, the Name or Last Name field for Debtor and Cosigners. Or you can set the To Us field for Transactions to be Must be Non Zero. This will prevent blank records from being created.

    • Fail Cancels Field Only: Check this if you have a condition that you only want to fail on this field, rather than the whole record.
    • Strip Non-Numerics, Spaces, Decimal Point: If you want to remove un-needed characters.

      • Useful Note We recommend using the Strip Non-Numerics in conjunction with the Only Numerics in Phone and SSN fields on the Screen and Messages form. This will allow you to only store the numeric data in the database, but apply a field mask on the display.

    • Refer to the form section above for the remaining fields.
    • Click OK to close the form.
  8. If this field has a different date format than what was specified as the global date format for the import map, click the Date Options button and set the applicable date format.
  9. Click OK to return to the Field list.

    10. Useful Note Instead of clicking OK, you can use Ctrl + N or F3 to save the Import Field Specification and display a blank form for the next field.

  10. Repeat the above process for all remaining fields for this record type.

    11. Useful Note Not all data is in the data file. You can use records with @> in the note field to load default data like Operator/Sales (if not set in the Client Settings), Status Code, Address OK, or Interest Details. For fields that are check boxes, use the letter X in the Default or Fill value field. You can use Printable Information codes. These should be placed into the Default Value field.

  11. Once you have finished the remaining fields for this record type, repeat the above process for any other Import Record Definitions and their Import Field Specifications.

When you have completed the Import Map's Record Definitions, you should have something that looks like the following.


Completed Record


Completed Import Map

Top of page.

Using Keys To Find Existing Records

The Import module has lookup capabilities based on values in the data file or Default Value field.

  1. In a field that you want to use for data lookup, check the Use as Key to Find Existing box. If the data is referencing the data file, it will use that value to look up a record; otherwise, it will use the value in the Default Value field.

    2. Useful Note You should use a field that is unique like file number, or a combination of field like Acct and Client Number. Please note that if you select a Client on the Import Options or Import Menu, it will automatically be used as a key.

  2. Exact Match Only should only be checked if you want to do an exact match on a Text field. Numeric fields will always do an exact match. For text fields, if this is not checked, then Collect! will return the first partial match.
  3. Inhibit Autocreate should be checked if you are not loading new records, like a payment import, that way blank records won't be created if the key is not found.
  4. Skip Subrecords if Key Not Found should be checked in most cases to prevent sub-records, like Transactions, from being created on random records.
  5. Skip if Record Found should only be checked if you want to skip an existing record. For example, you are importing a payment file and you are key on the Transaction ID and want to prevent duplicate payments from posting.

    6. Useful Note Check both the Skip Subrecords if Key Not Found and Skip if Record Found to emulate "Skip Subrecords if Key Found" where you don't want to import sub-records. For example, you don't want to import Cosigners if the Debtor already existed.

  6. Log Skipped Record to File can be checked if you want the skipped records to be written to the Collect! Application Log.
  7. Repeat for any other fields that you want to use as keys.


Use Keys to Find Existing

Top of page.

Text Concatenation With Appending

In some cases, like Debtor Name or Notes, you may want concatenate strings from multiple sources into a single field. You can use the Append to Existing Data section to accomplish this.

Debtor Name Example

  1. Set the first Import Field Specification record as you normally would to reference the last name in the data file (EX: column A) and save to the Name field.
  2. On the second record, which references the first name (EX: column B), check the Append to Existing Data, Prepend Comma, and Add Space before Appending fields.

Useful Note The logical process that the import is programmed to do is to store the last name from column A in the name field. Once saved, the next Import Field Specification is set to append data. First, a comma is prepended to the data from column B (after the data already in the field). Then, a space is added before appending (after the comma, before the data from column B). Finally, the data is imported from column B.

Useful Note When populating the Name field, Collect! will automatically populate the Contact field, and vice versa.


Loading First Name

Note Line Example

All notes are imported into the TEXT field. The other fields in the import modules for notes can be ignored. Each step in the list below represents an Import Field Specification.

  1. If a date exists in the file, then reference it; otherwise, put @d in the Default Value field, and put @> in the Note field, if this is a delimited file.
  2. If a timestamp exists in the file, then reference it; otherwise, put @t in the Default Value field, and put @> in the Note field, if this is a delimited file. Then check the Append to Existing Data and Add Space before Appending boxes.
  3. If a status exists in the file, then reference it; otherwise, put a Status in the Default Value field, and put @> in the Note field, if this is a delimited file. Then check the Append to Existing Data and Add Space before Appending boxes.
  4. If an Operator code exists in the file, then reference it; otherwise, put an Operator code in the Default Value field, and put @> in the Note field, if this is a delimited file. Then check the Append to Existing Data and Add Space before Appending boxes.
  5. Put a : in the Default Value field, and put @> in the Note field, if this is a delimited file. Then check the Append to Existing Data and Add Space before Appending boxes.
  6. Reference the applicable data text from the data file. Then check the Append to Existing Data and Add Space before Appending boxes.


Importing Note Line

Top of page.

Comparing Data With Default Value

There may be cases where you want to read a value from the data file and fill with something else. Some example include:

  • Payment Files that use an identifier like P for purchase, R for return, V for Visa, M for Mastercard, etc.
  • Converting full text states to the abbreviation.
  • Status or Operator code conversion from another system to Collect!.

  1. On the Import Field Specification, enter a value into the Default Value field that you want to compare to a value in the data file.
  2. Click the Other Options button.
  3. Click the Must Equal Default Value radio button.

    4. Useful Note You can check the Must not Equal Value, Greater than Value, or Less than Value radio options for those applicable comparisons.

  4. Check the Fail Cancels Field Only box, IF this comparison is for this field only and should not stop the entire record.

    5. Useful Note You would not check the Fail Cancels Field Only box if you are writing a payment import and used different Import Record Specifications for each Transactions Type.

  5. If you are doing multiple comparisons, like States, and want to stop when a match is made, check the Skip Remaining Field References box.

    6. Useful Note Skip Remaining Field References is primarily used with the Must not Equal value option.

  6. Click OK.
  7. Enter a value into the Fill Value field. This will be written to the selected field if the condition is met.


Default Value to Compare Data

Top of page.

Testing The Import Map

Useful Note If you are running complex imports, you may want to backup your database before running the import.

Whenever you build an import routine, it is a good idea to test it at various stages. Depending on the complexity, you can test either when the map is finished or when you have finished an Import Record Definition.

At various stages below, you will be asked to correct errors, if they exist. After you go back and correct the errors, repeat the steps below up to where you left off.

Initiate the Import

  1. Navigate back to the File Format Specification screen.
  2. Click the Import button (above the Import Options button) to display the Import Menu screen.
  3. Select the Down arrow next to the field labeled File Format. The File Format Specification list is displayed.
  4. Select Import Map that you are working on.
  5. Review any fields that were auto-populated. If there are any errors, click the CLOSE button to go back to the map and correct the issues. Possible errors:
    • Wrong data in the fields
    • Fields were populated that should have been blank (ignore the Starting File Number field)
    • Fields were empty that should have been populated
  6. File Name: If you specified a file name in the Import Map, this will be populated. If you didn't specify a file name, click the Browse button.
    • On the Open Import File dialog box, navigate to the location of the file that you are importing, then select the file, and click the Open button. Now the file and its location will be displayed in the field labeled File Name.
  7. Client/Number: If you specified a Client in the Import Map, this will be populated. If you didn't specify a client, or the specified client has accounts already, open either Client pick list and select the Client that you want to run the test import on.

    8. Useful Note Do not import test accounts in to a live Client. If this is a new client, without accounts, that is fine, but if it's a client with existing accounts, then we highly recommend using a TEST Client with the same settings as the live Client.

    Useful Note Not all Imports Maps require you select a Client. For example, if you are importing payment files, skip trace results, or dialer results, the Import Map should already be setup to key on a unique identifier like File Number. If your key is on Client Account Number, then you would select a Client to create a combined key for the lookup.


    Import Menu Settings

  8. Select the Import button. You will see the first record that is being imported.

Useful Note When you run an import, you will get the following prompt asking if you want to disable record tagging. Select NO if you plan to run batch processing or recalc after the accounts are imported.


Disable Record Tagging

Examining Records as They are Imported

When you import records, it is very important to examine the results that you are getting. For this reason, the import routine pauses as each record is imported. This gives you an opportunity to refuse the record, cancel the import, or proceed.

Notice at the bottom of the Collect! screen, in the status bar, that you are being prompted for a response. In white letters, you will see Update Debtor? (Yes/No/All/Cancel) Do not choose anything yet!


Debtor Record Being Imported

Useful Note You may have to auto hide your Windows taskbar if it is hiding the status bar in Collect!.

You have four choices:

  • Yes [y] accepts the record, imports it and displays the next record ready to import.
  • No [n] refuses the record, skips it and displays the next record ready to import.
  • All [a] runs the whole import with no more prompts.
  • Cancel [c] aborts the importing routine. Whatever you accepted up to this point is imported.

Useful Note Once an import begins, these are your only choices. We will always choose one of these letters. Clicking on the screen with your mouse may cause import issues. This is not an active form, but rather a snapshot for you to verify.

  1. If everything looks correct, press the y key on your keyboard to accept the record. If there are errors, press the c key on your keyboard to cancel the import and go back to correct the errors.
  2. The next record will now appear. Check for errors and proceed accordingly.
  3. Repeat this process until you have reviewed a large enough sample that is error free.
  4. When you are comfortable that the import is working, press the y key on your keyboard to import all remaining data.
  5. After the data has been imported, a summary window will appear. Review it and click OK.


    6. Import Summary

  6. If you selected the Recalculate Accounts box on the Import Options, you will be prompted to recalculate the debtors. If you imported dollar amounts, select the Yes button.

    7. Useful Note If you do not recalculate, you can always do it after the import. Select Tools from the top menu bar and select Recalculate from the drop-down choices.

  7. After the import, close the window and check out the new debtors to make sure the data is correct.

Useful Note After an import, newly imported records remain tagged. Select Browse from the top menu and select All Debtors from the drop-down choices. Then Select Edit from the top menu bar and select View Tags from the drop-down choices. You will be viewing a list of all your newly imported records.

Top of page.

Clean Up The Test And Do Live Import

Once you have reviewed the accounts and validated the data (data in correct fields, financials are accurate, etc.), then you need to clean up the data and do the live import.

  1. Navigate to the Main Menu.
  2. Click on the Edit menu and select Clear Tags. If you are unsure, you can close and reopen Collect!.
  3. Navigate to the Client that you imported the accounts to.
  4. Delete the imported Debtor Accounts:
    • If this was a new Client, open the Client, click in to the Debtors tab, use Ctrl + A (or Edit -> Select All) to tag all records, then delete them.
    • If this was a TEST Client created specifically for this test import, then delete the Client.

Top of page.

Troubleshooting

If you find that the import overwrites data, check your import map. Overwriting will occur if you have the 'Use as key to find existing' and/or 'Exact match only' turned ON for a field. This switch will cause overwriting if a match is found. Consider turning OFF all 'Use as key' fields.

Top of page.

Was this page helpful? Do you have any comments on this document? Can we make it better? If so how may we improve this page.

Please click this link to send us your comments: helpinfo@collect.org

Copyright © 2024 Comtech Systems Inc. All rights reserved. Use of the Collect! system indicates acceptance of our product terms • Legal Notices
Use of the Collect! website indicates acceptance of our website terms
This document and any attendant software and/or services subject to change without notice.