MySQL: How to export data to csv with column headers

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.

About these ads

---