Can I generate a CSV file from phpMyAdmin based on a MySQL query?
For example, let's say I queried a table to return results for the word "image". Could I then produce a CSV with all of the records containing the word "image"?
Answers
In PhpMyAdmin, go into the SQL tab and enter your query in there. Hit go, then click Export at the bottom of your results. You can select to export as a CSV.
What also works well is creating a table with the query and then export the table as usual, having all the options of phpmyadmin export available. Simply do something like this in SQL box of phpmyadmin
create table tmp_export
select * from xxxx
No problems with complex queries and large datasets using this approach.
SELECT order_id,product_name,qty
FROM orders
WHERE foo = 'bar'
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Using this command columns names will not be exported.
Also note that /var/lib/mysql-files/orders.csv will be on the server that is
running MySQL. The user that the MySQL process is running under must have
permissions to write to the directory chosen, or the command will fail.
If you want to write output to your local machine from a remote server
(especially a hosted or virtualize machine such as Heroku or Amazon RDS), this solution is not suitable.
mysql --batch, -B
Print results using tab as the column separator, with each row on a new line. With this option, mysql does not use the history file. Batch mode results in non-tabular output format and escaping of special characters. Escaping may be disabled by using raw mode; see the description for the --raw option.
This will give you a tab separated file. Since commas (or strings containing comma) are not escaped it is not straightforward to change the delimiter to comma.
Many of the answers on this page are weak because they don't handle the general case of what can occur in CSV format. e.g. commas and quotes embedded in fields and other conditions that always come up eventually. We need a general solution that works for all valid CSV input data.
Here's a simple and strong solution in Python:
#!/usr/bin/env python
import csv
import sys
tab_in = csv.reader(sys.stdin, dialect=csv.excel_tab)
comma_out = csv.writer(sys.stdout, dialect=csv.excel)
for row in tab_in:
comma_out.writerow(row)
Name that file tab2csv, put it on your path, give it execute permissions, then use it list this:
mysql OTHER_OPTIONS --batch --execute='select * from whatever;' | tab2csv >outfile.csv
The Python CSV-handling functions cover corner cases for CSV input format(s).
This could be improved to handle very large files via a streaming approach.
This is simple, and it works on anything without needing batch mode or output files:
The perl script (sniped from elsewhere) does a nice job of converting the tab spaced fields to CSV.
Not exactly as a CSV format, but tee command from MySQL client can be used to save the output into a local file:
tee foobar.txt
SELECT foo FROM bar;
You can disable it using notee.
The problem with SELECT … INTO OUTFILE …; is that it requires permission to write files at the server.
Using the solution posted by Tim, I created this bash script to facilitate the process (root password is requested, but you can modify the script easily to ask for any other user):
#!/bin/bash
if [ "$1" == "" ];then
echo "Usage: $0 DATABASE TABLE [MYSQL EXTRA COMMANDS]"
exit
fi
DBNAME=$1
TABLE=$2
FNAME=$1.$2.csv
MCOMM=$3
echo "MySQL password:"
stty -echo
read PASS
stty echo
mysql -uroot -p$PASS $MCOMM $DBNAME -B -e "SELECT * FROM $TABLE;" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $FNAME
It will create a file named: database.table.csv
Try this code:
SELECT 'Column1', 'Column2', 'Column3', 'Column4', 'Column5'
UNION ALL
SELECT column1, column2,
column3 , column4, column5 FROM demo
INTO OUTFILE '/tmp/demo.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
for more: http://dev.mysql.com/doc/refman/5.1/en/select-into.html
Tiny bash script for doing simple query to CSV dumps, inspired by https://.com/a/5395421/2841607.
#!/bin/bash
# $1 = query to execute
# $2 = outfile
# $3 = mysql database name
# $4 = mysql username
if [ -z "$1" ]; then
echo "Query not given"
exit 1
fi
if [ -z "$2" ]; then
echo "Outfile not given"
exit 1
fi
MYSQL_DB=""
MYSQL_USER="root"
if [ ! -z "$3" ]; then
MYSQL_DB=$3
fi
if [ ! -z "$4" ]; then
MYSQL_USER=$4
fi
if [ -z "$MYSQL_DB" ]; then
echo "Database name not given"
exit 1
fi
if [ -z "$MYSQL_USER" ]; then
echo "Database user not given"
exit 1
fi
mysql -u $MYSQL_USER -p -D $MYSQL_DB -B -s -e "$1" | sed "s/'/\'/;s/\t/\",\"/g;s/^/\"/;s/$/\"/;s/\n//g" > $2
echo "Written to $2"
MySQL provides an easy mechanism for writing the results of a select statement into a text file on the server. Using extended options of the INTO OUTFILE nomenclature, it is possible to create a comma separated value (CSV) which can be imported into a spreadsheet application such as OpenOffice or Excel or any other applciation which accepts data in CSV format.
Given a query such as
SELECT order_id,product_name,qty FROM orders
which returns three columns of data, the results can be placed into the file /tmo/orders.txt using the query:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.txt'
This will create a tab-separated file, each row on its own line. To alter this behavior, it is possible to add modifiers to the query:
SELECT order_id,product_name,qty FROM orders INTO OUTFILE '/tmp/orders.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'
In this example, each field will be enclosed in “double quotes,” the fields will be separated by commas, and each row will be output on a new line separated by a newline (\n). Sample output of this command would look like:
Keep in mind that the output file must not already exist and that the user MySQL is running as has write permissions to the directory MySQL is attempting to write the file to.
Yesterday I looked at basic use of mysqldump for backing up MySQL databases. Today I will look at how to use mysqldump to dump the data from a MySQL database into CSV and tab delimited text files, instead of using SQL insert queries which is the default dump method.
The -u flag is used to specify the username used to connect to the MySQL database server, and you would substitute the [username] part of the above example with your username.
The -p flag indicates that you will enter a password to connect to the database; you will be prompted for it once the command starts executing.
The -T flag followed by the directory name is where MySQL will write its files to. You can have a space between the -T and the start of the directory name or no space: it's up to you as either will work. It is important to note that the directory you specify must be writeable by the user the MySQL server runs as. If it is not, you'll get an error like this:
mysqldump: Got error: 1: Can't create/write to file '/path/to/filename.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
If your Linux machine has SELinux enabled, then the directory must also be allowed by the SELinux configuration for MySQL to write to.
Files created by mysqldump
Using the above example, with a database that has two tables called "something" and "something_else", four files will be created as follows:
something.sql - contains the SQL to create the table. By default, it includes DROP TABLE IF EXISTS `something`; as part of the query
something.txt - the data from the "something" table in tab delimited format
something_else.sql - contains the SQL to create the table. By default, it includes DROP TABLE IF EXISTS `something_else`; as part of the query
something_else.txt - the data from the "something_else" table in tab delimited format
If you don't want the *.sql files to be created, then you can add the -t flag to the mysqldump command like so:
Note that even if the directory you specify cannot be written to by the MySQL server, the *.sql files will still be created; it's only the text files which cannot be created.
Changing the output format
By default, mysqldump with the -T flag will dump the data into tab delimited files. However, it is possible to change the delimiter, and also to specify that quotes surround the field values.
To change the delimiter, use the --fields-terminated-by= flag like in the following example. In this example we will dump the data into comma separated values or CSV:
If you wanted to also put quotes around each field, then use the --fields-enclosed-by= flag. In the example below, each field is surrounded by quotes. Note that we need to escape the quote symbol on the command line with a slash.
MySQL has a couple of options for exporting data: using the command line tool mysqldump (read my using mysqldump to save data to CSV files post for more details) and using a "SELECT ... INTO OUTFILE" SQL query. This post looks at the latter to export data from MySQL into a CSV file.
To dump all the records from a table called "products" into the file /tmp/products.csv as a CSV file, use the following SQL query:
SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products
Note that the directory must be writable by the MySQL database server. If it's not, you'll get an error message like this:
#1 - Can't create/write to file '/tmp/products.csv' (Errcode: 13)
Also note that it will not overwrite the file if it already exists, instead showing this error message:
#1086 - File '/tmp/products.csv' already exists
If you don't need quotes around all fields (e.g. numeric fields) then change "ENCLOSED BY" to "OPTIONALLY ENCLOSED BY" and MySQL will only put quotes around the fields that need them. Some systems require all fields in a CSV file to have quotes around them so you may need to export the data with quotes around them all depending on your requirements.
To only export a selected set of fields or data, change "SELECT *" to "SELECT field1, field2, etc" and add a WHERE clause after the FROM clause.
Posts
![IVRAMANA](https://draft.blogger.com/img/profile_blank_gray.jpg" title="IVRAMANA)
