----------------------------------------------------------------------
--- Knud van Eeden --- 06 June 2008 - 09:02 pm -----------------------
Database: MySql: CSV: File: Table: Operation: Convert: How to export a MySql table as comma separated .csv text file including the first row of table column names as a list? Method: Batch file
---
What is looked for is a comma separated (.csv) list of elements
of a given table in your database, using MySql.
---
But also a comma separated list of the column names should be included,
as first row.
---
Currently there seems to be no easy way to do this in MySql (e.g. a
preferred way would be using e.g. a parameter in the INTO OUTFILE command,
but that seems not available yet).
---
Solutions using e.g. UNION in general demanded also manual interaction, and a more
automatic solution was sought.
---
Also mysqldump.exe was tried, but as far as currently is known, there
you also can not include a comma separated list of the column names in
the output, using parameters.
---
So a general automatic solution is to run this batch file.
You can supply your database name, table name and the executable path to your mysql.exe file
(usually in the 'bin' directory of your MySql installation).
The batch file uses the table 'information_schema_columns', selects the column names from this,
for your table. But it gives this result as rows beneath each other.
Tried was e.g. to transpose that rows to a columns instead, but no general method was found yet.
Instead using group_concat() you concatenate this rows to 1 string.
Because that string is only 1 element, thus a 1 row and 1 column table,
you can in general not immediately use e.g. UNION to concatenate that.
Therefore the task was split in 2 parts.
You could not immediately put it in a '-e' one line of MySql.exe commands
separated by semicolons (-e "...;...;..."), because INTO OUTFILE took only the last command.
By design, you can also not use the same file for INTO OUTFILE, so 2 separate files are created.
So putting it in a batchjob, where you call OUTFILE twice, once
creating a file with a comma separated list the column names, and once
creating the comma separated lists of other rows.
Note that you can probably not put the column names in double quotes,
because the commas will be escaped if you should try (e.g. a,b,c,d becomes "a\,b\,c\,d")
After generating the 2 .csv files you append the 2 files to the final .csv result.
---
Another solution is to run e.g. a PHP program (but this is more
involved, as you have to have PHP installed, and access to the MySql
database tables).
===
Steps: Overview:
1. -(the (temporary) files used are in this example 'c:\temp\ddd01.txt'
and 'c:\temp\ddd02.txt', the name of my database is 'test',
the name of my table is 'yourtablename'.
Adapt this to the circumstances on your system)
2. -Create for testing purposes this following database
--- cut here: begin --------------------------------------------------
CREATE DATABASE test;
--- cut here: end ----------------------------------------------------
3. -Create for testing purposes this following table
--- cut here: begin --------------------------------------------------
DROP TABLE IF EXISTS yourTableName;
CREATE TABLE yourTableName (
a VARCHAR( 255 ),
b VARCHAR( 255 ),
c VARCHAR( 255 ),
x VARCHAR( 255 ),
y VARCHAR( 255 )
);
--- cut here: end ----------------------------------------------------
4. -Put some values in that table
--- cut here: begin --------------------------------------------------
INSERT INTO yourTableName(a, b, c) VALUES("56","1222","78.5");INSERT INTO yourTableName(x, y) VALUES("45","-10");INSERT INTO yourTableName(x, b, a, y) VALUES("12","10459","756","9");
--- cut here: end ----------------------------------------------------
5. -E.g. create a batch file containing the following commands
(it creates a file containing comma separated list of column names of your table,
and a file containing a comma separated list of the other rows,
and finally concatenates it using the batch redirection '>>'.
--- cut here: begin --------------------------------------------------
6. -Batch file, with command line parameters
(save it e.g. as mysql2csv.bat)
--- cut here: begin --------------------------------------------------
@SET databasename=%1
@SET tablename=%2
@SET filename1=c:\\temp\\ddd01.csv
@SET filename2=c:\\temp\\ddd02.csv
@SET mysqlexe=%3
@DEL c:\temp\ddd01.csv
@DEL c:\temp\ddd02.csv
@%mysqlexe% -u root -e "SELECT group_concat( column_name ) FROM information_schema.columns WHERE table_name = '%tablename%' INTO OUTFILE '%filename1%'" --skip-column-names
@%mysqlexe% -u root -e "SELECT * FROM %databasename%.%tablename% INTO OUTFILE '%filename2%' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'" --skip-column-names
@TYPE %filename2% >> %filename1%
@ECHO.
@ECHO.
@TYPE %filename1%
--- cut here: end ----------------------------------------------------
8. Call it using
yourbatchfilename.bat yourdatabasename yourtablename yourmysqlexe
e.g.
mysql2csv.bat test yourtablename "P:\Program Files\MySQL\MySQL Server 5.0\bin\mysql.exe"
9. -That will show a screen output similar to the following:
--- cut here: begin --------------------------------------------------
a,b,c,x,y
"56","1222","78.5",\N,\N
\N,\N,\N,"45","-10"
"756","10459",\N,"12","9"
--- cut here: end ----------------------------------------------------
===
Tested successfully on
Microsoft Windows XP Professional (service pack 2),
running
MySql v5
and
4nt or cmd.exe as command processor
===
Book: see also:
===
Diagram: see also:
===
Help: see also:
===
Image: see also:
===
Internet: see also:
PHP Script: Export MySQL table data to CSV
http://www.tutorial5.com/content/view/159/85/
===
===
Screencast: see also:
===
Table: see also:
===
Video: see also:
---
----------------------------------------------------------------------