Export MySQL data to CSV file

As much as importing data into MySQL is helpful, you also might like to export your data to CSV file format that is widely supported. You can open your data with spreadsheet like Excel, Libreoffice Calc, Google sheets and more. There are various techniques of how to export a MySQL table to a CSV file, but I'm going to demonstrate two methods.

Method 1: MySQL

Following SQL query will dump all the records from a table called "person" into the file /tmp/export_person.csv as a CSV file.

SELECT * from person INTO OUTFILE '/tmp/export_person.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM test_table;

Take note of that the directory must be writable by the MySQL database server. Also that it will not overwrite the file if it already exists.

Method 2: Command line

You can also use command line in Linux to get your report. To get the same data as the example above

$ mysql -u dbusername -pdbpassword exampledb -B -e "select * from \'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > export_person.csv

• mysql : the executable file (should be where MySQL is installed)
• -u dbusername : a switch and option for the username to execute the SQL with
• -p dbpassword : a switch and option for the password of the user
• exampledb : the database to run the SQL against
• -B : instructs that the output should be tab delimited (we will convert this to commas later in the command)
• -e “the sql statement here” : the SQL statement to run returning your data
• | sed ’s/\t/”,”/g;s/^/”/;s/$/”/;s/\n//g’ : sed is a Unix stream processor, essentially allow for transformations in this case. Here we have four sed commands that change the tabs to double quotes, adds double quotes to the beginning and end of each line and adds a new line marker at the end of each line.
• > export_person.csv : outputs the results to the file named export_person.csv

I hope that this is somewhat helpful.

See also Import csv to MySQL

Import csv to MySQL with php

I have small project at work where I need to import data from over 80 excel document and from more than one sheet from each into database. And I also needed to enter data from the web form to the database at the same time to identify each port with equipment and location. After converting sheet from .xls to .cvs I import the file by upload it trough the web form.
Not very hard task but I had some issues that I came a cross.
There is a simple method to import a file to database. From the command line I would do this.

mysql > LOAD DATA INFILE 'filename' INTO TABLE tablename FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\n' (field1,field2,field3);

Ok, this was easy but. In my case it seams that the file neaded to be in /var/lib/mysql/databasename for this command to work and I did not figure out how to get the file there when I uploaded it with php, guess it's permission issue and i'm not going to change them. Adding LOCAL to the command is the way to import the file from my directory, but it considered an administrative task and it returned error.

In the connection parameters I had to add "false,128" to get it to work.

$connect = mysql_connect("localhost","user","pass",false,128);

mysql_select_db("database",$connect);

 mysql_query("LOAD DATA LOCAL INFILE 'upload/$filename' INTO TABLE database FIELDS TERMINATED BY ',' ENCLOSED BY '\"' LINES TERMINATED BY '\\n' (field1,field2,field3) set field4='value1',field5='value2';") ;

Now it works like a charm.
--cheers

See also how to export MySQL data to csv file