Importing Pilot Data from the FAA
We wanted to send out postcards to area pilots to encourage them to attend the EAA meetings that we have every third Sunday of the month. The more pilots we can get to attend, the better the quality of speaker that we can attract.
The FAA makes pilot names and addresses available in fixed format and csv format. It needs a little massaging before it is useable in my database of choice MySQL with phpMyAdmin as the import/viewing engine.
The first thing I did was to create the table and try to import a dozen lines from the file. There is a pdf on the FAA website that describes the file layout and field sizes so I just used that as my guide.
However, I got an error about incorrect number of fields when I tried to import. That is because they terminated each line with a comma. I removed them but ran into another problem when there was no medical info. So I put them back and then I changed a occurrences of ,,,, to ,NULL,NULL,NULL,. Then I removed the last comma in each line.
I got an error when I tried to import the whole database because PHP was not set up to import that file large.
I updated the /etc/php5/apache2php.ini file to account for the large size of the csv file. The file I was working with is 74 MB and my defaults are around 10 MB so it wouldn’t import.
I changed two lines
post_max_size = 210M
upload_max_filesize = 200M
and restarted Apache.
I still got an error trying to import line 495922 because the zip code had a comma in it. I fixed it and copied the rest of the file to a new file and it worked fine. I now have 571,598 records that I can use to print postcards from.