How To Create A Fixed Length Import Map

Collect!'s import module is a very powerful tool for importing data into Collect! from other databases, instantly giving you all the 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 to bring account information into the database. It is necessary that you are familiar with the use of Collect!'s Client and Debtor screens.

For this example, we will import data into Collect! using a fixed length *.txt file. If you are not familiar with *.txt files, we would recommend someone who is knowledgeable is this area. (i.e. technician or co-worker) The file we will be using for this import procedure is the fixedlengthinvoice.txt located in the Import folder of your Collect! application. If you do not have this file in your Import folder, please download the sample imports from Collect!'s Member Center before proceeding.

Download Import Samples

Please open the sample fixedlengthinvoice.txt file in a text editor program that allows you to view line numbers and character positions (i.e. Ultra Edit, WordPad, etc.) and take a look at it.

Fixed Length Invoice Text File

When viewed in a text editor program, each invoice account is in a block of data separated by an asterisk header, *****. The header is called an Identifier and is the key in determining where one invoice ends and the next one starts. Identifiers can sometimes be hard to find as they must be unique and can only occur once per invoice. The identifier must be above all the data that is being imported for each invoice.

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, as well as additional default settings. Please perform this exercise in your DEMODB Database.

Before proceeding, please create a test client in the DEMODB database so that we can import debtors to it.

In a Collect! database, records types follow a file hierarchy. It is very important to consider when you are creating

an import routine. The head of the file hierarchy must be imported or keyed on with conditional logic to locate a specific record in the case of an update import in order to import a fresh sub-record or update an existing sub-record.

Examples: In order to import client Attachments or Notes, you must first import a fresh client or key for an existing Client. In order to add Debtor Cosigners/Other parties, or Attachments, or Transactions, you must first import a fresh debtor or key for a specific existing debtor.

Let's begin by building the import map to bring the new debtors listed in the fixedlengthinvoice.txt file into your DEMODB database.

Create File Format Specification

1. Sign into your DEMODB database in Collect! and stop at the Main Menu. Select File from the top menu bar. Select Import/Export from the drop-down choices. A sub-menu will be displayed.

2. From the Import/Export sub-menu, select Customize Import/Export, 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. Type Fixed Length Import Sample in the field labeled Name.

5. Type *.* in the field labeled File.

6. Type Import Debtor Information in the field labeled Note.

7. Make sure the radio button labeled Fixed Length has a dot in it - select this with your Mouse or press your Spacebar.
The small box to the right of this field is left empty. Press F1 for information about the other choices on this form. We won't be using any other fields for this exercise.


File Format Specification Form

Create Record Definition

1. Click your mouse in the Import Record Definition sub-form to a activate it, or press F5. You will be prompted to create a Record Definition.

2. Select Yes to create our first Record Definition. You should now be looking at a new Import Record Definition form. We are going to create a Record Definition for the Debtor record because we are importing Debtors.

3. 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.

4. Choose Debtor from the Record Type list. You will be prompted to include all the fields from the Debtor form. Select No to refrain from entering all the fields from the Debtor form into your import map.

5. Previously, we discussed that the file contained a line of five asterisks. These are going to be used as an Identifier to tell Collect! when each new block of debtor information starts. In the field labeled Identifier, put in five asterisks. We do not need an End of Line ID since each invoice in the file is directly under the previous one. All the other fields are left empty.


Debtor Import Record Definition

We left the Offset field empty. However, if the asterisks (or Identifier) was not in the first position, we would need to specify the character position (or Offset) where the asterisks begin. Collect! starts looking at character position or Offset '0' (zero), whereas most text editor programs start at position 1. A blank or zero is considered the start position for an offset in Collect!

Create Import Fields Specifications

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

specification will match a field name on the Debtor Form. If you are unsure which fields we are choosing, please look at the Debtor form.

In the fixed length file, as explained above, the data for each field is located at a specific line number and character position.

1. Click your mouse in the Import Field Specification sub-form to activate it, or press F5. You will be prompted to create your first Field Specification. Select Yes to create the specifications for the fields we want to include.

2. Select the Down arrow next to the field labeled Field. This will display a list of all the fields in the Debtor form that may

be included in your import map. Scroll through the Field Type list and select Acct. You will be returned to the File Specification form and Acct will be displayed in the field labeled Field.

3. In the field labeled Note, type Debtor Account Number. This is the name of the field you are importing. What you enter here does not get imported. It is for your reference only.

