Is there an easy way to run a MySQL query from the Linux command line and output the results in CSV format?
Here's what I'm doing now:
mysql -u uid -ppwd -D dbname << EOQ | sed -e 's/ /,/g' | tee list.csv
select id, concat("\"",name,"\"") as name
from students
EOQ
It gets messy when there are a lot of columns that need to be surrounded by quotes, or if there are quotes in the results that need to be escaped.
From Save MySQL query results into a text or CSV file:
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';
Note: That syntax may need to be reordered to
SELECT order_id,product_name,qty
INTO OUTFILE '/var/lib/mysql-files/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM orders
WHERE foo = 'bar';
in more recent versions of MySQL.
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.
I encountered the same problem and Paul's Answer wasn't an option since it was Amazon RDS. Replacing the tab with the commas did not work as the data had embedded commas and tabs. I found that mycli, which is a drop-in alternative for the mysql-client, supports CSV output out of the box with the --csv flag:
mycli db_name --csv -e "select * from flowers" > flowers.csv
Also, if you're performing the query on the Bash command line, I believe the tr command can be used to substitute the default tabs to arbitrary delimiters.
$ echo "SELECT * FROM Table123" | mysql Database456 | tr "\t" ,
You can have a MySQL table that uses the CSV engine.
Then you will have a file on your hard disk that will always be in a CSV format which you could just copy without processing it.
What worked for me:
SELECT *
FROM students
WHERE foo = 'bar'
LIMIT 0,1200000
INTO OUTFILE './students-1200000.csv'
FIELDS TERMINATED BY ',' ESCAPED BY '"'
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';
None of the solutions on this thread worked for my particular case. I had pretty JSON data inside one of the columns, which would get messed up in my CSV output. For those with a similar problem, try lines terminated by \r\n instead.
Also another problem for those trying to open the CSV with Microsoft Excel, keep in mind there is a limit of 32,767 characters that a single cell can hold, above that it overflows to the rows below. To identify which records in a column have the issue, use the query below. You can then truncate those records or handle them as you'd like.
SELECT id,name,CHAR_LENGTH(json_student_description) AS 'character length'
FROM students
WHERE CHAR_LENGTH(json_student_description)>32767;
In my case from table_name ..... before INTO OUTFILE ..... gives an error:
Unexpected ordering of clauses. (near "FROM" at position 10)
What works for me:
SELECT *
INTO OUTFILE '/Volumes/Development/sql/sql/enabled_contacts.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM table_name
WHERE column_name = 'value'
If you have PHP set up on the server, you can use mysql2csv to export an (actually valid) CSV file for an arbitrary MySQL query. See my answer at MySQL - SELECT * INTO OUTFILE LOCAL ? for a little more context/info.
I tried to maintain the option names from mysql so it should be sufficient to provide the --file and --query options:
./mysql2csv --file="/tmp/result.csv" --query='SELECT 1 as foo, 2 as bar;' --user="username" --password="password"
"Install" mysql2csv via
wget https://gist.githubusercontent.com/paslandau/37bf787eab1b84fc7ae679d1823cf401/raw/29a48bb0a43f6750858e1ddec054d3552f3cbc45/mysql2csv -O mysql2csv -q && (sha256sum mysql2csv | cmp <(echo "b109535b29733bd596ecc8608e008732e617e97906f119c66dd7cf6ab2865a65 mysql2csv") || (echo "ERROR comparing hash, Found:" ;sha256sum mysql2csv) ) && chmod +x mysql2csv
(Download content of the gist, check checksum and make it executable.)
The following produces tab-delimited and valid CSV output. Unlike most of the other answers, this technique correctly handles escaping of tabs, commas, quotes, and new lines without any stream filter like sed, AWK, or tr.
The example shows how to pipe a remote MySQL table directly into a local SQLite database using streams. This works without FILE permission or SELECT INTO OUTFILE permission. I have added new lines for readability.
mysql -B -C --raw -u 'username' --password='password' --host='hostname' 'databasename'
-e 'SELECT
CONCAT('\''"'\'',REPLACE(`id`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''id'\'',
CONCAT('\''"'\'',REPLACE(`value`,'\''"'\'', '\''""'\''),'\''"'\'') AS '\''value'\''
FROM sampledata'
2>/dev/null | sqlite3 -csv -separator $'\t' mydb.db '.import /dev/stdin mycsvtable'
The 2>/dev/null is needed to suppress the warning about the password on the command line.
If your data has NULLs, you can use the IFNULL() function in the query.
A simple solution in Python that writes a standard-format CSV file with headers and writes data as a stream (low memory use):
import csv
def export_table(connection, table_name, output_filename):
cursor = connection.cursor()
cursor.execute("SELECT * FROM " + table_name)
# thanks to https://gist.github.com/madan712/f27ac3b703a541abbcd63871a4a56636 for this hint
header = [descriptor[0] for descriptor in cursor.description]
with open(output_filename, 'w') as csvfile:
csv_writer = csv.writer(csvfile, dialect='excel')
csv_writer.writerow(header)
for row in cursor:
csv_writer.writerow(row)
You could use it like:
import mysql.connector as mysql
# (or https://github.com/PyMySQL/PyMySQL should work but I haven't tested it)
db = mysql.connect(
host="localhost",
user="USERNAME",
db="DATABASE_NAME",
port=9999)
for table_name in ['table1', 'table2']:
export_table(db, table_name, table_name + '.csv')
db.close()
For simplicity, this intentionally doesn't include some fancier stuff from another answer like using an environment variable for credentials, contextlib, etc. There is a subtlety mentioned there about line endings that I haven't evaluated.
If you are getting an error of secure-file-priv then, also after shifting your destination file location inside the C:\ProgramData\MySQL\MySQL Server 8.0\Uploads and also after then the query -
SELECT * FROM attendance INTO OUTFILE 'C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
is not working, you have to just change \(backsplash) from the query to / (forwardsplash)
And that works!!
Example:
SELECT * FROM attendance INTO OUTFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/FileName.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';
Each time when you run the successful query, it will generate the new CSV file each time! Cool, right?
The following Bash script works for me. It optionally also gets the schema for the requested tables.
#!/bin/bash
#
# Export MySQL data to CSV
#https://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format
#
# ANSI colors
#http://www.csc.uvic.ca/~sae/seng265/fall04/tips/s265s047-tips/bash-using-colors.html
blue='\033[0;34m'
red='\033[0;31m'
green='\033[0;32m' # '\e[1;32m' is too bright for white bg.
endColor='\033[0m'
#
# A colored message
# params:
# 1: l_color - the color of the message
# 2: l_msg - the message to display
#
color_msg() {
local l_color="$1"
local l_msg="$2"
echo -e "${l_color}$l_msg${endColor}"
}
#
# Error
#
# Show the given error message on standard error and exit
#
# Parameters:
# 1: l_msg - the error message to display
#
error() {
local l_msg="$1"
# Use ANSI red for error
color_msg $red "Error:" 1>&2
color_msg $red "\t$l_msg" 1>&2
usage
}
#
# Display usage
#
usage() {
echo "usage: $0 [-h|--help]" 1>&2
echo " -o | --output csvdirectory" 1>&2
echo " -d | --database database" 1>&2
echo " -t | --tables tables" 1>&2
echo " -p | --password password" 1>&2
echo " -u | --user user" 1>&2
echo " -hs | --host host" 1>&2
echo " -gs | --get-schema" 1>&2
echo "" 1>&2
echo " output: output CSV directory to export MySQL data into" 1>&2
echo "" 1>&2
echo " user: MySQL user" 1>&2
echo " password: MySQL password" 1>&2
echo "" 1>&2
echo " database: target database" 1>&2
echo " tables: tables to export" 1>&2
echo " host: host of target database" 1>&2
echo "" 1>&2
echo " -h|--help: show help" 1>&2
exit 1
}
#
# show help
#
help() {
echo "$0 Help" 1>&2
echo "===========" 1>&2
echo "$0 exports a CSV file from a MySQL database optionally limiting to a list of tables" 1>&2
echo " example: $0 --database=cms --user=scott --password=tiger --tables=person --output person.csv" 1>&2
echo "" 1>&2
usage
}
domysql() {
mysql --host $host -u$user --password=$password $database
}
getcolumns() {
local l_table="$1"
echo "describe $l_table" | domysql | cut -f1 | grep -v "Field" | grep -v "Warning" | paste -sd "," - 2>/dev/null
}
host="localhost"
mysqlfiles="/var/lib/mysql-files/"
# Parse command line options
while true; do
#echo "option $1"
case "$1" in
# Options without arguments
-h|--help) usage;;
-d|--database) database="$2" ; shift ;;
-t|--tables) tables="$2" ; shift ;;
-o|--output) csvoutput="$2" ; shift ;;
-u|--user) user="$2" ; shift ;;
-hs|--host) host="$2" ; shift ;;
-p|--password) password="$2" ; shift ;;
-gs|--get-schema) option="getschema";;
(--) shift; break;;
(-*) echo "$0: error - unrecognized option $1" 1>&2; usage;;
(*) break;;
esac
shift
done
# Checks
if [ "$csvoutput" == "" ]
then
error "output CSV directory is not set"
fi
if [ "$database" == "" ]
then
error "MySQL database is not set"
fi
if [ "$user" == "" ]
then
error "MySQL user is not set"
fi
if [ "$password" == "" ]
then
error "MySQL password is not set"
fi
color_msg $blue "exporting tables of database $database"
if [ "$tables" = "" ]
then
tables=$(echo "show tables" | domysql)
fi
case $option in
getschema)
rm $csvoutput$database.schema
for table in $tables
do
color_msg $blue "getting schema for $table"
echo -n "$table:" >> $csvoutput$database.schema
getcolumns $table >> $csvoutput$database.schema
done
;;
*)
for table in $tables
do
color_msg $blue "exporting table $table"
cols=$(grep "$table:" $csvoutput$database.schema | cut -f2 -d:)
if [ "$cols" = "" ]
then
cols=$(getcolumns $table)
fi
ssh $host rm $mysqlfiles/$table.csv
cat <<EOF | mysql --host $host -u$user --password=$password $database
SELECT $cols FROM $table INTO OUTFILE '$mysqlfiles$table.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
EOF
scp $host:$mysqlfiles/$table.csv $csvoutput$table.csv.raw
(echo "$cols"; cat $csvoutput$table.csv.raw) > $csvoutput$table.csv
rm $csvoutput$table.csv.raw
done
;;
esac
Standing on the shoulders of Chris Johnson, I extended the answer from Feb 2016 with a custom dialect for reading.
This shell pipeline tool does not need to connect to your database, handles random commas and quotes in the input, and works nicely in Python 2 and Python 3!
#!/usr/bin/env python
import csv
import sys
# fields are separated by tabs; double-quotes may occur anywhere
csv.register_dialect("mysql", delimiter="\t", quoting=csv.QUOTE_NONE)
tab_in = csv.reader(sys.stdin, dialect="mysql")
comma_out = csv.writer(sys.stdout, dialect=csv.excel)
for row in tab_in:
# print("row: {}".format(row))
comma_out.writerow(row)
Use that print statement to convince yourself it's parsing your input correctly :)
A major caveat: treatment of carriage return characters, ^M aka control-M, \r in Linux terms. Although batch-mode MySQL output correctly escapes embedded newline characters, so there is truly one row per line (defined by the Linux newline character \n), MySQL puts no quotes around column data. If a data item has an embedded carriage-return character, csv.reader rejects that input with this exception:
new-line character seen in unquoted field -
do you need to open the file in universal-newline mode?
Please don't @ me saying I should use universal file mode by re-opening sys.stdin.fileno with mode 'rU'. I tried that, and it causes the embedded \r characters to be treated as end-of-record markers, so a single input record is incorrectly transformed into many incomplete output records.
I have not found a Python solution to this limitation of Python's csv.reader module. I think the root cause is the csv.reader implementation/limitation noted in their documentation, csv.reader:
The reader is hard-coded to recognise either '\r' or '\n' as end-of-line,
and ignores lineterminator.
The weak and unsatisfying solution I can offer is to change each \r character to the two-character sequence '\n' before Python's csv.reader sees the data. I used the sed command. Here's an example of a pipeline with a MySQL select and the Python script from above:
mysql -u user db --execute="select * from table where id=12345" \
| sed -e 's/\r/\\n/g' \
| mysqlTsvToCsv.py
After fighting this for some time I think Python is not the right solution. If you can live with Perl, I think the one-liner script offered by artfulrobot may be the most-effective and simplest solution.
If there is PHP installed on the machine you are using, you can write a PHP script to do that. It requires the PHP installation has the MySQL extension installed.
You can call the PHP interpreter from the command line like so:
php --php-ini path/to/php.ini your-script.php
I am including the --php-ini switch, because you may need to use your own PHP configuration that enables the MySQL extension. On PHP 5.3.0+ that extension is enabled by default, so that is no longer necessary to use the configuration to enable it.
Then you can write your export script like any normal PHP script:
<?php
#mysql_connect("localhost", "username", "password") or die(mysql_error());
mysql_select_db("mydb") or die(mysql_error());
$result = mysql_query("SELECT * FROM table_with_the_data p WHERE p.type = $typeiwant");
$result || die(mysql_error());
while($row = mysql_fetch_row($result)) {
$comma = false;
foreach ($row as $item) {
# Make it comma separated
if ($comma) {
echo ',';
} else {
$comma = true;
}
# Quote the quotes
$quoted = str_replace("\"", "\"\"", $item);
# Quote the string
echo "\"$quoted\"";
}
echo "\n";
}
?>
The advantage of this method is, that it has no problems with varchar and text fields, that have text containing newlines. Those fields are correctly quoted and those newlines in them will be interpreted by the CSV reader as a part of the text, not record separators. That is something that is hard to correct afterwards with sed or so.
This solution places the SQL query in a heredoc and pipes the output though a filter:
#!/bin/bash
mysql --defaults-group-suffix=[DATABASE_NAME] --batch << EOF | python query.py
SELECT [FIELDS]
FROM [TABLE]
EOF
This version of the Python filter works without requiring the use of the csv module:
import sys
for line in sys.stdin:
print(','.join(["\"" + str(element) + "\"" for element in line.rstrip('\n').split('\t')]))
This version of the Python filter uses the CSV module and involves slightly more code but is arguably a little bit more clear:
import csv, sys
csv_reader = csv.reader(sys.stdin, delimiter='\t')
csv_writer = csv.writer(sys.stdout, quoting=csv.QUOTE_NONNUMERIC)
for line in csv_reader:
csv_writer.writerow(line)
Or you could use Pandas:
import csv, sys
import pandas as pd
df = pd.read_csv(sys.stdin, sep='\t')
df.to_csv(sys.stdout, index=False, quoting=csv.QUOTE_NONNUMERIC)
For those, who may want to download a query result in CSV format, but doesn't have access the server file but the database.
First of all, it's not a Linux command. Steps are bellow:
Create VIEW v as (Select * from user where status = 0))view section of your database.CSV.Export method: to Custom - display all possible options and check Put columns names in the first row.If you are getting this error while you try to export your file
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
and you are not able to solve this error, you can do one thing by simply running this Python script
import mysql.connector
import csv
con = mysql.connector.connect(
host="localhost",
user="root",
passwd="Your Password"
)
cur = con.cursor()
cur.execute("USE DbName")
cur.execute("""
select col1,col2 from table
where <cond>
""")
with open('Filename.csv',mode='w') as data:
fieldnames=["Field1","Field2"]
writer=csv.DictWriter(data,fieldnames=fieldnames)
writer.writeheader()
for i in cur:
writer.writerow({'Field1':i[0],'Field2':i[1]})
This one avoids having to write output to a file, only requires expat to be installed, properly escapes values, and outputs empty string (instead of a literal NULL) for null values.
You tell MySQL to output the results in XML format (using the --xml flag), and then pipe the results through the C program below.
This should also be pretty close to the fastest possible way to do this.
// mysql-xml-to-csv.c
#include <assert.h>
#include <ctype.h>
#include <err.h>
#include <expat.h>
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <unistd.h>
/*
Example of MySQL XML output:
<?xml version="1.0"?>
<resultset xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" statement="SELECT id as IdNum, lastName, firstName FROM User">
<row>
<field name="IdNum">100040</field>
<field name="lastName" xsi:nil="true"/>
<field name="firsttName">Cher</field>
</row>
</resultset>
*/
#define BUFFER_SIZE (1 << 16)
// These accumulate the first row column names and values until first row is entirely read (unless the "-N" flag is given)
static XML_Char **column_names;
static size_t num_column_names;
static XML_Char **first_row_values;
static size_t num_first_row_values;
// This accumulates one column's value
static XML_Char *elem_text; // note: not nul-terminated
static size_t elem_text_len;
// Flags
static int first_column;
static int reading_value;
// Expat callback functions
static void handle_elem_start(void *data, const XML_Char *el, const XML_Char **attr);
static void handle_elem_text(void *userData, const XML_Char *s, int len);
static void handle_elem_end(void *data, const XML_Char *el);
// Helper functions
static void output_csv_row(XML_Char **values, size_t num);
static void output_csv_text(const char *s, size_t len);
static void add_string(XML_Char ***arrayp, size_t *lengthp, const XML_Char *string, size_t len);
static void add_chars(XML_Char **strp, size_t *lenp, const XML_Char *string, size_t nchars);
static size_t xml_strlen(const XML_Char *string);
static void free_strings(XML_Char ***arrayp, size_t *lengthp);
static void usage(void);
int
main(int argc, char **argv)
{
char buf[BUFFER_SIZE];
int want_column_names = 1;
XML_Parser p;
FILE *fp;
size_t r;
int i;
// Parse command line
while ((i = getopt(argc, argv, "hN")) != -1) {
switch (i) {
case 'N':
want_column_names = 0;
break;
case 'h':
usage();
exit(0);
case '?':
default:
usage();
exit(1);
}
}
argv += optind;
argc -= optind;
switch (argc) {
case 0:
fp = stdin;
break;
case 1:
if ((fp = fopen(argv[0], "r")) == NULL)
err(1, "%s", argv[0]);
break;
default:
usage();
exit(1);
}
// Initialize arrays for column names and first row values
if (want_column_names) {
if ((column_names = malloc(10 * sizeof(*column_names))) == NULL)
err(1, "malloc");
if ((first_row_values = malloc(10 * sizeof(*first_row_values))) == NULL)
err(1, "malloc");
}
// Initialize parser
if ((p = XML_ParserCreate(NULL)) == NULL)
errx(1, "can't initialize parser");
XML_SetElementHandler(p, handle_elem_start, handle_elem_end);
XML_SetCharacterDataHandler(p, handle_elem_text);
// Process file
while (1) {
if ((r = fread(buf, 1, sizeof(buf), fp)) == 0 && ferror(fp))
errx(1, "error reading input");
if (XML_Parse(p, buf, r, r == 0) == XML_STATUS_ERROR)
errx(1, "line %u: %s", (unsigned int)XML_GetCurrentLineNumber(p), XML_ErrorString(XML_GetErrorCode(p)));
if (r == 0)
break;
}
// Clean up
XML_ParserFree(p);
fclose(fp);
// Done
return 0;
}
static void
handle_elem_start(void *data, const XML_Char *name, const XML_Char **attr)
{
if (strcmp(name, "row") == 0)
first_column = 1;
else if (strcmp(name, "field") == 0) {
if (column_names != NULL) {
while (*attr != NULL && strcmp(*attr, "name") != 0)
attr += 2;
if (*attr == NULL)
errx(1, "\"field\" element is missing \"name\" attribute");
add_string(&column_names, &num_column_names, attr[1], xml_strlen(attr[1]));
} else {
if (!first_column)
putchar(',');
putchar('"');
}
reading_value = 1;
}
}
static void
handle_elem_text(void *userData, const XML_Char *s, int len)
{
if (!reading_value)
return;
if (column_names != NULL)
add_chars(&elem_text, &elem_text_len, s, len);
else
output_csv_text(s, len);
}
static void
handle_elem_end(void *data, const XML_Char *name)
{
if (strcmp(name, "row") == 0) {
if (column_names != NULL) {
output_csv_row(column_names, num_column_names);
output_csv_row(first_row_values, num_first_row_values);
free_strings(&column_names, &num_column_names);
free_strings(&first_row_values, &num_first_row_values);
} else
putchar('\n');
} else if (strcmp(name, "field") == 0) {
if (column_names != NULL) {
add_string(&first_row_values, &num_first_row_values, elem_text, elem_text_len);
free(elem_text);
elem_text = NULL;
elem_text_len = 0;
} else
putchar('"');
first_column = 0;
reading_value = 0;
}
}
static void
output_csv_row(XML_Char **values, size_t num_columns)
{
int i;
for (i = 0; i < num_columns; i++) {
if (i > 0)
putchar(',');
putchar('"');
output_csv_text(values[i], xml_strlen(values[i]));
putchar('"');
}
putchar('\n');
}
static void
output_csv_text(const XML_Char *s, size_t len)
{
while (len-- > 0) {
if (*s == '"')
putchar('"');
putchar(*s);
s++;
}
}
static void
add_string(XML_Char ***arrayp, size_t *lengthp, const XML_Char *string, size_t nchars)
{
char **new_array;
if ((new_array = realloc(*arrayp, (*lengthp + 1) * sizeof(**arrayp))) == NULL)
err(1, "malloc");
*arrayp = new_array;
if (((*arrayp)[*lengthp] = malloc((nchars + 1) * sizeof(XML_Char))) == NULL)
err(1, "malloc");
memcpy((*arrayp)[*lengthp], string, nchars * sizeof(XML_Char));
(*arrayp)[*lengthp][nchars] = (XML_Char)0;
(*lengthp)++;
}
static void
add_chars(XML_Char **strp, size_t *lenp, const XML_Char *string, size_t nchars)
{
XML_Char *new_array;
if ((new_array = realloc(*strp, (*lenp + nchars) * sizeof(XML_Char))) == NULL)
err(1, "malloc");
*strp = new_array;
memcpy(*strp + *lenp, string, nchars * sizeof(XML_Char));
*lenp += nchars;
}
static size_t
xml_strlen(const XML_Char *string)
{
size_t len;
len = 0;
while (string[len] != (XML_Char)0)
len++;
return len;
}
static void
free_strings(char ***arrayp, size_t *lengthp)
{
while (*lengthp > 0)
free((*arrayp)[--*lengthp]);
free(*arrayp);
*arrayp = NULL;
}
static void
usage(void)
{
fprintf(stderr, "Usage: mysql-xml-to-csv [options] [file.xml]\n");
fprintf(stderr, "Options:\n");
fprintf(stderr, " -N\tDo not output column names as the first row\n");
fprintf(stderr, " -h\tShow this usage info\n");
}
For those who don't work with C very often, you can build this code by running the following (assuming you have the expat library installed):
gcc mysql-xml-to-csv.c -lexpat -o mysql-xml-to-csv
Tested with openSUSE 15.2 and gcc 7.5.0.
Update: Now available as an open source project on github.
This is dirty and ugly. It is only suitable for the particular situation where all you have is a PHP-*-admin and the server is running with the --secure-file-priv option, so then you cannot use the INTO OUTFILE '/path/to/export.csv' clause in your query.
What you can do is parse lines of CSV with ... wait for it!, CONCAT, then copy the results and paste them into a file.
Here's an example, where I needed SQL format (it'd be trivial to adapt it for CSV):
SELECT CONCAT(
"('",
`username`, "', '",
`password`, "', '",
`first_name`, "', '",
`last_name`, "', '",
`gender`, "'),"
) AS `row`
FROM `users`
WHERE `role` != 'Not set'
AND `user_status` = 'Active'
ORDER BY `role`, `gender`, `user_id`
LIMIT 200
That gives nice, ready for import output similar to this:
('jane', '3d7ff...', 'Jane', 'Doe', 'Female'),
('john', 'd2a33...', 'John', 'Doe', 'Male'),
...
If you are on production or any other server with no access to a file system, you can use this simple trick and a little bit of manual effort to get what you want.
Step 1. Just wrap all the columns under CONCAT and use as CSVFormat option provided by MySQL to get comma-delimited results (or use any delimiter you want). Here is an example:
SELECT
CONCAT(u.id,
',',
given,
',',
family,
',',
email,
',',
phone,
',',
ua.street_number,
',',
ua.route,
',',
ua.locality,
',',
ua.state,
',',
ua.country,
',',
ua.latitude,
',',
ua.longitude) AS CSVFormat
FROM
table1 u
LEFT JOIN
table2 ua ON u.address_id = ua.id
WHERE
role_policy = 31 and is_active = 1;
Step 2. Copy results from your terminal to a file and clean up all the pipe characters (that forms the layout of your results) using any text editor.
Step 3. Save as .csv file and that's it.
You can use the below command from your SQL editor/Terminal:
mysql -h(hostname/IP>) -u(username) -p(password) databasename <(query.sql) > outputFILE(.txt/.xls)
E.g.,
hostname -x.x.x.x
uname - username
password - password
DBName - employeeDB
queryFile - employee.sql
outputFile - outputFile.xls
mysql -hx.x.x.x -uusername -ppassword employeeDB< employee.sql> outputFile.xls
Make sure you are executing the command from the directory where SQL query is located or mention the full path of the SQL query location in the above command.