~cg/uls2sqlite

Convert an FCC ULS Database to an SQLite Database
91be8578 — Chris Goff 8 months ago
Updated README with repository name in lowercase and formatting fixes.
369e92af — Chris Goff 9 months ago
Fixed formatting.
5d2a36dc — Chris Goff 9 months ago
Initial commit.

refs

master
browse  log 

clone

read-only
https://git.sr.ht/~cg/uls2sqlite
read/write
git@git.sr.ht:~cg/uls2sqlite

You can also use your local clone with git send-email.

#uls2sqlite

This tool is designed to convert an FCC Universal Licensing System (ULS) database into an SQLite database.

#Requirements

  • Bash (or compatible shell) for helper tools
  • Python 3.9 or later
  • sqlite-utils Python package
  • FCC ULS Database files
  • curl (if using the download_weekly_dbs.sh helper tool)

uls2sqlite has been tested on Fedora Linux 37, but may be portable to other operating systems.

#Installation

git clone https://git.sr.ht/~cg/uls2sqlite
cd uls2sqlite/
pip install -r requirements.txt

#How to Use

This tool will process .DAT files from a daily or weekly download from the FCC website, or take a single file from a geographical data export.

Command-line arguments to specify input and output files. Use the -i or --import_file flag to specify the ULS file to be imported, and the -o or --output_file flag to specify the SQLite database filename to export to. If no SQLite database exists, it will be created.

Example using ULS .DAT file as input:

python uls2sqlite.py -i HD.dat -o output.sqlite --encoding=utf-8

Note search export files are also pipe-delimited, however they are a single file containing many ULS records. There is a helper tool included, uls_splitter.py, which can be run against these files to split them into separate pipe-delimited files.

Example using a single ULS file as input:

cd tools
python uls_splitter.py -i UL20236291542491.txt
cd ..
bash process_pipe_files.sh databases/my_database.sqlite

This will split the UL20236291542491.txt file into separate pipe-delimited files, and process each of them into the my_database.sqlite SQLite database located in the databases directory.

There is also a helper file, process_zip_files.sh, which can be used to process ZIP files downloaded from the FCC website located in the archives directory. This will process all the files for you in an automated manner.

Example using ZIP files downloaded to the archives directory:

bash process_zip_files.sh

#Helper Tools

There are a number of helper tools included to assist with the conversion process.

#process_zip_files.sh

Processes batches of ZIP files downloaded from the FCC website and converts them into SQLite databases.

Usage:

bash process_zip_files.sh

Note: If GNU Parallel is installed on your system, the script will attempt to process ZIP files in parallel for a massive speed boost. Be aware this can consume considerable system resources!

#process_pipe_files.sh

Processes a single pipe-delimited file downloaded from the FCC website and converts it into an SQLite database.

Processes a single pipe-delimited ULS database to a SQLite database. Run uls_splitter.py first to generate the necessary pipe-delimited files.

Usage:

bash process_pipe_files.sh output.sqlite
#tools/download_weekly_dbs.sh

Downloads the weekly ULS databases from the URLs in weekly_dbs_urls.txt.

#tools/ready_dat_files.py

Ready the .DAT files for import by making sure filenames are correct.

#tools/uls_splitter.py

If a combined ULS database is being used, split it into separate pipe-delimited files by record type. Files are placed in the temp directory.

Usage:

python uls_splitter.py -i UL20236291542491.txt
#tools/weekly_dbs_urls.txt

Contains list of URLs for weekly ULS databases provided by the FCC.

#Resources

ULS Databases:
https://www.fcc.gov/wireless/data/public-access-files-database-downloads

ULS definitions SQL file:
https://www.fcc.gov/sites/default/files/public_access_database_definitions_sql_v6_0_0.txt

ULS record types
https://www.fcc.gov/sites/default/files/pubacc_intro_11122014.pdf

ULS definitions:
https://www.fcc.gov/sites/default/files/uls_code_definitions_07182022.txt