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.

31 Mar 2008

Link: MySQL CSV Import Statement

Need to blog about this...

10 Oct 2007

Anti-Definite

Just a quick post to highlight a MySQL function I’ve created that I’m finding to be very useful (perhaps others will as well).

I love it when sorting ignores prefixes of ‘The’ or ‘A’ – definite or indefinite articles, if you want the correct terms. To get this happening within a SQL SELECT request is a bit tricky, but inspired by someone else’s solution (Ok, so I pretty much copied their code), I’ve created the following function to make it a lot easier:

CREATE FUNCTION ANTIDEFINITE(field VARCHAR(1024))
RETURNS VARCHAR(1024) DETERMINISTIC
  RETURN CASE
    WHEN SUBSTRING_INDEX(field, ' ', 1) IN ('a', 'an', 'the')
      THEN CONCAT(SUBSTRING(field, INSTR(field, ' ') + 1),
      ', ', SUBSTRING_INDEX(field, ' ', 1))
    ELSE field
  END;

It works in MySQL 5.0 and 5.1 – I’m sure something similar could be concocted for Postgres.

To use, just call the function on whichever fields you want in your order clause, such as:

SELECT *
FROM posts
ORDER BY ANTIDEFINITE(subject);

Or, via Rails:

Post.find :all, :order => 'ANTIDEFINITE(subject)'

I’m not sold on the function name, though – it’s a bit long, but at least it’s clear. If you’ve got a better suggestion, let me know.

10 Oct 2007

Link: AS Workshop ยป How to sort MySQL results ignoring definite and indefinite articles (the, a, an)

A not-too-ugly method of sorting while ignoring 'The', 'A', etc

31 Jul 2007

Link: Sphinx - Free open-source SQL full-text search engine

07 May 2007

Link: top-secret tuned mysql configurations for rails :: evan weaver

26 Apr 2007

Link: The Exciter: Installing Lighttpd on OSX

Note the mysql flags for lighttpd so mod_mysql_vhost works

26 Apr 2007

Link: Hivelogic - The Narrative - Building Ruby, Rails, LightTPD, and MySQL on Tiger

Note the config settings in ./configure calls

21 Dec 2006

Link: Hivelogic: Articles: Building Ruby, Rails, LightTPD, and MySQL on Tiger

09 Oct 2006

Link: .Net Adventures : MySQL Tools for Visual Studio 1.0.1

Tags:

MySQL plugin for Visual Studio 2005

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.