Without this EN.dat file you're asking us to speculate on a few points.

However, what immediately catches my eye is that first field, record_type. That's a good sign of a single flat table that's trying to do the job of multiple tables interlinked by some relation, probably this unique_system_identifier value.

What then follows from that is this guess: your SELECT statement should include a WHERE record_type='EN' or similar, to filter out all of the other record types.

Once you sort these initial confusions out, if you're going to use this data long-term, I'd do some serious refactoring on the data to get it into normal form.

The CSV file you're downloading has a format error which is preventing SQLite from being able to import it all.

Here's an example line from the file:

EN|261932|||K7SAS|L|L00171357|SCHURMAN SR, ROBERT W|ROBERT|W|SCHURMAN|SR||||1175 1/2 "D" ST|BAKER CITY|OR|97814| ||000|0002101574|I||||||

You can't do this in a file in CSV format. Double quotes are used to delimit text strings. You can't use them inside strings unless you double them. So that line should really look like this:

EN|261932|||K7SAS|L|L00171357|SCHURMAN SR, ROBERT W|ROBERT|W|SCHURMAN|SR||||1175 1/2 ""D"" ST|BAKER CITY|OR|97814| ||000|0002101574|I||||||

But CSV files use commas, not pipes, so it should really look more like

EN,261932,,,K7SAS,L,L00171357,SCHURMAN SR, ROBERT W,ROBERT,W,SCHURMAN,SR,,,,1175 1/2 ""D"" ST,BAKER CITY,OR,97814, ,,000,0002101574,I,,,,,,

But some of those commas are inside the fields, not delimiters, so this particular line should look more like

EN,261932,,,K7SAS,L,L00171357,"SCHURMAN SR, ROBERT W",ROBERT,W,SCHURMAN,SR,,,,1175 1/2 ""D"" ST,BAKER CITY,OR,97814, ,,000,0002101574,I,,,,,,

I think you would need to find some way to make these global changes to the file before you proceed. It's possible that there's some combination of settings for the SQLite tool which allows importing without it, but I don't know one.

I suspect you would find it simplest to import your data from this file into a SQLite database by writing your own program, than to use the command-line tool.

What would help you make more of a better guess of what's going on?

The data can be downloaded here : ftp://wirelessftp.fcc.gov/pub/uls/complete/l_amat.zip. It's about 250MB in size.

All of the records that I imported have EN as the record_type.

Kyle

I don't think I changed my mode to CSV when importing the EN.dat file. I've imported CSV files in the past and I've had to change the mode to CSV before importing.

The problem I have is this database is 1.4 million lines long. Any program (that I know of) just doesn't cut it with importing something like that and converting the pipes to commas.

Kyle

I think you would need to find some way to make these global changes to the file before you proceed.

Any decent stream editor (sed or perl) can do that. Here's a line of sed which fully quotes all of the records, double-quoting any existing quote characters:

$ sed -e 's/\r//g' -e 's/"/""/g' -e 's/|/","/g' -e 's/^/"/g' -e 's/$/"/'  < EN.dat > _en.dat
$ grep 'SCHURMAN SR' _en.dat 
"EN","261932","","","K7SAS","L","L00171357","SCHURMAN SR, ROBERT W","ROBERT","W","SCHURMAN","SR","","","","1175 1/2 ""D"" ST","BAKER CITY","OR","97814"," ","","000","0002101574","I","","","","","",""
...

The initial \r removal is needed with GNU sed, else it apparently doesn't recognize \r\n as the end-of-line for purposes of the $ character.

(Ach - it looks like Warren has responded while i was writing this, but i'll go ahead and post it for completeness's sake.)

Any program (that I know of) just doesn't cut it with importing something like that

Such files are trivial to slice up from the CLI. e.g. to split them into 100k-line chunks using the bash shell and sed:

$ x=1; y=1; chunk=100000; while sed -n $x,$((x + $chunk))p EN.dat > EN_$y.dat; do test -s EN_$y.dat || break; x=$((x + $chunk + 1)); y=$((y + 1)); done

