I ran across a couple of things when uploading data to our MySql database that kept me puzzled for a few hours. I was trying to load data from the FAA on airports. There are 19,755 airports in an xls file. Last month I converted the records to semi-colon delimited fields and imported them without a problem. This month I wanted to add a few more fields, so I thought I’d just create the new fields and import the data in the same step. I used
INSERT INTO `Airports_NFDC` (`id`, `NFDC_id`, `type`, `FAA_id`, `state`, `city`, `name`, `owner`, `access`, `lat`, `lon`, `elev`, `var`, `TPA`, `DistCBD`, `DirCBD`, `Activation`) VALUES
to insert the data. However, it appears that this takes forever and my query would time out without importing any records. (It could be that something else was causing the termination, but I managed to get 5,000 records to import, so I’m reasonably certain that it is a transaction time issue.)
I converted the data to CSV delimited by a ‘;’ and within a minute the file was uploaded. One glitch occurred. It gave me an error on the last record. The last record did not have an end-of-line marker and the importer didn’t know that an end-of-file must mean that the line had ended as well. Inserting a return after the last line fixed the problem.