Loading CSV Files

To import CSV (comma separated values) files into mysql, use LOAD DATA LOCAL INFILE.

SQL Statements:

DROP TABLE IF EXISTS zips;

CREATE TABLE `zips` (
  `zip_id` int(11) NOT NULL auto_increment,
  `zip` varchar(5) default NULL,
  `state` varchar(2) default NULL,
  `lat` varchar(10) default NULL,
  `lon` varchar(10) default NULL,
  `city` varchar(50) default NULL,
  `statename` varchar(50) default NULL,
  PRIMARY KEY  (`zip_id`)
);

LOAD DATA LOCAL INFILE 'zips.csv'
INTO TABLE zips
FIELDS TERMINATED BY ', ' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(zip, state, lat, lon, city, statename);

Sample of zips.csv content:

"zip code", "state abbreviation", "latitude", "longitude", "city", "state"
"35004", "AL", " 33.606379", " -86.50249", "Moody", "Alabama"
"35005", "AL", " 33.592585", " -86.95969", "Adamsville", "Alabama"
"35006", "AL", " 33.451714", " -87.23957", "Adger", "Alabama"
"35007", "AL", " 33.232422", " -86.80871", "Alabaster", "Alabama"
"35010", "AL", " 32.903432", " -85.92669", "Alexander City", "Alabama"
"35014", "AL", " 33.355960", " -86.27720", "Alpine", "Alabama"
"35016", "AL", " 34.323715", " -86.49278", "Arab", "Alabama"
"35019", "AL", " 34.292540", " -86.63505", "Baileyton", "Alabama"
"35020", "AL", " 33.405559", " -86.95141", "Bessemer", "Alabama"