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
submenu will be displayed.
2. From the Import/Export submenu, 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 subform 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 subform
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'. 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 subform.
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
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.
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.
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 first 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' 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.
See Also
- How To use Import/Export
- Import/Export Topics
|
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