This is a followup to this post, required because the FCC has changed the format of their files from version 3 to version 4.
The contents of the eight FCC files are now (as described in the version 4 document):
Amateur
[AM] -- unchanged from version 3
1 Record Type [AM] char(2)
2 Unique System Identifier numeric(9,0)
3 ULS File Number char(14)
4 EBF Number varchar(30)
5 Call Sign char(10)
6 Operator Class char(1)
7 Group Code char(1)
8 Region Code tinyint
9 Trustee Call Sign char(10)
10 Trustee Indicator char(1)
11 Physician Certification char(1)
12 VE Signature char(1)
13 Systematic Call Sign Change char(1)
14 Vanity Call Sign Change char(1)
15 Vanity Relationship char(12)
16 Previous Call Sign char(10)
17 Previous Operator Class char(1)
18 Trustee Name varchar(50)
Comments
[CO] -- unchanged from version 3
1 Record Type [CO] char(2)
2 Unique System Identifier numeric(9,0)
3 ULS File Number char(14)
4 Call Sign char(10)
5 Comment Date mm/dd/yyyy
6 Description varchar(255)
7 Status Code char(1)
8 Status Date mm/dd/yyyy
Entity
[EN] -- unchanged from version 3
1 Record Type [EN] char(2)
2 Unique System Identifier numeric(9,0)
3 ULS File Number char(14)
4 EBF Number varchar(30)
5 Call Sign char(10)
6 Entity Type char(2)
7 Licensee ID char(9)
8 Entity Name varchar(200)
9 First Name varchar(20)
10 MI char(1)
11 Last Name varchar(20)
12 Suffix char(3)
13 Phone char(10)
14 Fax char(10)
15 Email varchar(50)
16 Street Address varchar(60)
17 City varchar(20)
18 State char(2)
19 Zip Code char(9)
20 PO Box varchar(20)
21 Attention Line varchar(35)
22 SGIN char(3)
23 FCC Registration Number (FRN) char(10)
24 Applicant Type Code char(1)
25 Applicant Type Code Other char(40)
26 Status Code char(1)
27 Status Date mm/dd/yyyy
Application/License Header
[HD]
1 Record Type [HD] char(2)
2 Unique System Identifier numeric(9,0)
3 ULS File Number char(14)
4 EBF Number varchar(30)
5 Call Sign char(10)
6 License Status char(1)
7 Radio Service Code char(2)
8 Grant Date mm/dd/yyyy
9 Expired Date mm/dd/yyyy
10 Cancellation Date mm/dd/yyyy
11 Eligibility Rule Num char(10)
12 Reserved char(1)
13 Alien char(1)
14 Alien Government char(1)
15 Alien Corporation char(1)
16 Alien Officer char(1)
17 Alien Control char(1)
18 Revoked char(1)
19 Convicted char(1)
20 Adjudged char(1)
21 Reserved char(1)
22 Common Carrier char(1)
23 Non Common Carrier char(1)
24 Private Comm char(1)
25 Fixed char(1)
26 Mobile char(1)
27 Radiolocation char(1)
28 Satellite char(1)
29 Developmental or STA or Demonstration char(1)
30 InterconnectedService char(1)
31 Certifier First Name varchar(20)
32 Certifier MI char(1)
33 Certifier Last Name varchar(20)
34 Certifier Suffix char(3)
35 Certifier Title char(40)
36 Female char(1)
37 Black or African-American char(1)
38 Native American char(1)
39 Hawaiian char(1)
40 Asian char(1)
41 White char(1)
42 Hispanic char(1)
43 Effective Date mm/dd/yyyy
44 Last Action Date mm/dd/yyyy
45 Auction ID integer
46 Broadcast Services - Regulatory Status char(1)
47 Band Manager - Regulatory Status char(1)
48 Broadcast Services - Type of Radio Service char(1)
49 Alien Ruling char(1)
50 Licensee Name Change char(1)
51 Whitespace Indicator char(1)
52 Operation/Performance Requirement Choice char(1)
53 Operation/Performance Requirement Answer char(1)
54 Discontinuation of Service char(1)
55 Regulatory Compliance char(1)
History
[HS] -- unchanged from version 3
1 Record Type [HS] char(2)
2 Unique System Identifier numeric(9,0)
3 ULS File Number char(14)
4 Call Sign char(10)
5 Log Date mm/dd/yyyy
6 Code char(6)
License Attachment
[LA] -- unchanged from version 3
1 Record Type [LA] char(2)
2 Unique System Identifier numeric(9,0)
3 Call Sign char(10)
4 Attachment Code char(1)
5 Attachment Description varchar(60)
6 Attachment Date mm/dd/yyyy
7 Attachment File Name varchar(60)
8 Action Performed char(1)
Special Condition
[SC] -- unchanged from version 3
1 Record Type [SC] char(2)
2 Unique System Identifier numeric(9,0)
3 ULS File Number char(14)
4 EBF Number varchar(30)
5 Call Sign char(10)
6 Special Condition Type char(1)
7 Special Condition Code int
8 Status Code char(1)
9 Status Date mm/dd/yyyy
License Free Form Special ConditionThe following extract from the code that creates the output database maps these on a one-to-one basis to internal identifiers (the four new fields in the [HD] records don't seem to be important -- at least for now -- so there is no change in the output as compared to the processing of version 3 files, although the new fields are processed):
Position Data Element Definition
[SF] -- unchanged from version 3
1 Record Type [SF] char(2)
2 Unique System Identifier numeric(9,0)
3 ULS File Number char(14)
4 EBF Number varchar(30)
5 Call Sign char(10)
6 License Free Form Type char(1)
7 Unique License Free Form Identifier numeric(9,0)
8 Sequence Number integer
9 License Free Form Condition varchar(255)
10 Status Code char(1)
11 Status Date mm/dd/yyyy
[AM]
RECORD_TYPE,[CO]
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
PHYSICIAN_CERTIFICATION,
VE_SIGNATURE,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,
PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME
RECORD_TYPE,[EN]
ID,
ULS_NUMBER,
CALLSIGN,
COMMENT_DATE,
DESCRIPTION,
STATUS_CODE,
STATUS_DATE
RECORD_TYPE,[HD]
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
ENTITY_TYPE,
LICENSE_ID,
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,
LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,
ATTENTION_LINE,
SGIN,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
STATUS_CODE,
STATUS_DATE
RECORD_TYPE,[HS]
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,
CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
RESERVED_1,
ALIEN,
ALIEN_GOVERNMENT,
ALIEN_CORPORATION,
ALIEN_OFFICER,
ALIEN_CONTROL,
REVOKED,
CONVICTED,
ADJUDGED,
RESERVED_2,
COMMON_CARRIER,
NON_COMMON_CARRIER,
PRIVATE_COMM,
FIXED,
MOBILE,
RADIOLOCATION,
SATELLITE,
DEVELOPMENTAL_STA_DEMONSTRATION,
INTERCONNECTED_SERVICE,
CERTIFIER_FIRST_NAME,
CERTIFIER_MIDDLE_INITIAL,
CERTIFIER_LAST_NAME,
CERTIFIER_SUFFIX,
CERTIFIER_TITLE,
FEMALE,
BLACK_AFRICAN_AMERICAN,
NATIVE_AMERICAN,
HAWAIIAN,
ASIAN,
WHITE,
HISPANIC,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
AUCTION_ID,
BROADCAST_SERVICES_REGULATORY_STATUS,
BAND_MANAGER_REGULATORY_STATUS,
BROADCAST_SERVICES_SERVICE_TYPE,
ALIEN_RULING,
LICENSEE_NAME_CHANGE,
WHITESPACE_INDICATOR
RECORD_TYPE,[LA]
ID,
ULS_NUMBER,
CALLSIGN,
LOG_DATE,
CODE
RECORD_TYPE,[SC]
ID,
CALLSIGN,
ATTACHMENT_CODE,
ATTACHMENT_DESCRIPTION,
ATTACHMENT_DATE,
ATTACHMENT_FILENAME,
ACTION_PERFORMED
RECORD_TYPE,[SF]
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
SPECIAL_CONDITION_TYPE,
SPECIAL_CONDITION_CODE,
STATUS_CODE,
STATUS_DATE
RECORD_TYPE,
ID,
ULS_NUMBER,
EBF_NUMBER,
CALLSIGN,
LICENSE_FREEFORM_TYPE,
UNIQUE_LICENSE_FREEFORM_ID,
SEQUENCE_NUMBER,
LICENSE_FREEFORM_CONDITION,
STATUS_CODE,
STATUS_DATE
The 48 output fields selected from the above lists are (arranged in groups of ten for easy counting):
ID,The contents of these fields are based on the original equivalent entries in the original data files. The entries for the fields are subject to the following transformations before being written to the output file:
CALLSIGN,
OPERATOR_CLASS,
GROUP_CODE,
REGION_CODE,
TRUSTEE_CALLSIGN,
TRUSTEE_INDICATOR,
SYSTEMATIC_CALLSIGN_CHANGE,
VANITY_CALLSIGN_CHANGE,
VANITY_RELATIONSHIP,
PREVIOUS_CALLSIGN,
PREVIOUS_OPERATOR_CLASS,
TRUSTEE_NAME,
COMMENT_DATE,
DESCRIPTION,
CO_STATUS_CODE, (i.e., STATUS_CODE from [CO])
CO_STATUS_DATE, (i.e., STATUS_DATE from [CO])
ENTITY_NAME,
FIRST_NAME,
MIDDLE_INITIAL,
LAST_NAME,
SUFFIX,
PHONE,
FAX,
EMAIL,
STREET_ADDRESS,
CITY,
STATE,
ZIP_CODE,
PO_BOX,
ATTENTION_LINE,
FRN,
APPLICANT_TYPE_CODE,
APPLICANT_TYPE_CODE_OTHER,
EN_STATUS_CODE, (i.e., STATUS_CODE from [EN])
EN_STATUS_DATE, (i.e., STATUS_DATE from [EN])
LICENSE_STATUS,
RADIO_SERVICE_CODE,
GRANT_DATE,
EXPIRED_DATE,
CANCELLATION_DATE,
ELIGIBILITY_RULE_NUM,
REVOKED,
CONVICTED,
ADJUDGED,
EFFECTIVE_DATE,
LAST_ACTION_DATE,
LICENSEE_NAME_CHANGE
- The entry is converted to upper case;
- Any line feeds (yes, the FCC allows line feeds within a field) are converted to the four-character sequence: <LF>;
- Leading and trailing spaces are removed;
- If the field is a date, it is converted from FCC format (mm/dd/yyyy) to ISO 8601 extended format: YYYY-MM-DD.
The full source code to generate the output file may be downloaded here.
To create the binary from the source code, go to the directory that contains the makefile and type:
make fcc-dbThis should generate the executable program as: bin/fcc-db. The program may be executed from within the bin directory as:
fcc-db [directory]where [directory] is the name of the directory that contains the input FCC AM.dat, CO.dat, EN.dat and HD.dat files. Those files should be processed and the output written to stdout.
For what it's worth, it takes somewhat less than 15 seconds for the program to execute to completion on my desktop computer if stdout is redirected to an output file.
