Table of Contents

Loading CSV Files

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

LOAD DATA LOCAL INFILE Example

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"

set

User variables in the SET clause can be used in several ways. The following example uses the first input column directly for the value of t1.column1, and assigns the second input column to a user variable that is subjected to a division operation before being used for the value of t1.column2: 

LOAD DATA INFILE 'file.txt'
  INTO TABLE t1
  (column1, @var1)
  SET column2 = @var1/100;