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.

Advertisements

Creating a SQL Proper Case (propercase) user function

I’m working on a database project and had to revisit a user function that I had created a long time ago.  After evaluation the various changes and having a look at code from Tim Raster here and more from Richard McCutchen aka PsycoCoder here, I decided to combine the various features in my own code.

The code below features conversion of a string, with or without spaces and punctuation marks, to proper case.

It also understands how to spell MacIntosh, MacDonalds, et al, and features an exception list which prevents names like Maces to be written as MacEs.

The code below can be pasted straight into a SQL Code window (SQL 2008 Management Studio or and older version) to create the user defined function.

-- ==============================================
-- Author:         Roland Giesler
-- Create date:    3 May 2010
-- Description:    Change a string to proper case
-- ==============================================
 CREATE FUNCTION Proper(@value VARCHAR(8000))
 RETURNS VARCHAR(8000) AS
 BEGIN
    DECLARE @MAC_EXCEPTIONS varchar(255), @APPENDICES varchar(50), @final  VARCHAR(8000), @MyLoop int, @CharToFind varchar(1)
    -- set the Mac exeptions
    SET @MAC_EXCEPTIONS = ',Maceo,Mackinac,Mackinac  Island,Mackinaw,Mackinaw City,Macks Creek,Macks  Inn,Macom,Macomb,Macombtownship,Macon,Macy' 

    --set the initial string to lowercase and append the first separator to  be searched for
    -- this prevents a "not found" condition when searching for the first  separator
    -- and returns the whole input string unchanged when the separator is  not present in the original @value
    SET @value = LOWER(@value) + '/'
    --set the final result variable to the same as @value
    -- This is done to accomodate the loop set statements in the main loop
    SET @final = @value

    -- Repeat the whole procedure for each separator list below
    SET @MyLoop = 0
    WHILE @MyLoop < 4
       BEGIN -- Main Loop
       SET @MyLoop = @MyLoop + 1
       SET @CharToFind =
          CASE @MyLoop
             WHEN 1 THEN '/'
             WHEN 2 THEN '-'
             WHEN 3 THEN ''''
             WHEN 4 THEN ' ' -- space has to be last, to have it auto removed at ***
          END
       --set the working string to the result of the previous pass
       -- strip the appended previous separator and add the current one
       SET @value = LEFT(@final,LEN(@final)-1) + @CharToFind
       --set the final result variable to empty
       SET @final = ''

       --this ensures a loop
       WHILE 1=1
          BEGIN
          --check if "mac" appears just after a separator AND
          --the string does not appear in the MAC_EXCEPTIONS with or without  trailing punctuation
          ----------------------------------------------------------------------------------------------
          -- TO DO:
          -- NOTE: This caters for only one character like and "!" for example  trailing after the "mac" word.
          -- A beter way would be to strip all characters in a predefined  punctuation list and then lookup the
          -- word in @MAC_EXCEPTIONS.
          ----------------------------------------------------------------------------------------------
          IF PATINDEX('%mac%',@value) = 1 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-1),@MAC_EXCEPTIONS)  = 0 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-2),@MAC_EXCEPTIONS)  = 0
             SET @final = @final + 'Mac' + UPPER(SUBSTRING(@value,4,1)) +  SUBSTRING(@value,5,CHARINDEX(@CharToFind,@value)-4)

          --check if "mc" appears just after a separator AND
          --the string does not appear in the MAC_EXCEPTIONS with or without  trailing punctuation
          ELSE
             IF PATINDEX('%mc%',@value) = 1 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-1),@MAC_EXCEPTIONS)  = 0 AND  CHARINDEX(SUBSTRING(@value,1,CHARINDEX(@CharToFind,@value)-2),@MAC_EXCEPTIONS)  = 0
                SET @final = @final + 'Mc' + UPPER(SUBSTRING(@value,3,1)) +  SUBSTRING(@value,4,CHARINDEX(@CharToFind,@value)-3)
             --if there are no more separators then exit the loop
             ELSE
                IF PATINDEX('%'+@CharToFind+'%',@value) = 0
                   BREAK
                ELSE
                   --now we loop through each character and uppercase letters after a  separator
                   --or at the beginning of the string
                   SET @final = @final + UPPER(LEFT(@value,1)) +  SUBSTRING(@value,2,CHARINDEX(@CharToFind,@value)-1)
          SET @value =  SUBSTRING(@value,CHARINDEX(@CharToFind,@value)+1,LEN(@value))
       END
    --
    END -- Main Loop
 -- *** The LEN function seems to ignore trailing spaces, so we don't  have to remove the last space
 SET @final = LEFT(@final,LEN(@final))
 --return the final string
 RETURN @final
 END

 --Sample Usage
 -- SELECT dbo.Proper('mAke tHIS PRopER-CASE, MacKinaW!')