4. You must now tell Collect! where to find the account data in the fixed length file. Using the identifier as our reference point for line counting, the account number data is one line down at position 7. Hence, place a 1 in the field labeled Lines after Identifier and place a 6 in the field labeled Offset. Remember, Collect! starts counting at position zero. So seven positions is 0, 1, 2, 3, 4, 5, and 6.

5. You must also tell Collect! the character length of the data to import from this location. The account number is currently 11

characters long. However, it might be possible that other account numbers are larger. Instead of placing 11 into the field labeled Length, let's put 15 just in case. If the account number is always 11 in size, you can place 11 in this field.


Debtor Acct Field Specification

If we were going to update existing debtors, we would make sure to select the check box labeled 'Use as key to find existing' and/or 'Exact match only'. Then the import routine would only create a new debtor if the account number did not already exist in the database. Please leave this without a check mark for our exercise.

6. Press F3 to display a blank Import Field Specification form for the next field.

If you need to go back or forward to another import field, select the << or >> button or select the OK button and then select the field in the Import Field Specification sub-form.

7. Select the Down arrow next to the field labeled Field. You will see a list of all the fields you can choose from. You will notice that Collect! positions you in the list at the Acct field since that was our last selection.

It is always good to scroll back to the top or press CTRL+HOME to return to the top of the list. Some import fields have duplicate names, but have different index values in the record. 'Name' is one of them.

8. Select Name from the Field Type list.

For advanced ways of importing into the field labeled Name on the Debtor form, please look at the Name Examples in the list of File Format Specifications.

9. In the field labeled Note, type Debtor Name. This is the name of the field you are importing.

10. This data is located 2 lines from the identifier and starts at the first position. Type 2 into the field labeled Lines after identifier and put a zero into offset (or leave it empty). Since lengths of names can vary and there is no extra data after the name on that line, we can set the length to a larger size, like 35 characters.


Debtor Name Field Specification

11. Continue adding the next seven data fields: Address, Phone, City, SSN, State, DL, and Zip. They must be entered in this order , as you must go from left to right, line by line. You can not move back up a line if a field is missed. The data is read down and across the line.

It is always good to scroll back to the top or press CTRL+HOME to return to the top of the list. Some import fields have duplicate names, but have a different index value in the record. As you become more familiar with the list, you will be able to find the fields much easier and will not need to scroll to the top every time.

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. So when you create a Field Specification for a Date field, (i.e. DOB, Charged Date, or Delinquent Date), the Import Field Date Format must be correctly filled out to match the way the dates are formatted in the raw data file. For instance, in our sample *.txt file, the date format is mmddyyyy (01/01/2003). We have to specify this in the specification for this field.

When you get to Date of Birth in your field specifications,

1. Press F3 to display a blank Import Field Specification form.

2. Select the Down arrow next to the field labeled Field. This will display a list of all the fields you can choose.

3. Select DOB from the Field Type list.

4. Type Date of Birth in the field labeled Note.

5. Offset is 62, length 10, and Lines after Identifier is 6

6. Now select the Date Options button to open the Import Field Date Format form.

7. Make sure the radio button labeled MMDDYY is selected with a dot.

8. Make sure the check box labeled Day Leading Zero (05 vs 5) is selected with a check mark.

9. Make sure the check box labeled Month Leading Zero (03 vs 3) is selected with a check mark.

10. Make sure the check box labeled Century(1995 vs 95) is selected with a check mark.

11. Type a forward slash [ / ] in the box labeled Date Separator.


Date Options Settings

12. Select the OK button to save the information.

13. Press F3 to create a new blank form.

Setting Currency Formats

Usually money amounts already exist with their decimal placements, as you can 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 Original Principal field in the Financial Detail form and then displays this in the Principal field on the Debtor form.

1. We should be on a blank form. If not, press F3. Select Original Principal from the field list.

2. If your *.txt 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.

3. Fill in the appropriate Offset, Length, and Lines after identifier numbers.

4. You should now have an import map which maps to eleven data fields.

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.

Field Specifications List

Setting Default Values

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

Up to now, to keep this exercise simple, we have only referenced fields in the *.txt file and related them to fields in Collect! Sometimes, you will want to set default values for other fields as you import records. For instance, when importing debtors, you may want to set the Debtor Address OK switch to ON, set the Debtor Mode to Active, and set the Debtor Status to New. Setting these defaults is a very good idea whenever you import debtors. We will set them now.

You should have a blank Import Field Specification form in front of you. If not, press F3.

1. Select the Down arrow next to the field labeled Field and select Mode from the Field Type list.

