Dan and I are back from the MySQL for Developers training, and we decided to share the enlightenment. Instead of boring you with a summary of what we've learned, we've decided to divvy up our notes into various "tips & tricks" posts.

Let's say that a client sends you an Access, Excel, or CSV file and tells you to stick all that information into your database. The client is oblivious to the structure of your table, and the file contains several columns that aren't needed. Some of the other columns are improperly formatted, making your life that much harder.



Your first instinct might be to write a PHP (or Perl, or Python, etc) script that squeezes and trims data to the right dimensions, then sends to MySQL. That's fine, but many don't know that MySQL can usually do this tedius work for you. It might also be worth mentioning that MySQL parses this data exceedingly fast.

LOAD DATA INFILE (or LOAD LOCAL DATA INFILE from your local machine) is an importer's dream come true. It allows for MySQL itself to load and parse a file. Once loaded, you can set the FIELDS TERMINATED BY, ENCLOSED BY, LINES TERMINATED BY, and IGNORE ... LINES parameters to split the data into meaningful chunks.

Setup:

LOAD DATE INFILE '[file.ext]' INTO TABLE [table_name]
FIELDS TERMINATED BY '[column_separator]'
ENCLOSED BY '[usually doublequote]'
LINES TERMINATED BY '[usually \r or \n]'
IGNORE [lines_to_skip] LINES
(col1, @variable2, col3, col4)
SET col2 = SOME_FUNCTION(@variable2, some_parameters);

You can use @variables instead of columns for processing. If unneeded file columns exist, then we store that column into a dummy variable (look at @skip below).

The import file uses "Apr 20 1982" for the birthday, but we want it looking like "1982-04-20". Below, we set the @birthday variable equal to the file's birthday column, then SET birthday (the DB column) equal to STR_TO_DATE(@birthday, 'input_format').

Real-world usage:

LOAD DATA INFILE 'import.csv' INTO TABLE people
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 2 LINES
(id, @skip, @fname, @lname, @skip, @birthday)
SET name = CONCAT(@fname, ' ', $lname),
birthday = STR_TO_DATE(@birthday, '%b %e %Y');

NOTE: STR_TO_DATE expects the input date format to be specified. The 2nd parameter contains the curious string '%b %e %Y'. In this example, it's looking for a date formatted like 'Jan 9 2001'. The meaning of those special characters can be found here.

I would highly recommend attending the MySQL training seminars. David Swain, our trainer, was highly competent and had helpful answers and further insight to every question we could throw at him.