Home | About Chris Hope | Contact Chris Hope | Search:

Importing data with mysqlimport from multiple files

Posted February 25th, 2009 in MySql

14 months ago I posted how to restore data from a tab delimited file to MySQL using the command line tool mysqlimport. When it came to importing multiple files at once I suggested using a for loop but have realised there's a much simpler way to do this using wildcards or mutiple filenames.

Using mysqlimport

As a quick recap, to import data into a MySQL table from a tab delimited file, make sure the name of the file matches the name of the table that you are importing into and do this, substituting the bracketed parts with the appropriate information.

mysqlimport -u [username] -p [database] `pwd`/[tablename].txt

The `pwd` part is replaced with the current working directory when the command is executed on *nix machines. You need to specify the full path to the file otherwise MySQL will complain with the error "mysqlimport: Error: Can't get stat of '/var/lib/mysql/tablename.txt' (Errcode: 2), when using table: tablename"

After hitting <enter> you'll be prompted for your password and then the data will be imported.

Loading data from multiple files

You can specify multiple files to import. If we wanted to import data for the tables products, categories and products_to_categories we can do this:

mysqlimport -u [username] -p [database] `pwd`/products.txt `pwd`/categories.txt `pwd`/products_to_categories.txt

Just keep adding additional tables at the end separated by spaces.

When you hit <enter> you'll be asked for the password just once and then the data will be imported.

Using wildcards

You can also use a wildcard instead of specifying the filenames to import. If the current directory had several tab delimited files you wanted to import and they all ended with the .txt extension, and there were no other txt files in the current directory, you can do this:

mysqlimport -u [username] -p [database] `pwd`/*.txt

When you hit <enter> it works the same way as the previous example and you'll only have to enter the password once.

Comments

blog comments powered by Disqus