Summary
This document describes the file format for importing serviceable address records using the Bulk Data application.
Links:
File Format
General Format
The following general characteristics apply to create and modify serviceable address records:
File naming convention must be *.csv
A header is required
The header must be followed by one or more comma-delimited detail records
Place quotes (“) around any text fields containing a comma.
Empty (non-required) fields are acceptable.
The columns are not required to appear in a specific order.
You do not have to include all columns in an import file.
Import Identifier
The import identifier is optional if you import files via the Bulk Import web module. The correct identifier is automatically applied by selecting the type of import.
If you import using the file automation method by uploading files and placing them on the file server, the first row of the file must have the file format identifier, starting in the first position of the first row. For the serviceable address file the identifier is:
FORMAT:IDI/CostGuardBulkData/ServiceableAddress
Note:
“Format:” is part of the identifier and must be included.
Header Record
The import file must contain the appropriate column names. This is typically the first row when using the Bulk Import module. It will be the second row when using the file automation method. The column names are listed in the Record Layout section of this document. The columns do not need to appear in a specific order. Import files do not need to include all of the columns, only the columns that contain data to be imported are necessary.
Custom Properties
The header record must list a field for each unique custom property name that is being assigned a value in the file. Add custom property fields using the following syntax:
ATTR_<property name>
Note: For mandatory BDC reporting, broadband service providers can store the BSL ID for their address as a custom property.
Record Layout
Field Name | Required | Description/ Logic | Serviceable Address Target Field |
|---|---|---|---|
SMARTY_KEY | Y | Unique key for the address. If there is already an address with the value sent, then the address is updated. Otherwise, it is added. | ExternalReference |
FIRST_LINE | not used | ||
PRIMARY_NUMBER | Y | StreetNumber | |
PRE_DIRECTION | StreetDirectionPrefix | ||
STREET_NAME | Y | StreetName | |
STREET_SUFFIX | Thoroughfare | ||
POST_DIRECTION | StreetDirectionSuffix | ||
SECONDARY_DESIGNATOR | Designator1 | ||
SECONDARY_NUMBER | Designator1Value | ||
CITY | Y | City | |
STATE | Y | 2 character state | State |
ZIP | Y | Zip | |
ZIP4 | Zip4 | ||
COUNTY_FIPS | CountyFIPS | ||
COUNTY | County | ||
RDI | Y | Residential/Commercial | ResBusIndicator |
CENSUS_BLOCK_CODE_2020 | CensusBlock | ||
SDP_TAG | not used | ||
ALIAS_FLAG | Skip records where this is TRUE | ||
SECONDARY_FLAG | SecondaryFlag | ||
CONTAINS_SECONDARIES | Y | True/False | ContainsSecondaries |
SECONDARY_COUNT | Y | If CONTAINS_SECONDARIES is True, this must be set to an integer greater than 0 (zero). If CONTAINS_SECONDARIES is False, this must be set to 0 (zero). | SecondaryCount |
LINK_TO_PARENT_ADDRESS | ParentExternalReference | ||
LATITUDE | Decimal Value | Latitude | |
LONGITUDE | Decimal Value | Longitude | |
PRECISION | not used | ||
SERVICE_AVAILABLE | True/False When this field is not sent or not populated, the address is loaded as Available (True) | ServiceAvailable | |
AVAILABLE_DATE | This field must contain a date or be null; it does not support date/time or time offsets. Format: mm/dd/yyyy | AvailableDate | |
PRICING_ZONE | Derive Pricing Zone using a Zone Reference. If you pass in this value in the bulk import, use the Zone Reference as defined in the web Product Catalog Pricing Zone configuration. | Zone Reference | |
TECH_REGION | For Field Service Management. No validation. | TechRegion | |
QUALIFICATION_TAGS | Comma-separated list in quotations. Tags will get added into the system as they are received, and are not validated. | Creates entries in: ServiceableAddressQualificationTagLink | |
ATTR_* | Contains the value of a custom property for a Serviceable Address. There may be zero or more custom property fields in an import record. See Custom Properties must be listed in the Header row. Custom Properties section above. | Custom property as specified in the header |