MySQL: How do I dump each record from a table to a separate files in csv format?
I honestly do not know why somebody would want to export each record from a table in to its’ own files in a csv format. I am sure people have their own reasons. But since I got request from couple people, I figure I would post a solution here. Same script can be used to dump the whole table in to one csv file as well, with little tweaking. I will start with creating database with a table. I then insert three rows with test data into the table just to show three separate files creation.
mysql> CREATE DATABASE testdump;
mysql> USE testdump
mysql> CREATE TABLE `testtable` (
`id` TINYINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`testfield` VARCHAR( 15 ) NOT NULL ,
`testfield2` VARCHAR( 15 ) NOT NULL
) ENGINE = innodb COMMENT = 'test table for dumping each row to file';
mysql> INSERT INTO `testtable` values ('','test1','test2'),('','test3','test4'),('','test5','test6');
mysql> select * from testtable;
+----+-----------+------------+
| id | testfield | testfield2 |
+----+-----------+------------+
| 1 | test1 | test2 |
| 2 | test3 | test4 |
| 3 | test5 | test6 |
+----+-----------+------------+
3 rows in set (0.00 sec)
Now we create a php script which we can use to select data from MySQL database and save it to a file under /tmp directory. You can easily modify this script to save it somewhere else, pull from multiple tables and/or save with different delimiter.
<?php
$link = mysql_connect('localhost', 'username', 'password');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
$db = mysql_select_db('testdump',$link);
//specify what query to use to pull data.
$select = "SELECT * FROM `testtable`";
$result = mysql_query($select);
while($myrow = mysql_fetch_array($result))
{
extract($myrow);
//specify what query to use for create text files with csv data
//in our case we create files with testtable_ and ID which is unique
//per row. We use the same ID in where clause to limit one record at a time
//modify this part to your needs. Eg change delimiter from , to - or something else
$export = "
SELECT * INTO OUTFILE '/tmp/testtable_$id.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
LINES TERMINATED BY '\n'
FROM testtable where id=$id";
mysql_query($export);
}//loop until we are done exporting all records.
mysql_close($link);
?>
You can run this script from command line if you have php cli installed by issuing: php filename.php
Above example is a very simple example. t is merely a proof of concept. You can modify it to do whatever you need it to do. I would also put some error checking, not use extract() function, etc before using it in production environment.
————————————-
DISCLAIMER: Please be smart and use code found on internet carefully. Make backups often. And yeah.. last but not least.. I am not responsible for any damage caused by this posting. Use at your own risk.
