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 described below is not required in the specification if you import files via the Bulk Import web module as selecting the file format is part of this process.
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 second row of the import file must contain the appropriate column names. 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.
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 | True/False | ContainsSecondaries | |
| SECONDARY_COUNT | 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 ZoneIdentifier. The system translates the ZoneIdentifier as NPANXXZoneID that must be an existing value in the NPANXXZone table. | NPANXXZoneID | |
| 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 |
