MySQL: Dynamically export CSV file with headers on top

Question

I want to dynamically export CSV files with headers from mySQL. I tried the answer of shadow0359: MySQL: Dynamically export CSV file with headers, but his solution, i believe exports the header on the bottom of the records and not on the top. Does anyone knows how to dynamically export CSV files from mySQL with headers on top?

My Code (Scripts to create):

Stored Procedure

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`(in_Accountid 
varchar(30))
BEGIN

SET @default_group_concat_max_len = (SELECT @@group_concat_max_len);
SET SESSION group_concat_max_len = 1000000;
SET @FilePath:='C:/ProgramData/MySQL/MySQL Server 5.7/Uploads/';
SET @FileName:=CONCAT(in_AccountID,'_',DATE_FORMAT(NOW(),'%Y-%m- 
%d'),'.xls');
SET @FullFilePath:=CONCAT(@FilePath,@FileName);
SET @TableName:='temp_transactionslines';
SET @TableSchema:='melhor_metade_2016';

SET @SQL = ( select CONCAT('SELECT  
                        TransactionID
                        ,RecordID
                        ,AccountID
                        ,AccountDescription
                        ,SourceDocumentID
                        ,SystemEntryDate
                        ,Description
                        ,DebitAmount
                        ,CreditAmount
                        ,Period
                        ,TransactionDate
                        ,CustomerTaxID
                        ,CustomerID 
                        INTO OUTFILE \''
                        , @FullFilePath, '\'
                        FROM (SELECT '
                            ,group_concat(CONCAT("'", COLUMN_NAME,"'"))
                            ,' UNION ALL SELECT * FROM 
temp_transactionslines  order by TransactionID ASC) as tmp')

        from INFORMATION_SCHEMA.COLUMNS 
        WHERE TABLE_NAME = @TableName
        AND TABLE_SCHEMA = @TableSchema
        order BY ORDINAL_POSITION );

SET SESSION group_concat_max_len = @default_group_concat_max_len;

PREPARE dynamic_statement FROM @SQL;
EXECUTE dynamic_statement;
DEALLOCATE PREPARE dynamic_statement;

END$$
DELIMITER ;

Table

CREATE TABLE `temp_transactionslines` (
`TransactionID` varchar(70) NOT NULL,
`RecordID` varchar(30) NOT NULL,
`AccountID` varchar(30) DEFAULT NULL,
`AccountDescription` varchar(100) DEFAULT NULL,
`SourceDocumentID` varchar(60) DEFAULT NULL,
`SystemEntryDate` varchar(19) DEFAULT NULL,
`Description` varchar(200) DEFAULT NULL,
`DebitAmount` float(13,2) DEFAULT NULL,
`CreditAmount` float(13,2) DEFAULT NULL,
`Period` int(2) DEFAULT NULL,
`TransactionDate` varchar(19) DEFAULT NULL,
`CustomerTaxID` varchar(20) DEFAULT NULL,
`CustomerID` varchar(30) DEFAULT NULL,
`SupplierID` varchar(30) DEFAULT NULL,
 KEY `i_accountID` (`AccountID`)) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Data

INSERT INTO `melhor_metade_2016`.`temp_transactionslines`
(`TransactionID`,
`RecordID`,
`AccountID`,
`AccountDescription`,
`SourceDocumentID`,
`SystemEntryDate`,
`Description`,
`DebitAmount`,
`CreditAmount`,
`Period`,
`TransactionDate`,
`CustomerTaxID`,
`CustomerID`,
`SupplierID`)
VALUES
('a','b',3,4,5,2, 1, 1,null,3,1,2,3,4);

Absolutely, yes I tried it and the headers always go to last line! Try it also if you do not believe me! — António Manuel Paraíso Pinto
– António Manuel Paraíso Pinto, Commented Mar 3, 2019 at 19:52
I've already tried the second (simplified) part - and it worked fine. You sould post your sample data and the code that didn't work for you. However - I would replace UNION with UNION ALL. — Paul Spiegel
– Paul Spiegel, Commented Mar 3, 2019 at 19:55
The simplified part is not dynamic, its a static version. I also changed the union to union all and has no impact, the headers go to bottom. — António Manuel Paraíso Pinto
– António Manuel Paraíso Pinto, Commented Mar 3, 2019 at 20:01
You have an ORDER BY after UNION. Replace UNION ALL SELECT * FROM temp_transactionslines order by TransactionID ASC by UNION ALL (SELECT * FROM temp_transactionslines order by TransactionID ASC). — Paul Spiegel
– Paul Spiegel, Commented Mar 3, 2019 at 21:08

Paul Spiegel · Accepted Answer · 2019-03-03 21:22:21Z

1

You generate the following query:

SELECT 'TransactionID'
      ,'RecordID'
      ,'AccountID'
      -- more column names
UNION ALL
SELECT * FROM temp_transactionslines
order by TransactionID ASC

The ORDER BY clause applies to to the full UNION set. If you only want to sort the second query result, you should wrap the query into parentheses:

SELECT 'TransactionID'
      ,'RecordID'
      ,'AccountID'
      -- more column names
UNION ALL
(SELECT * FROM temp_transactionslines  order by TransactionID ASC)

Demo: https://www.db-fiddle.com/f/fSXBH527fxg9hXy1JYGHyQ/0

answered Mar 3, 2019 at 21:22

Paul Spiegel

31.9k5 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySQL: Dynamically export CSV file with headers on top

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related