Include headers when using SELECT INTO OUTFILE

Question

Is it possible to include the headers somehow when using the MySQL INTO OUTFILE

User · Answer

Since the  include-headers  functionality doesn t seem to be build-in yet  and most  solutions  here need to type the columns names manually  and or don t even take joins into account  I d recommand to get around the problem    The best alternative I found so far is using a decent tool  I use HeidiSQL   Put your request  select the grid  just right click and export to a file   It got all necessary options for a clean export  ans should handle most needs  In the same idea  user3037511 s approach works fine  and can be automated easily  Just launch your request with some command line to get your headers   You may get the data with a SELECT INTO OUTFILE    or by running your query without the limit  yours to choose   Note that output redirect to a file works like a charm on both Linux AND Windows     This makes me want to highlight that 80  of the time  when I want to use SELECT FROM INFILE or SELECT INTO OUTFILE  I end-up using something else due to some limitations  here  the absence of a  headers options   on an AWS-RDS  the missing rights  and so on    Hence  I don t exactly answer to the op s question    but it should answer his needs    EDIT   and to actually answer his question   no As of 2017-09-07  you just can t include headers if you stick with the SELECT INTO OUTFILE command

User · Answer

The solution provided by Joe Steanelli works  but making a list of columns is inconvenient when dozens or hundreds of columns are involved  Here s how to get column list of table my table in my schema   -- override GROUP CONCAT limit of 1024 characters to avoid a truncated result set session group concat max len   1000000   select GROUP CONCAT CONCAT     COLUMN NAME       from INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    my table  AND TABLE SCHEMA    my schema  order BY ORDINAL POSITION   Now you can copy  amp  paste the resulting row as first statement in Joe s method

User · Answer

I faced similar problem while executing mysql query on large tables in NodeJS  The approach which I followed to include headers in my CSV file is as follows   Use OUTFILE query to prepare file without headers      SELECT   INTO OUTFILE  FILE NAME  FIELDS TERMINATED BY     OPTIONALLY ENCLOSED      BY      LINES TERMINATED BY   n  FROM  TABLE NAME   Fetch column headers for the table used in point 1      select GROUP CONCAT CONCAT      COLUMN NAME        as col names from      INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    TABLE NAME  AND TABLE SCHEMA         DATABASE NAME  ORDER BY ORDINAL POSITION  Append the column headers to the file created in step 1 using prepend-file  npm package   Execution of each step was controlled using promises in NodeJS

User · Answer

Actually you can make it work even with an ORDER BY   Just needs some trickery in the order by statement - we use a case statement and replace the header value with some other value that is guaranteed to sort first in the list  obviously this is dependant on the type of field and whether you are sorting ASC or DESC   Let s say you have three fields  name  varchar   is active  bool   date something happens  date   and you want to sort the second two descending   select           name           is active  as is active         date something happens as  date something happens    union all   select name  is active  date something happens   from     my table   order by       case is active when  is active  then 0 else is active end  desc       case date when  date  then  9999-12-30  else date end  desc

User · Answer

You can use prepared statement with lucek s answer and export dynamically table with columns name in CSV    --If your table has too many columns SET GLOBAL group concat max len   100000000  --Prepared statement SET  SQL     select CONCAT  SELECT   INTO OUTFILE   YOUR PATH   FIELDS TERMINATED BY       OPTIONALLY ENCLOSED BY       ESCAPED BY      LINES TERMINATED BY     n   FROM  SELECT    GROUP CONCAT CONCAT     COLUMN NAME         UNION select   from YOUR TABLE  as tmp   from INFORMATION SCHEMA COLUMNS WHERE TABLE NAME    YOUR TABLE  AND TABLE SCHEMA    YOUR SCHEMA  order BY ORDINAL POSITION    --Execute it PREPARE stmt FROM  SQL  EXECUTE stmt    Thank lucek

User · Answer

This is an alternative cheat if you are familiar with Python or R  and your table can fit into memory    Import the SQL table into Python or R and then export from there as a CSV and you ll get the column names as well as the data   Here s how I do it using R  requires the RMySQL library   db  lt - dbConnect MySQL    user  user   password  password   dbname  myschema   host  localhost    query  lt - dbSendQuery db   select   from mytable   dataset  lt - fetch query  n -1   write csv dataset   mytable backup csv     It s a bit of a cheat but I found this was a quick workaround when my number of columns was too long to use the concat method above  Note  R will add a  row names  column at the start of the CSV so you ll want to drop that if you do need to rely on the CSV to recreate the table

User · Answer

