MYSQL – Create CSV File With Fieldnames For Headers


Recently, I went to dump a MYSQL table into a CSV file, with the fieldnames in the first line of the file.

MYSQL Logo

MYSQL Logo

 

This simple enough in Oracle’s SQLPLUS, so I did some internet searches to determine the syntax.

Instead of the simple MYSQL syntax, I found a number of relatively complex solutions. Some involved MYSQL scripts, and others utilized Unix string parsing commands.

However, I came up with a much simpler solution using one simple MYSQL command:

SELECT
"field1"
, "field2"
, "field3"
, "field4"
from table1
union
SELECT
field1
, field2
, field3
, field4
from table1
INTO OUTFILE 'export1.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

 

You will find the .csv file under the datadir directory, which you can find using the command:

show variables like 'datadir';

+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| datadir | /var/lib/mysql/ |
+---------------+-----------------+

Under the datadir directories, will be a subdirectory for the particular database you are using (use testdb).

If you specify a full path to a different directory, be sure that the Linux mysql user can write to that directory first.

 

That’s it.  Nothing like simplicity over complexity.

Hope it’s useful!

 

Other Links:

MySQL: How to export data to csv with column headers

http://lifeboysays.wordpress.com/2012/06/23/mysql-how-to-export-data-to-csv-with-column-headers/ 

 

MySQL dump into CSV text files with column names at the top?

http://stackoverflow.com/questions/4589891/mysql-dump-into-csv-text-files-with-column-names-at-the-top

 

How to export / dump a MySql table into a text file including the field names (aka headers or column names)

http://stackoverflow.com/questions/262924/how-to-export-dump-a-mysql-table-into-a-text-file-including-the-field-names-a

 

 

 

 

Advertisements

2 Responses to MYSQL – Create CSV File With Fieldnames For Headers

  1. Of course, this is much simpler than creating a function to deal with it, but the downside is that you have to go and find the column names first and then write the query. Using the function approach is more work, once and then a matter of called the function with a table name. If you have many tables with many columns it can become quite tedious.

  2. rodgersnotes says:

    HI Roland,

    On the other hand, as I point out in DB Design Mistakes To Avoid, table structures rarely change, compared to processes and code.
    https://rodgersnotes.wordpress.com/2010/09/14/database-design-mistakes-to-avoid/

    So, unless you have dozens of tables, and hundreds of columns, to worry about, it’s probably faster to just edit a few queries. Any data migration always involves a certain amount of code and data wrangling.

    This query to the data dictionary, to get the column names, can make things easier:

    select concat ( COLUMN_NAME , ” ,” ) as column_name
    from INFORMATION_SCHEMA.COLUMNS
    where TABLE_NAME = “table1”
    order by ORDINAL_POSITION

    HTH

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: