Freelancing Gods 2014

God
01 Apr 2008

Fast CSV reading and writing with MySQL

I came across a handy way to import CSVs into MySQL the other day – something that’s probably useful to other people. Pure MySQL too.

First, grab your CSV file, and remove the header line (into the clipboard is recommended). Then open up MySQL via a shell of your choice, and type something like the following:

LOAD DATA LOCAL INFILE '/path/to/my_data.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column_a, column_b, column_c, column_d);

Obviously, put in the correct path to your csv file, the appropriate table name, and the columns in the same order as they are in the file (which is where your clipboard contents becomes useful). It is damn fast. I barely blinked and it was done on a 50,000 record file. For 150,000, it took all of 10 seconds.

Of course, knowing this, I figured there’d be an equivalent way to get the data out into CSV files as well. A quick search of MySQL’s site revealed the following:

SELECT column_a, column_b, column_c, column_d
INTO OUTFILE 'path/to/my_data.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name;

More details are in the documentation – for both importing and exporting.

RssSubscribe to the RSS feed

About Freelancing Gods

Freelancing Gods is written by , who works on the web as a web developer in Melbourne, Australia, specialising in Ruby on Rails.

In case you're wondering what the likely content here will be about (besides code), keep in mind that Pat is passionate about the internet, music, politics, comedy, bringing people together, and making a difference. And pancakes.

His ego isn't as bad as you may think. Honest.

Here's more than you ever wanted to know.

Ruby on Rails Projects

Other Sites

Creative Commons Logo All original content on this site is available through a Creative Commons by-nc-sa licence.