So  if all the columns in my table are a character data type  we can combine the top answers  by Joe  matt and evilguc  together  to get the header added automatically in one  simple  SQL query  e g   select   from      select column name     from information schema columns     where table name    my table      and table schema    my schema      order by ordinal position    union all    select       potentially complex SELECT statement with WHERE  ORDER BY  GROUP BY etc    from my table   as tbl into outfile   path outfile  fields terminated by     optionally enclosed by     escaped by      lines terminated by   n     where the last couple of lines make the output csv   Note that this may be slow if my table is very large

User · Answer

SELECT  ColName1    ColName2    ColName3  UNION ALL SELECT ColName1  ColName2  ColName3     FROM YourTable     INTO OUTFILE  c   datasheet csv  FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY     LINES TERMINATED BY   n

User · Answer

The easiest way is to hard code the columns yourself to better control the output file  SELECT  ColName1    ColName2    ColName3  UNION ALL SELECT ColName1  ColName2  ColName3     FROM YourTable     INTO OUTFILE   path outfile

User · Answer

This will alow you to have ordered columns and or a limit  SELECT  ColName1    ColName2    ColName3  UNION ALL SELECT   from  SELECT ColName1  ColName2  ColName3     FROM YourTable order by ColName1 limit 3  a     INTO OUTFILE   path outfile

User · Answer

I had no luck with any of these  so after finding a solution  I wanted to add it to the prior answers    Python  3 8 6   MySQL  8 0 19  Forgive my lack of SO formatting foo   Somebody please clean up   Note a couple of things  First  the query to return column names is unforgiving of punctuation   Using   backticks or leaving out   quote around the  schema name  and  table name  will throw an  quot unknown column quot  error  WHERE TABLE SCHEMA    schema  AND TABLE NAME    table   Second  the column header names return as a single-entity tuple with all the column names concatenated in one quoted string   Convert to quoted list was easy  but not intuitive  for me at least   headers list   headers result 0  split  quot   quot    Third  cursor must be buffered or the  quot lazy quot  thing will not fetch your results as you need them   For very large tables  memory could be an issue   Perhaps chunking would solve that problem  cur   db cursor buffered True   Last  all types of UNION attempts yielded errors for me   By zipping the whole mess into a list of dicts  it became trivial to write to a csv  using csv DictWriter  headers sql    quot  quot  quot     SELECT  GROUP CONCAT CONCAT COLUMN NAME  ORDER BY ORDINAL POSITION     FROM INFORMATION SCHEMA COLUMNS    WHERE TABLE SCHEMA    schema  AND TABLE NAME    table       quot  quot  quot  quot    cur   db cursor buffered True   cur execute header sql   headers result   cur fetchone    headers list   headers result 0  split  quot   quot    rows sql    quot  quot  quot    SELECT   FROM schema table     quot  quot  quot  quot    data   cur execute rows sql   data rows   cur fetchall    data as list of dicts    dict zip headers list  row   for row in data rows   with open csv destination file   w   encoding  utf-8   as destination file opened       dict writer   csv DictWriter destination file opened  fieldnames headers list       dict writer writeheader     for dict in dict list          dict writer writerow dict

User · Answer

an example from my database table name sensor with colums  id time unit   select   id   as id    time   as time    unit   as unit UNION ALL SELECT   INTO OUTFILE  C  Users User Downloads data csv    FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY       LINES TERMINATED BY   n    FROM sensor

User · Answer

I simply make 2 queries  first to get query output  limit 1  with column names  no hardcode  no problems with Joins  Order by  custom column names  etc   and second to make query itself  and combine files into one CSV file   CSVHEAD   usr bin mysql  CONNECTION STRING -e   QUERY limit 1   head -n1 xargs sed -e  s        g   echo     CSVHEAD     gt   TMP head txt  usr bin mysql  CONNECTION STRING -e   QUERY into outfile    TMP  data txt  fields terminated by     optionally enclosed by      escaped by    lines terminated by   r n    cat  TMP head txt  TMP data txt  gt   TMP data csv

User · Answer

For complex select with ORDER BY I use the following   SELECT   FROM       SELECT  Column name  1    Column name  2    Column name         UNION ALL                  complex SELECT statement with WHERE  ORDER BY  GROUP BY etc          resulting set INTO OUTFILE   path to file

User · Answer