2. Type Set Mode = Active in the field labeled Note.

3. Type Active in the field labeled Default value.


Set Mode To Active

4. Press F3 to display a blank Import Field Specification form for the next field.

5. Select the Down arrow next to the field labeled Field and select Status from the Field Type list.

6. Type Set Status to New in the field labeled Note.

7. Type New in the field labeled Default Value.

Usually you would want to assign an operator to the import accounts. Here we're going to assign the accounts to an in house operator, HSE.

8. Press F3 to display a blank Import Field Specification form for this field.

9. Select the Down arrow next to the field labeled Field and select Operator from the Field Type list.

10. Type Set Operator in the field labeled Note.

11. Type HSE in the field labeled Default Value.

Default Values For Switches

A switch means any place in Collect! where you mark your choice with a check mark. Here's how to turn ON a switch, such as the Address OK setting, when you run an import.

1. Press F3 to display a blank Import Field Specification form for the next field.

2. Select the Down arrow next to the field labeled Field and select Address OK from the Field Type list.

3. Type Set Address OK in the field labeled Note.

4. Type X in the field labeled Default Value. This indicates that the switch is switched ON.

This method can be used whenever you want to set the value of a switch in your import. Now let's finish up.

5. Select the OK button, or press F8, to save your settings and exit the Import Field Specification form. You will now notice a list of all your Field Specifications in the Import Field Specification list.


Completed Field Specifications List

We have now completed the import map. You should have a field listing similar to the image above.

6. Select the OK button, or press F8, to save your settings and exit the Import Record Definition form. You should be looking at the File Format Specification form.


Completed File Format Specification

Set Commission Rates And Tax During Import

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

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 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

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.

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

The Tax Rate Option 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 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

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.

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.

Testing The Import Map

Whenever you build an import routine, it is a good idea to test it at various stages. Let's try out what we have designed so far. This will confirm that it is behaving as we expect.

1. You should be looking at the File Format Specification form. Select the Import button. The Import Menu is displayed.

2. Select the Down arrow next to the field labeled File Format. The File Format Specification list is displayed. Our Fixed Length Sample is a new item in this list.

3. Select Fixed Length Import Sample. Now this is displayed in the field labeled File Format.

4. Select the Browse button next to the field labeled File Name and navigate to the location of the fixedlengthinvoice.txt.

It should be in the Import folder of your Collect! application.

5. Select fixedlengthinvoice.txt and select the Open button. Now the file and its location will be displayed in the field labeled File Name.

6. Select the Down arrow next to the field labeled Client. This will display the list of all your clients. Select the client that you set up for this test. Now this client is displayed in the field labeled Client.


Import Menu Settings

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

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/2019 15:30 NEW OWN : Review New Account )

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.

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 automatically create the date/time stamp.

Importing Partial Note Line

Examining Records As They Are Imported

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

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.

1. You are now looking at the first invoice being imported.

2. 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

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.

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.

3. Press the y key on your Keyboard to accept the record.

4. The second invoice record will now appear. Press the ykey to accept this debtor as well.

5. The third invoice record now appears. We are comfortable with this data, so we will press the a key to accept all.

If this were a real import routine, you would probably want to examine a few more records in this fashion before pressing the a key to allow the import to carry on automatically without your assistance.

6. After the data has been imported, a summary window will appear.


Import Summary

7. Select the OK button on the Summary Report.

8. You will be prompted to recalculate the debtors. If you imported dollar amounts (which we did), select the Yes button.

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.

9. After recalcing, Collect! will prompt to sort the debtors. This is up to you, but most clients do.

10. If you selected Yes, Collect! will prompt you to choose your sort order. The choices are by Name or by Account.

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

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.

You have just completed your import. Although we imported a simplified file, you have successfully completed all the major steps

to an actual import routine. As you can see, taking this step by step in the correct order, you can quickly and accurately bring

volumes of data into your database.

• Remember to start simple and test your results along the way
• Always test your import map on a DEMO database or a copy of your Masterdb database
• Never run your import on your live database until you are SURE it is performing correctly in every area
• Backup your database before importing records into it

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.

If for any reason, you decide to re-import, make sure you delete the debtors you just imported.

Importing Address Fields

When importing a fixed length file with City, State and ZIP on a single line that can be imported as a single string, you can import it into the Addr 1 field. This field will automatically split the string into City, State and ZIP and put each element into the appropriate field on the Debtor form. If there is an actual Addr 1 to be imported, such as an Apt. #, or an additional Address line, this can be imported with a second pass using another Debtor record definition.