FCC Database Downloads + SQL Developer?

[CanesFan95]

Welcome to the world of ETL; you can find a whole career in this area. Import-export from text files is not entirely straightforward in SQL Server, even the paid version.

The Option 3 that wyShack recommended is not all that difficult by using Windows PowerShell. I wrote a small script that takes the DAT file and converts it into the necessary INSERT statement to insert the records into the table (find it here: https://gist.github.com/mattstrom/e12519e10763c4b0e56b07664e480812).

Example:

INSERT INTO vs_market (record_type,content_indicator,file_number,registration_number,unique_system_identifier,entity_type,entity_type_code,entity_type_other,licensee_id,entity_name,first_name,mi,last_name,suffix,phone,fax,internet_address,street_a
ddress,street_address2,po_box,city,state,zip_code,attention,frn) VALUES 
('EN','1603679','','',WPLM204,'L','L01097442','NPCR, Inc.','','','','','','','','','','','','','','','','',''),
('EN','1603680','','',WPLM204,'L','L01097442','NPCR, Inc.','','','','','','','','','','','','','','','','',''),
('EN','1603681','','',WPLM204,'L','L01097442','NPCR, Inc.','','','','','','','','','','','','','','','','','');

WARNING: PowerShell scripts have the ability to make serious modifications to your system. You should always be cautious when using scripts from untrusted sources. Understand the code in the script before running it.

Your feedback is appreciated (and everyone else's fas well). This is going to be a project that I'll slowly work on as time allows during the weekends. I got MySQL Workbench installed and running and was able to create the a3 table and import the a3.dat file data into it. Problem is, next time I opened MySQL Workbench, it gave some error about not being able to connect and I couldn't get back to where I was before. Why is saving a database on a C:\ drive so hard? I don't want to "connect" to anything, but MySQL Workbench seems to want all kinds of passwords and directories and server locations and yada yada. Geez.

Well, I'm not sure what "ETL" means, but I think the difficulty with that code example you've got there is that you'd have to manually type out every line of data from all the .dat files, as opposed to doing an automated import. I'm looking to run something more like this:

LOAD DATA LOCAL INFILE 'C:/FCC Data Land Mobile - Private/a3.dat'
INTO TABLE PUBACC_A3
FIELDS TERMINATED BY '|' /* Field delimiter */
LINES TERMINATED BY '\n'; /* EOL character */

 

[CanesFan95]

Someone has been parsing the FCC database for awhile now:

Quick Scanner Links

and this link should be handy:

Using the raw FCC data

Marshall KE4ZNR

I think the data on that first link only shows license grants for the current week. It does not give a list of all existing licensees (and related data). And you can't break it down by county, city, zip code, etc. This is why I want to do my own database from scratch. To tweak the data the way I want it. The second link there has no actual technical information as to how to setup the database. I just has a few basic sentences that are less in depth than this thread.

 

[CanesFan95]

Message #15

Thanks. I went ahead and put in a request to join that list. Until then, it won't let me see it. ("Oops! You need to be a member to perform this action.")

 

[CanesFan95]

If I sort the .dat files for the download that the FCC website calls "Land Mobile - Private" by file size, it looks like the largest .dat file is 368 Mb which will fit under the 2 Gb limit of Access 2007. Two snags with Access 2007 that I ran into are:

1. The SQL syntax for creating numeric fields in a table is different. The data type is called 'number' instead of 'numeric(X,Y)'. So, you can't just easily copy and paste the create table statements off the FCC website without having to manually edit them. And I can't figure out how you tell Access to use a certain number of decimal places like you can with 'numeric(X,Y)'. The 'numeric(X,Y)' causes a syntax error in Access.

2. The FCC files end in .dat with each field being pipe | delimited. These files look and act just like .txt files, except they end in .dat. But Access 2007 doesn't seem to have an option to import pipe | delimted data. It can do others like .csv, but not the pipe character | as the delimeter.

 

[CanesFan95]

Thanks. I went ahead and put in a request to join that list. Until then, it won't let me see it. ("Oops! You need to be a member to perform this action.")

Well, I just got approved and looked at that link. It points to the same page with the weekly grants. Not a complete listing of all existing frequencies for each county with the transmit location, licensee name, emission type, etc. like I'm shooting for. If you're searching around on your scanner, say, 460 - 465 MHz, the scanner stops on a frequency, and you wanna see what that frequency is, that info won't work.

Yes, you could do an FCC Geo search, but it's slow and time-consuming to have to drill into each result, and sometimes you have to re-run your search for different counties. What I'm shooting for is a complete listing saved in a .csv or .xslx that I can Ctrl + F and search around in. The only way (I think) is to run your own SQL queries on the raw data .dat's with table joins and create your own .csv's or .xslx's.

 

[Voyager]

2. The FCC files end in .dat with each field being pipe | delimited. These files look and act just like .txt files, except they end in .dat. But Access 2007 doesn't seem to have an option to import pipe | delimted data. It can do others like .csv, but not the pipe character | as the delimeter.

I just change .DAT to .TXT - problem 1 solved.

ACCESS will let you specify the delimiter for import. Specify it as the pipe and it will import fine. - problem 2 solved (unless 2007 broke that capability)

 

[KK6WTT]

If I sort the .dat files for the download that the FCC website calls "Land Mobile - Private" by file size, it looks like the largest .dat file is 368 Mb which will fit under the 2 Gb limit of Access 2007. Two snags with Access 2007 that I ran into are:

1. The SQL syntax for creating numeric fields in a table is different. The data type is called 'number' instead of 'numeric(X,Y)'. So, you can't just easily copy and paste the create table statements off the FCC website without having to manually edit them. And I can't figure out how you tell Access to use a certain number of decimal places like you can with 'numeric(X,Y)'. The 'numeric(X,Y)' causes a syntax error in Access.

2. The FCC files end in .dat with each field being pipe | delimited. These files look and act just like .txt files, except they end in .dat. But Access 2007 doesn't seem to have an option to import pipe | delimted data. It can do others like .csv, but not the pipe character | as the delimeter.

PowerShell to the rescue again.

For item 1, you can replace strings in a text file with the following:

Get-Content -Path file.dat | %{ $_ -replace "numeric\(\d+, ?\d+\)", "number" } | Set-Content file.new.dat

For item 2, you can effortlessly convert pipe-delimited files into tab-delimited files (comma-separated files are a bit more complicated because the data contains commas in its values).

Import-Csv -Path file.dat -Delimiter `| | Export-Csv -Path file.csv

If you want to do it for all DAT files, you can use something like this:

Get-ChildItem -Path . -Filter "*.dat" -Recurse | %{
    Import-Csv -Delimiter `| -Path $_.FullName | Export-Csv -Path ($_.FullName -replace ".dat$", ".csv")
}

Also, ETL stands for Extract, Transform, and Load, the process of importing and exporting database between data sources.

 

[natedawg1604]

This is a wonderful concept. Once you import all the data files into a SQL database (no small task!!), you can run tons of sophisticated queries, which would be impossible to get from the ULS Web API. For example, you can run a search query filtered by Emission Designator, among many other things. I did this a while back with a local Postgresql database, it was somewhat time consuming.

As you likely discovered, the raw data files are a MAJOR pain to work with. They are quite obviously generated from an ancient steam-powered database platform, and some files contain numerous random rows of garbage records (i.e. errant data which violates the type constraint of the corresponding field). Once you get the data formatted correctly for import, you then import the final data files into "staging tables" in your SQL database of choice. Then, you can manipulate/join/merge/copy etc. the staging tables into a final set of usable tables which you will query against.

Once you get everything cleaned-up and imported you can run a LOT of fancy queries, which are wayyyy beyond the capabilities of the ULS Web API (and much, much, much faster). Finally, I would reiterate the earlier warnings re: Access - don't waste your time. For this project you need a real SQL database.

 

[CanesFan95]

I am trying both MySQL and Access for now. I submitted an inquiry to the FCC to ask which table has the transmitter location data (rather than just the licensee's mailing address). They confirmed it's in the LO table. They also said that for what I'm trying to to do, the best data set to download off their website is the one under "Land Mobile - Private".

So here's a question for the group about Access 2007. When I try to create a table in Access, some of the fields are numeric with decimal places. For example:

create table PUBACC_LO
(
record_type char(2) not null,
unique_system_identifier numeric(9,0) not null,
uls_file_number char(14) null,

<SNIP>.

The numeric part doesn't work, so I changed it to:

create table PUBACC_LO
(
record_type char(2) not null,
unique_system_identifier decimal (9,0) not null)

<SNIP>,

but that doesn't work either. If I use the word number without the (9,0) part, it works. But if you try to use number(9,0) or number (9,0), it doesn't work.

Does anyone know what the syntax is to tell Access to create a numeric field and to specify the number of decimal places?

Thanks.

 

[CanesFan95]

I managed to create a couple of tables in MySQL. But when I try to load the data into the LO table, I get the following error and no data rows load into it:

Error Code: 1300. Invalid utf8 character string: 'Calle Luis Mu'

 

[CanesFan95]

This is a wonderful concept. Once you import all the data files into a SQL database (no small task!!), you can run tons of sophisticated queries, which would be impossible to get from the ULS Web API. For example, you can run a search query filtered by Emission Designator, among many other things...

Once you get everything cleaned-up and imported you can run a LOT of fancy queries, which are wayyyy beyond the capabilities of the ULS Web API (and much, much, much faster).

Exactly! I think you actually understand how great this would be if only I can figure it all out!

 

[natedawg1604]

I managed to create a couple of tables in MySQL. But when I try to load the data into the LO table, I get the following error and no data rows load into it:

Error Code: 1300. Invalid utf8 character string: 'Calle Luis Mu'

I'm now remembering how awful these files are. Looking at my notes, it appears you have to change the character encoding of the files from US-ASCII to UTF-8. Here's an example of how I did it (from the linux command line, I have no clue how to do this in Windows):

iconv -c -f US-ASCII -t utf-8 MK.dat > MK.txt

But the fun doesn't stop there! I also had to replace the newline character in a bunch of the files, here's another example (again, I opened the file in VI from the Linux Command line):

vi :%s/^M//g (^M=Ctrl+v Ctrl+m)

Hopefully you can find an easy way to carry out these tasks in Windows, maybe someone else can offer suggestions. I have no clue why it's necessary to making these seemingly arcane formatting changes, but such is life...

 

[KK6WTT]

So here's a question for the group about Access 2007. When I try to create a table in Access, some of the fields are numeric with decimal places. For example:

create table PUBACC_LO
(
record_type char(2) not null,
unique_system_identifier numeric(9,0) not null,
uls_file_number char(14) null,

<SNIP>.

The numeric part doesn't work, so I changed it to:

create table PUBACC_LO
(
record_type char(2) not null,
unique_system_identifier decimal (9,0) not null)

<SNIP>,

but that doesn't work either. If I use the word number without the (9,0) part, it works. But if you try to use number(9,0) or number (9,0), it doesn't work.

Does anyone know what the syntax is to tell Access to create a numeric field and to specify the number of decimal places?

Thanks.

The numeric types are a bit trickier in Access. Access does not allow you to define the precision of floating point numbers. You're limited to SINGLE and DOUBLE precision. SINGLE should suffice for most things. Also see https://msdn.microsoft.com/en-us/library/ms714540(v=vs.85).aspx

 

[CanesFan95]

Well that sounds like an unfortunate (and silly) limitation. So, I'm not understanding what SINGLE and DOUBLE actually mean. Does that mean you can specify either one or 2 decimal places? I'm thinking another more time-consuming option might be to manually set the field parameters in the design view after creating the tables.

 

[CanesFan95]

Well, it looks like Access 2007 is officially out of the running. The Access 2007 file type is .accdb and it evidently has an overall file size limit of 2 Gb. I changed the file extension from .dat to .txt on a few files and then Access 2007 lets you import the .txt's with an option to specify the delimiter as |.

So I created 3 tables in Access 2007 and was able to successfully import the data into 2 of them. But once the file size got to 1.99 Gb and I tried to import data into the third table, bam, error message. So Access 2007 is done now and maybe it's on to PostgreSQL to see if that will work.

 

[blantonl]

Here is how we load the FCC data into MySQL. Note the PERL regular expression fixes to fix common problems in the raw text data.

foreach ($load as $toLoad) {
	if ($toLoad == "coast") {
		$filesToLoad = array("EM","EN","LO","FR","HD");
	}
	elseif ($toLoad == "bcast") {
		$filesToLoad = array("EN","HD");
	}
	else {
		$filesToLoad = array("EM","EN","LO","FR","HD","LM");
	}
	foreach ($filesToLoad as $fileToLoad) {
		if ($toLoad == "coast" && $fileToLoad == "LO") {
			shell_exec("perl -i -pe 's/\r\n/\n/g' ".$fccLoadDirs["$toLoad"]."/LO.dat");
			shell_exec("perl -i -pe 's/\r\n//g' ".$fccLoadDirs["$toLoad"]."/LO.dat");	
			shell_exec("perl -i -pe 's/\r\r//g' ".$fccLoadDirs["$toLoad"]."/LO.dat");
			shell_exec("perl -i -pe 's/\r/\r\n/g' ".$fccLoadDirs["$toLoad"]."/LO.dat");
		}
		$result = mysql_query("LOAD DATA LOCAL INFILE '".$fccLoadDirs["$toLoad"]."/".$fileToLoad.".dat' INTO TABLE $fileToLoad FIELDS TERMINATED BY '|'") or die ("Error->" . mysql_error());
	}
}

You'll then need to index all your columns that you plan to query against.

 

[blantonl]

And here is the MySQL schema that I wrote many years ago...

drop table if exists CO;
create table CO
(
	record_type               char(2)              not null,
	unique_system_identifier  decimal(9,0)         not null,
	uls_file_num              char(14)             null,
	callsign                  char(10)             null,
	date                      char(10)             null,
	description               varchar(255)         null
);

drop table if exists EM;
create table EM
(
	record_type               char(2)              null,
	unique_system_identifier  decimal(9,0)         not null,
	uls_file_number           char(14)             null,
	ebf_number                varchar(30)          null,
	call_sign                 char(10)             null,
	location_number           int                  null,
	antenna_number            int                  null,
	frequency_assigned        decimal(16,8)        null,
	emission_action_performed char(1)              null,
	emission_code             char(10)             null,
	digital_mod_rate          decimal(8,1)         null,
	digital_mod_type          char(7)              null,
	frequency_number          int                  null
);

drop table if exists EN;
create table EN
(
	record_type               char(2)              not null,
	unique_system_identifier  decimal(9,0)         not null,
	uls_file_number           char(14)             null,
	ebf_number                varchar(30)          null,
	call_sign                 char(10)             null,
	entity_type               char(2)              null,
	licensee_id               char(9)              null,
	entity_name               varchar(200)         null,
	first_name                varchar(20)          null,
	mi                        char(1)              null,
	last_name                 varchar(20)          null,
	suffix                    char(3)              null,
	phone                     char(10)             null,
	fax                       char(10)             null,
	email                     varchar(50)          null,
	street_address            varchar(60)          null,
	city                      varchar(20)          null,
	state                     char(2)              null,
	zip_code                  char(9)              null,
	po_box                    varchar(20)          null,
	attention_line            varchar(35)          null,
	sgin                      char(3)              null,
	frn                       char(10)             null
);
drop table if exists FR;
create table FR
(
	record_type               char(2)              null,
	unique_system_identifier  decimal(9,0)         not null,
	uls_file_number           char(14)             null,
	ebf_number                varchar(30)          null,
	call_sign                 char(10)             null,
	frequency_action_performed char(1)             null,
	location_number           int                  null,
	antenna_number            int                  null,
	class_station_code        char(4)              null,
	op_altitude_code          char(2)              null,
	frequency_assigned        decimal(16,8)        null,
	frequency_upper_band      decimal(16,8)        null,
	frequency_carrier         decimal(16,8)        null,
	time_begin_operations     int                  null,
	time_end_operations       int                  null,
	power_output              decimal(15,3)        null,
	power_erp                 decimal(15,3)        null,
	tolerance                 decimal(6,5)         null,
	frequency_ind             char(1)              null,
	status                    char(1)              null,
	eirp                      decimal(7,1)         null,
	transmitter_make          varchar(25)          null,
	transmitter_model         varchar(25)          null,
	auto_transmitter_power_control char(1)         null,
	cnt_mobile_units          int                  null,
	cnt_mob_pagers            int                  null,
	freq_seq_id               int                  null
);
drop table if exists HD;
create table HD
(
	record_type               char(2)              not null,
	unique_system_identifier  decimal(9,0)         not null,
	uls_file_number           char(14)             null,
	ebf_number                varchar(30)          null,
	call_sign                 char(10)             null,
	license_status            char(1)              null,
	radio_service_code        char(2)              null,
	grant_date                char(10)             null,
	expired_date              char(10)             null,
	cancellation_date         char(10)             null,
	eligibility_rule_num      char(10)             null,
	applicant_type_code       char(1)              null,
	alien                     char(1)              null,
	alien_government          char(1)              null,
	alien_corporation         char(1)              null,
	alien_officer             char(1)              null,
	alien_control             char(1)              null,
	revoked                   char(1)              null,
	convicted                 char(1)              null,
	adjudged                  char(1)              null,
	involved                  char(1)              null,
	common_carrier            char(1)              null,
	non_common_carrier        char(1)              null,
	private_comm              char(1)              null,
	fixed                     char(1)              null,
 	mobile                    char(1)              null,
	radiolocation             char(1)              null,
	satellite                 char(1)              null,
	developmental_or_sta      char(1)              null,
	interconnected_service    char(1)              null,
	certifier_first_name      varchar(20)          null,
	certifier_mi              char(1)              null,
	certifier_last_name       varchar(20)          null,
	certifier_suffix          char(3)              null,
	certifier_title           char(40)             null,
	gender                    char(1)              null,
	african_american          char(1)              null,
	native_american           char(1)              null,
	hawaiian                  char(1)              null,
	asian                     char(1)              null,
	white                     char(1)              null,
	ethnicity                 char(1)              null,
	effective_date            char(10)             null,
	last_action_date          char(10)             null,
	auction_id                decimal(9,0)         null,
	reg_stat_broad_serv       char(1)              null,
	band_manager              char(1)              null,
	type_serv_broad_serv      char(1)              null
);
drop table if exists LO;
create table LO
(
	record_type               char(2)              not null,
	unique_system_identifier  decimal(9,0)         not null,
	uls_file_number           char(14)             null,
	ebf_number                varchar(30)          null,
	call_sign                 char(10)             null,
	location_action_performed char(1)              null,
	location_type_code        char(1)              null,
	location_class_code       char(1)              null,
	location_number           int                  null,
	site_status               char(1)              null,
	corresponding_fixed_location decimal(9,0)      null,
	location_address          varchar(80)          null,
	location_city             char(20)             null,
	location_county           varchar(60)          null,
	location_state            char(2)              null,
	radius_of_operation       decimal(5,1)         null,
	area_of_operation_code    char(1)              null,
	clearance_indicator       char(1)              null,
	ground_elevation          decimal(7,1)         null,
	lat_degrees               int                  null,
	lat_minutes               int                  null,
	lat_seconds               decimal(3,1)         null,
	lat_direction             char(1)              null,
	long_degrees              int                  null,
	long_minutes              int                  null,
	long_seconds              decimal(3,1)         null,
	long_direction            char(1)              null,
	max_lat_degrees           int                  null,
	max_lat_minutes           int                  null,
	max_lat_seconds           decimal(3,1)         null,
	max_lat_direction         char(1)              null,
	max_long_degrees          int                  null,
	max_long_minutes          int                  null,
	max_long_seconds          decimal(3,1)         null,
	max_long_direction        char(1)              null,
	nepa                      char(1)              null,
	quiet_zone_notification_date char(10)          null,
	tower_registration_number char(10)             null,
	height_of_support_structure decimal(7,1)       null,
	overall_height_of_structure decimal(7,1)       null,
	structure_type            char(6)              null,
	airport_id                char(4)              null,
	location_name             char(20)             null,
	units_hand_held           int                  null,
	units_mobile              int                  null,
	units_temp_fixed          int                  null,
	units_aircraft            int                  null,
	units_itinerant           int                  null
);
drop table if exists LM;
create table LM
(
	record_type               char(2)              not null,
	unique_system_identifier  decimal(9,0)         null,
	uls_file_number           char(14)             null,
	ebf_number                varchar(30)          null,
	callsign                  char(10)             null,
	ext_implement_appr        char(1)              null,
	lm_eligibility_activity   varchar(255)         null
);

 

[KK6WTT]

So Access 2007 is done now and maybe it's on to PostgreSQL to see if that will work.

I imagine that you will encounter the same error with character encodings in PostgreSQL that you encountered in MySQL. Instead I would recommend configuring the CHARSET of the database to match that of the DAT files (who knows what that is, though). Or doing what @natedawg1604 suggested with iconv.exe

You may need to recreate the database to do so because the present encoding will be corrupted after changing the CHARSET.

See https://dev.mysql.com/doc/refman/5.7/en/charset-applications.html

 

[CanesFan95]

I'm getting tripped up on MySQL. What is the difference between "MySQL" and "MySQL Workbench"? Are these not the same software program? I've been using MySQL Workbench, but the problem is, when I come back the next day, boot up the computer, and open MySQL Workbench, it always says it can't connect to the database that I created. Yesterday, I uninstalled it, re-installed it, and re-created the database and got it working again. Now today, same thing, can't connect. It was JUST working last night. Dang it! So what is thing doing???? All I want to do is save a database on the C:\ drive and then open it later. I'm not trying to "connect" to anything.

 

[KK6WTT]

I'm getting tripped up on MySQL. What is the difference between "MySQL" and "MySQL Workbench"? Are these not the same software program? I've been using MySQL Workbench, but the problem is, when I come back the next day, boot up the computer, and open MySQL Workbench, it always says it can't connect to the database that I created. Yesterday, I uninstalled it, re-installed it, and re-created the database and got it working again. Now today, same thing, can't connect. It was JUST working last night. Dang it! So what is thing doing???? All I want to do is save a database on the C:\ drive and then open it later. I'm not trying to "connect" to anything.

MySQL Workbench is a graphical client that serves as a tool to access the MySQL database server engine, which is usually an executable named mysqld.exe (the d stands for daemon) and which runs as a process in the background (i.e. it has no user interface). This process has to be running for you to be able to connect using Workbench.

The earliest way to run the server is to find myself.exe and run it at the command prompt and leave the prompt open. You can also configure it to run as a service that starts automatically with Windows, but that is a tad more complicated.