I was writing my code in PHP  and I had a bit of trouble using concat and union functions  and also did not use SQL variables  any ways I got it to work  here is my code     first I connected to the information scheme DB   headercon mysqli connect  localhost    USERNAME    PASSWORD    information schema       took the healders out in a string  I could not get the concat function to work  so I wrote a loop for it        headers            sql    SELECT column name AS columns FROM  COLUMNS  WHERE table schema    YOUR DB NAME  AND table name    YOUR TABLE NAME         result    headercon- gt query  sql       while  row    result- gt fetch row                   headers    headers          row 0                  headers   substr   headers   0  -2       connect to the DB of interest   con mysqli connect  localhost    USERNAME    PASSWORD    YOUR DB NAME        export the results to csv  sql4    SELECT  headers UNION SELECT   FROM YOUR TABLE NAME WHERE     INTO OUTFILE   output csv  FIELDS TERMINATED BY        result4    con- gt query  sql4

User · Answer

You d have to hard code those headers yourself  Something like   SELECT  ColName1    ColName2    ColName3  UNION ALL SELECT ColName1  ColName2  ColName3     FROM YourTable     INTO OUTFILE   path outfile

User · Answer

I think if you use a UNION it will work   select  header 1    header 2       union select col1  col2      from       I don t know of a way to specify the headers with the INTO OUTFILE syntax directly

User · Answer

Here is a way to get the header titles from the column names dynamically       Change table name and database name    SET  table name    table name   SET  table schema    database name   SET  default group concat max len    SELECT   group concat max len       Sets Group Concat Max Limit larger for tables with a lot of columns    SET SESSION group concat max len   1000000   SET  col names       SELECT GROUP CONCAT QUOTE  column name    AS columns   FROM information schema columns   WHERE table schema    table schema   AND table name    table name    SET  cols   CONCAT   SELECT     col names         SET  query   CONCAT   SELECT   FROM     table schema        table name      INTO OUTFILE    tmp your csv file csv     FIELDS ENCLOSED BY          TERMINATED BY    t   ESCAPED BY        LINES TERMINATED BY    n           Concatenates column names to query    SET  sql   CONCAT  cols    UNION ALL     query       Resets Group Contact Max Limit back to original value    SET SESSION group concat max len    default group concat max len   PREPARE stmt FROM  sql  EXECUTE stmt  DEALLOCATE PREPARE stmt

User · Answer

I would like to add to the answer provided by Sangam Belose  Here s his code   select   id   as id    time   as time    unit   as unit UNION ALL SELECT   INTO OUTFILE  C  Users User Downloads data csv    FIELDS TERMINATED BY     OPTIONALLY ENCLOSED BY       LINES TERMINATED BY   n    FROM sensor   However  if you have not set up your  secure file priv  within the variables  it may not work  For that  check the folder set on that variable by   SHOW VARIABLES LIKE  secure file priv    The output should look like this   mysql gt  show variables like   secure file priv     ------------------ ------------------------------------------------    Variable name      Value                                             ------------------ ------------------------------------------------    secure file priv   C  ProgramData MySQL MySQL Server 8 0 Uploads     ------------------ ------------------------------------------------  1 row in set  1 warning  0 00 sec    You can either change this variable or change the query to output the file to the default path showing

User · Answer

MySQL alone isn t enough to do this simply  Below is a PHP script that will output columns and data to CSV   Enter your database name and tables near the top    lt  php  set time limit  24192000    ini set   memory limit    -1     setlocale  LC CTYPE   en US UTF-8     mb regex encoding   UTF-8       dbn    DB NAME    tbls   array   TABLE1    TABLE2    TABLE3       db   new PDO   mysql host localhost dbname      dbn     charset UTF8    root    pass      foreach   tbls as  tbl         echo  tbl     n        path     var lib mysql      tbl     csv         colStr            cols    db- gt query   SELECT COLUMN NAME AS  column  FROM INFORMATION SCHEMA COLUMNS WHERE TABLE NAME         tbl      AND TABLE SCHEMA         dbn        - gt fetchAll  PDO  FETCH COLUMN        foreach   cols as  col                 if   colStr    colStr                   colStr           col                    db- gt query       SELECT       FROM               SELECT      colStr             UNION ALL         SELECT   FROM      tbl           AS sub     INTO OUTFILE       path          FIELDS TERMINATED BY         ENCLOSED BY          LINES TERMINATED BY   n               exec   gzip      path         print r   db- gt errorInfo           gt    You ll need this to be the directory you d like to output to  MySQL needs to have the ability to write to the directory    path     var lib mysql      tbl     csv     You can edit the CSV export options in the query   INTO OUTFILE       path      FIELDS TERMINATED BY     ENCLOSED BY      LINES TERMINATED BY   n     At the end there is an exec call to GZip the CSV

[mysql] Include headers when using SELECT INTO OUTFILE?

Examples related to mysql

Examples related to into-outfile