CSV Import Manual

The Alarm Traffic Manager central station software supports the importation of comma separated value (CSV) files submitted by dealers. These files support a subset of the features of the central station database. Specifically not supported are accounts that have open/close supervision, line security, system certification (such as UL), periodic reports prepared by the central station, more than one alarm cancel code or special instruction text. The supported features are sufficient for monitoring most residential and small commercial accounts.

The CSV file is organized in the following fields:

No.		Field Name	Size	Description
1		Old Account No.	7	The account number, if any, that is presently assigned to this account. For new accounts this field will contain zero. For existing accounts this field must contain the existing account number.
	2	New Account No.	7	Placing a zero in this field will delete an existing account. Otherwise, this field contains the current subscriber account number. This field may be used to change the subscriber’s account number.
	3	Group No.	5	The group number is usually the same as the dealer number. Mandatory.
	4	Subscriber Name	30	The name should be in first name first format using both upper and lower case. Mandatory.
	5	Address 1	30	This is the first of two address fields. It should contain building, apartment and suite numbers, where appropriate.
	6	Address 2	30	This is the second of two address fields. It should contain the street number and street name. An address is mandatory.
	7	City	20	Mandatory.
	8	State	2	Mandatory.
	9	Zip Code	10	Five digit zip codes are acceptable. Use a dash when sending Zip + 4. Mandatory
	10	Main Phone	12	All telephone numbers must be in nnn-nnn-nnnn format. This field must contain the telephone number which operators will use for the verify call. Mandatory.
	11	Alt. Phone	12	This optional field will be called during the verify step if the main phone could not be used to cancel an alarm. Use of this field is optional.
	12	Account Type	1	This field must contain “R” for residential or “C” for commercial.
	13	Contract Date	8	Dates must be in mm/dd/yy format. Years from 51 through 99 are considered 1900’s. Years from 00 through 50 are considered 2000’s. If this field is omitted the date of import will be used by default.
	14	Inactive Date	8	Leave blank for active accounts.
	15	Cancel Code	16	Code word or number. If blank the lower three digits of the account number will be accepted as the proper code. Only one cancel code per account is supported for file import.
	16	Permit Number	16	Optional unless required for dispatching.
	17	Directions	50	Name of sub-division or development should be included here. Optional.
	18	Dealer Defined Field	10	Optional field. Use as you see fit.
	19	Panel Type	16	Optional.
	20	Police Phone	12	Mandatory telephone number of police dispatch agency. May use pound sign followed by Central One’s dispatch agency identification number, ie #801.
	21	Fire Phone	12	Mandatory telephone number of fire dispatch agency. . May use pound sign followed by Central One’s dispatch agency identification number, ie #801.
	22	Medical Phone	12	Optional telephone number of medical dispatch agency. . May use pound sign followed by Central One’s dispatch agency identification number, ie #801. If omitted, the fire dispatch agency number will be used.
	23	Template Account No.	7	The account number of the template account that contains the signal data for this subscriber. Signal definitions are not required if SIA or Ademco Contact ID formats are used.
	24	Callout 1 Name	30	Use upper and lower case. First name first.
	25	Main Phone	12	nnn-nnn-nnnn format
	26	Alternate Phone	12	nnn-nnn-nnnn format or may be omitted
	27	Callout 2 Name	30	Use upper and lower case. First name first.
	28	Main Phone	12	nnn-nnn-nnnn format
	29	Alternate Phone	12	nnn-nnn-nnnn format or may be omitted
	30	Callout 3 Name	30	Use upper and lower case. First name first.
	31	Main Phone	12	nnn-nnn-nnnn format
	32	Alternate Phone	12	nnn-nnn-nnnn format or may be omitted
	33	Callout 4 Name	30	Use upper and lower case. First name first.
	34	Main Phone	12	nnn-nnn-nnnn format
	35	Alternate Phone	12	nnn-nnn-nnnn format or may be omitted

The CSV file must contain comma delimiters for all of the fields through the first callout’s alternate phone number. It is recommended that text fields be contained in quotation marks. This allows text fields to contain commas. Extra fields at the end of a record are ignored.

Template account numbers must belong to the dealer submitting the file. In addition to signals the two way voice type and communicator test interval and fail to test code fields will be imported from the template account.

The following logic applies to the old and new account number fields:

Old Number	New Number	Import Action
0	123	Create new account. It is error if account number 123 already exists.
123	123	Update an existing account. It is error if account number 123 does not already exist. All fields must be submitted with the update. If a blank field is submitted it will erase the existing contents of the subscriber’s record.
123	456	Update an existing account and change the account number. All fields must be submitted.
123	0	Delete an account.

The file import utility verifies that the account numbers you use have been allocated to you (or a group to which you have access).

The file import utility will not process any part of a file which contains errors. Central station data entry personnel will add dispatch agencies to the central station database when new telephone numbers are received in the CSV file. For all other errors you will be notified and must resubmit the file.

When a template account number is changed during an update the signals in the new template will completely replace the old. If the template account number changes to zero there will be no changes.

When deleting an account the only required fields are old account number, new account number and group number.

Creating Your Own Database to Update Central One

If you wish to keep central station information in your own database using Microsoft Access, SQL or any similar database program you will need to be sure to add all of the fields enumerated above in addition to the fields you use for account management, billing and service purposes. It is a good idea not to allow screen editing of the “old account number” field. This field should default to zero when a new account is created and be updated by your export program when the CSV file is made for Central One. You should also create a Boolean field that can be called the mod flag. Whenever any of the fields that go to Central One are modified this flag should be set to TRUE. When the export program creates a record in the CSV file for Central One the field must be changed back to FALSE. The best way to keep these two fields from being editing improperly is to use data entry forms and to omit these fields from the form.

Create a procedure in connection with each of the central station data fields that validates the contents of the field and also sets the Boolean mod flag to true. The export procedure logic would be as follows:

while not end_of_subscriber_file

get next record

If mod flag is true

set mod flag to false

write all central station data fields to the CSV export file

set old account number field to new account number

end if

end while

Because the CSV import file structure is a subset of the monitoring database capabilities of Central One’s Alarm Traffic Manager software there may be some subscriber accounts that cannot be supported by this method. To solve this problem you will need to create an additional Boolean field called the exportable flag. The field should default to true and be changed manually to false for any subscriber account that exceeds the CSV format capabilities. For example, if a subscriber needs more than four callouts the CSV file format will not be adequate. The “exportable flag” would be set to false and the export procedure would be modified as follows:

while not end_of_subscriber_file

get next record

If mod flag is true

set mod flag to false

if exportable flag is true

write all central station data fields to the CSV export file

else

print all central station data fields on a local printer (for manual special processing)

end if

set old account number field to new account number

end if

end while