export from MYSQL to CSV

Question

How do I export from MYSQL to CSV in mariadb on Centos 7?

I am using:

Server version      5.5.37-MariaDB
Protocol version    10
Connection          Localhost via UNIX socket
UNIX socket         /var/lib/mysql/mysql.sock

on CentOS Linux release 7.0.1406 (Core)

I tried the following:

    mysql> SELECT *
        -> INTO OUTFILE 'cd /var/foo.csv'
        -> FIELDS TERMINATED BY ','
        -> ENCLOSED BY '"'
        -> ESCAPED BY '\\'
        -> LINES TERMINATED BY '\r\n'
        -> FROM foo;
    Query OK, 4079 ROWS affected (0.02 sec)

Where foo is the database name? But it gives me a syntax error. Do I not have to select a particular table?

Then I tried this :

mysql -u root -p enterpass nameofdb -B -e “select * from \`person\`;” | sed     ‘s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ > nameofdb.csv

Same again with syntax error.

I am practicing this so I can see if I could manually migrate one kind of database store into another. A zen cart db to an open cart one without having to use some paid product like cart2cart and mainly so I can see the guts of the stores to better understand.

which sytax error on first example ? your filename is cd /var/foo.csv or just /var/foo.csv ? (without cd) — Archemar
– Archemar, Commented Nov 5, 2014 at 10:05
thanks, will try without cd and for second one it says "unknown command: `?'". — cea
– cea, Commented Nov 5, 2014 at 12:16
select syntax will export a table not a database. have you tried with a single table ? (or is foo you table, not your database ? ). — Archemar
– Archemar, Commented Nov 6, 2014 at 7:57
On second tough, migrating from one database to another is usually done by mysqldump > foo.sql old database/engine ; mysql < foo.sql new database/engine. — Archemar
– Archemar, Commented Nov 6, 2014 at 7:59

cea · Accepted Answer · 2014-11-06 08:36:32Z

Seeing no-one else is answering I will but to be fair Archemar worked it out before me. I knew I needed to select a table but was unsure of syntax and didn't consider making sure folder apache owned.

To export mysql database table to CSV file you log in to admin then use following commands.

SELECT *
INTO OUTFILE '/tmp/products.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
FROM products;

It then left three files like this "systemd-private-SjP6B1" , one of which had tmp folder and within that products.csv.

I could open the products.csv in google docs or excel if I had it.

Stack Exchange Network

export from MYSQL to CSV

1 Answer 1

You must log in to answer this question.

Hot Network Questions

export from MYSQL to CSV

1 Answer 1

You must log in to answer this question.

Related

Hot Network Questions