MySQL: loading data from an external file

Path of least resistance

When you need to get something done quickly, the community edition of MySQL is a great option.   It has just what you need in terms of a management studio and requires very little setup.

Yesterday I was in that exact circumstance.  I have some data files that I need to analyze and not much time to do it.  So I downloaded the community edition of MySQL (found here).

Data time!

As I said, I needed to get some analysis done quickly, so I created a few tables with your basic ANSI  create table statements and I was ready to load my data.

I was loading data from delimited sources not located in the default MySQL data directory so I had to include a few wrinkles in my load data statement. By the way, load data is the command for loading data from a file into a MySQL table.

The wrinkles

Since I was loading data from an external file I had to include the following statement in my load data statement:
 Load data LOCAL INFILE 'INSERT FILE PATH HERE'
I was using a comma delimited file so I had to include the following statement as well:
FIELDS TERMINATED BY ','
And finally, I had column headers in external file so the following statement was necessary:
IGNORE 1 LINES
The load script, when complete, looked like this:
Use SCHEMA NAME;
Load data LOCAL INFILE Load data LOCAL INFILE 'INSERT FILE PATH HERE'
into table TARGET TABLE HERE
FIELDS TERMINATED BY ','
IGNORE 1 LINES;

Nothing too magical, but I wanted to pass it along and, hopefully, save some of you the time in looking up the statements.

More information on MySQL load data parameters can be found here