MySQL CSV Export with Data and Header

Question

I have tried to export CSV from the MySQL Database with the Header too. But I am getting the following error-

ERROR 1222 (21000): The used SELECT statements have a different number of columns

The MySQL Query is as below-

SELECT * FROM (
  (SELECT COLUMN_NAME
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_NAME = 'ORDERS'
    AND TABLE_SCHEMA = 'REMOTE'
    ORDER BY ORDINAL_POSITION)
  UNION ALL
  (SELECT * FROM ORDERS WHERE orderDate>='2018-05-07')) AS TBL
INTO OUTFILE 'C:/xampp/htdocs/WES/DBScript/SS_Orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

score 1 · Answer 1 · answered May 09 '18 at 18:31

Usually you have to do it like so:

SELECT *
FROM (
    select 'A', 'B', 'C'
    union all
    select a, b, c from table
    ) t
INTO OUTFILE [...]

Note that you'd have to ensure that all the column data types correctly match, meaning they will all need to be varchar or similar.

For the headers you could kind of automate them with something like:

SELECT * FROM (
SELECT MAX(CASE WHEN ORDINAL_POSITION = 1 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 2 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 3 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 4 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 5 THEN COLUMN_NAME END),
    MAX(CASE WHEN ORDINAL_POSITION = 6 THEN COLUMN_NAME END),
    [...]
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'ORDERS'
    AND TABLE_SCHEMA = 'REMOTE'
UNION ALL
SELECT [...]
) t
INTO OUTFILE [...]

But you've still got to know the number of columns. If you want to do it dynamically, that essentially will require dynamic SQL.

It may be easier to just write a Python script to do it.

It has 72 columns in this table. Can you not offer me something concise? — Rajib Hossain Pavel, May 09 '18 at 18:35
@observo No, I can't offer you something more concise. `INTO OUTFILE` does not support outputting headers. That is a limitation of MySQL and MariaDB and if you want to use it you've got to work around that limitation. Dynamically transforming rows to columns -- which is what you're doing with `information_schema.columns` -- is hard because it breaks database normalization. It's going to be be ugly if you use this method because SQL literally isn't meant to do that. The `UNION ALL` method is a hack that *sort of* works but it's ugly (and technically non-deterministic as written here). — Bacon Bits, May 09 '18 at 18:53

MySQL CSV Export with Data and Header

1 Answers1