Tag Archives: data import

How to use MySQL to Import a .sql file Tips, Tricks and Tutorials 05 JUL 2015

Quite often I like backing my databases up as .sql files, which then comes in handy for also moving databases around, or making copies of them. The question is, given your Ubuntu server and the mysql command, how do you import a .sql file?

Well first, you enter the mysql console in the usual manner:

mysql -u USERNAME -p

Once in, switch to the database that you want to run the SQL import against:

mysql> use DATABASE_NAME;

With the database changed, the next step is to actually do the SQL import itself:

mysql> source PATH/TO/FILE.sql;

Easy as that. Of course, you could just have done the whole thing via the terminal command line if you didn’t want to access the MySQL console in the first place:

mysql -u USERNAME -p DATABASE_NAME < PATH/TO/FILE.sql

Nifty.

importing goods on a container ship

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