Roland's blog

Yesterday I had to import some 80,000 records into 4 different tables. The data was "|" delimited and formatted almost properly. My first instinct was to use queries to insert all records in the database. I cleaned up the data and created the appropriate mappings into the database fields.

INSERT statements took some 5-10 minutes for all the records. I used PHP for the task and the restrictions on PHP scripts to execute within a certain amount of time was very annoying at first. Using the Command Line Interface I was able to get all the data in.

I figured, there has to be a better way to do things. So I researched data loading in MYSQL: http://dev.mysql.com/doc/refman/5.0/en/load-data.html

Here's how you import the data:

LOAD DATA INFILE '/tmp/data.txt' INTO TABLE test
  FIELDS TERMINATED BY '|'  LINES TERMINATED BY '\n';

Huhuh!!! The execution time was much much faster 1-3 seconds at the most. Lesson learned!

For a project I was trying to figure out all the Counties in each state in the United States.
First I got a list at http://www.census.gov/datamap/fipslist/AllSt.txt.

The data is not very well formatted so I wrote a ruby script to import the data. According to this site http://www.usgs.gov/faq/list_faq_by_category/get_answer.asp?id=785 There are "3,141 counties and county equivalents in the 50 States and the District of Columbia". Well the list above is not very accurate if this is the case. I was able to get find parse out only 2954 counties. The Census site says the data was collected in January 1, 1990. Hmm, that's really out of date information. However, I am posting the code that I wrote to parse the file. You will need a mysql database and activerecord to run the script.

Download: counties_in_states.zip.