$ wc -l EN.dat 
1505803 EN.dat
$ wc -l EN_*.dat 
   100001 EN_10.dat
   100001 EN_11.dat
   100001 EN_12.dat
   100001 EN_13.dat
   100001 EN_14.dat
   100001 EN_15.dat
     5788 EN_16.dat
        0 EN_17.dat
   100001 EN_1.dat
   100001 EN_2.dat
   100001 EN_3.dat
   100001 EN_4.dat
   100001 EN_5.dat
   100001 EN_6.dat
   100001 EN_7.dat
   100001 EN_8.dat
   100001 EN_9.dat
  1505803 total

That 0-sized file at the end will always exist using this simple automation - simply remove it before importing the chunks.

EDIT: fixed off-by-one error which caused each successive group to start with the last line of the previous group. Now the line counts match up.

The data file is fine, it is just in a very weird format.

It uses | as the field separator but does not do quoting or escaping according to any commonly used method of quoting or escaping.

For example, the record:

EN|4350131|||KM0BUL|L|L02375253|Bulla 495, Kerry "|Kerry|"|Bulla|495||||5835 Best Rd|Larkspur|CO|80118|80118|Kerry "Mark" Bulla|000|0029933348|I||||||

It requires a custom parser written according to whatever rules apply for the data file format since it is non-standard.

If you read the file, split records on \n and fields on | then rewrite as a properly formatted csv it works just fine. (Save that it has fubar termination, but that is ok).

Python 3.9.4 (tags/v3.9.4:1f2e308, Apr  6 2021, 13:40:21) [MSC v.1928 64 bit (AMD64)] on win32
Type "help", "copyright", "credits" or "license" for more information.
>>> import csv
>>> f = open('en.dat', 'r')
>>> data = f.read().split('\n')
>>> f.close()
>>> data = [row.split('|') for row in data]
>>> f = open('en.csv', 'w')
>>> c = csv.writer(f)
>>> c.writerows(data)
>>> f.close()
>>> quit()

If you want to do it row-by-each rather than processing the whole file in RAM at once the following should do that:

>>> import csv
>>> fi = open('en.dat', 'r')
>>> fo = open('en.csv', 'w')
>>> c = csv.writer(fo)
>>> c.writerows(row.strip('\n').split('|') for row in fi)
>>> fi.close()
>>> fo.close()

$ man 1 split

😁

Hey Warren, thanks for the info and the reply. I'm a bit over my head here but thanks for hanging with me.

Here is what I've gotten so far. I had to install some packages as I didn't have any python libraries installed. I also had to install some other dev libraries to get the csvs-to-sqlite to work as it was throwing an error. Had to issue this command sudo apt-get install libatlas-base-dev to get csvs-to-sqlite to work after installing.

sudo apt-get install python3-pip (which installed a ton of dependencies).

sudo pip3 install --user csvs-to-sqlite which installed in /.local/bin

sudo apt-get install dos2unix (installed fine)

echo to > fcc.tsv completed correctly

csvs-to-sqlite fcc.tsv fcc.db -s $'t' has some errors as below;

pi@raspberrypi:~ $./.local/bin/csvs-to-sqlite fcc.tsv fcc.db -s $'t' /root/.local/lib/python3.7/site-packages/click/core.py:1066: DtypeWarning: Columns (12,18,21) have mixed types.Specify dtype option on import or set low_memory=False. return ctx.invoke(self.callback, **ctx.params) Loaded 1 dataframes Created fcc.db from 1 CSV file

Now the schema for the table looks a bit funky? Is this correct?

sqlite> .schema CREATE TABLE IF NOT EXISTS "fcc" ( "record_typeunique_system_identifieruls_file_numberebf_numbercall_signentity_typelicensee_identity_namefirst_namemilast_namesuffixphonefaxemailstreet_addresscitystatezip_codepo_boxattention_linesginfrnapplicant_type_codeapplicant_type_otherstatus_codestatus_datelic_category_codelinked_license_idlinked_callsign" INTEGER ); sqlite>

When I import the EN.dat (.import EN.dat fcc) not in CSV mode, I get the following error while it increments up. I stopped the import, this was the last line.

EN.dat:578088: expected 1 columns but found 30 - extras ignored

Thanks Kyle

You aren’t copying or pasting the hard tabs in the third command. It’s a single 30-element tab-separated line, repeating your field list from your initial post.

The single quotes should preserve the tabs, but perhaps they aren’t being pasted into your terminal properly.

Worst case, you can construct the line manually.

I think this is the ticket! This worked!