MySQL: How to export data to csv with column headersPosted: 23 June 2012
I recently had to export a whole number of tables from a MySQL database into .csv files, but to my surprise (or should I say disgust), exporting to .csv with headers is not possible by simply using to the “SELECT … INTO OUTFILE” syntax. Fortunately all is not lost, although to me this is more proof that MySQL is not really suitable for large applications. Here is how I solved the problem.
I created a table (`tables_to_export`) with a single `tablename` column which I populated with the names of the tables that I wanted to export. I then created a procedure to traverse this table by means of a cursor so I could build the sql statements required to get the desired export result.
Basically the trick is to create a text string of comma separated column names that is used to create a select union statement to combine the column names with the data. There are quite a few examples of doing this with a single table and known column names, but I wanted to do this for a whole lot of tables in a loop. I also need to repeat this export in future, so it made sense to automate the process.
Note: The ‘information_scheme’ database contains a ‘columns’ table that has all the column names, so I used that to get the names to create the string. The
CREATE DEFINER=`root`@`localhost` PROCEDURE `export_important_tables` _ (IN `quote` TINYTEXT, IN `delimiter` TINYTEXT, IN `export_dir` VARCHAR(200)) LANGUAGE SQL NOT DETERMINISTIC CONTAINS SQL SQL SECURITY DEFINER COMMENT 'Export in csv format with headers' cont:BEGIN /* This procedure has been created to add the column names as a header column when exporting mysql tables. A table, 'tables_to_export', contains the list of tables to be exported Usage example: CALL export_important_tables('"',',','/tmp/'); */ -- Declare some variables DECLARE done INT DEFAULT FALSE; DECLARE myFile CHAR(30); DECLARE curs CURSOR FOR SELECT * FROM `foodmedb`.`tables_to_export`; -- Declare a cursor DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; -- There don't need to be set (@variables don't exist outside the procedure, -- but I set them for easy reference anyway SET @myResult = ''; SET @myHeader = ''; SET @myCommand = ''; SET @myCombined = ''; -- Do some very simple checking for valid parameters IF LENGTH(`quote`) > 1 THEN SELECT 'Parameter Error: may not be more than 1 character'; LEAVE cont; ELSEIF `quote` = '' THEN SET `quote` = '"'; END IF; IF LENGTH(`delimiter`) > 1 THEN SELECT 'Parameter Error: may not be more than 1 character'; LEAVE cont; ELSEIF `delimiter` = '' THEN SET `delimiter` = ','; END IF; IF RIGHT(`export_dir`,1) != '/' THEN SELECT 'Paramater Error: has to end in a /'; LEAVE cont; ELSEIF LENGTH(`export_dir`) = 0 THEN SET `export_dir` = '/tmp'; END IF; -- Initialise the cursor query OPEN curs; -- Set up a loop so we can traverse all the row in the cursor table table_loop: LOOP -- Get a filename FETCH curs INTO myFile; IF done THEN LEAVE table_loop; END IF; -- Build a sql statement string that concatenates the column names from the -- information schema's 'columns' table to build a header for the csv file SET @myHeader = concat('SELECT GROUP_CONCAT(',char(39),`quote`,char(39),', COLUMN_NAME, ', char(39),`quote`,char(39), ' SEPARATOR ', char(39),`delimiter`, char(39),') FROM `INFORMATION_SCHEMA`.`COLUMNS` WHERE table_schema=',char(39), 'foodmedb',char(39),' and table_name=',char(39),myFile,char(39), ' INTO @myResult'); PREPARE stmt1 FROM @myHeader; EXECUTE stmt1; -- Build the the sql statement string that will dump the data into the csv file SET @myCommand = concat('SELECT * FROM foodmedb.', myFile, ' into OUTFILE ', char(39), `export_dir`, myFile, '-', DATE_FORMAT(now(),'%Y%m%d-%H%i%s'), '.csv', char(39), ' FIELDS TERMINATED BY ', char(39), `delimiter`, char(39), ' OPTIONALLY ENCLOSED BY ', char(39), `quote`, char(39), ' LINES TERMINATED BY ', char(39),'\n',char(39)); -- Use Union to combine the two parts SET @myCombined = concat('SELECT ', @myResult,' UNION ALL ',@myCommand); PREPARE stmt2 FROM @myCombined; -- Sweet, now the files are being written! EXECUTE stmt2; END LOOP; CLOSE curs; END
This stored procedure should function as it is above. I have created parameters and some simple error checking to assist with using it more generally than just my particular use.
Please make suggestions and improvements and post them in a comment if you’d like.