MySQL: Quickly Load a CSV file into a Database Table Tips, Tricks and Tutorials 17 FEB 2014

20090107_MySQL_Logo2CSV (comma separated value) files are often used as a way to transfer data between two different systems – it’s a simple, verbose, and difficult to break (if you double quote every single string of course) way of saving grouped data into a flat text file.

Pleasingly, MySQL comes with a command line tool that makes it a snap to load a CSV file directly into a database table, namely mysqlimport – a utility described at best as a client that provides a command-line interface to the handy LOAD DATA INFILE SQL statement.

Generally you invoke mysqlimport pretty much like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file’s contents. For example, files named ‘patient.csv’, ‘patient.txt’, and ‘patient’ all would be imported into a table named patient.

In addition to this basic call, you general tweak things via various settable options, for example specifying the user account and password to make use of, how the tool should break up values and lines, etc.

It is important to note that this is a relatively inflexible tool and as such requires good data inputs, and a pretty much near match in terms of available table columns versus the comma separated values.

This utility is a great way to automate data imports via the cron (or scheduled task handler), with my bash files often executing lines that look an awfully lot like this:

mysqlimport -u Myusername -pMypassword  --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --local Mydatabase datatoimport.csv

In other words, mysqlimport is a great way of grabbing CSV data and dumping it into your database fast!

Related Link: mysqlimport

About Craig Lotter

Software developer, husband and dad to two little girls. Writer behind An Exploring South African. I don't have time for myself any more.