MySQL is a popular open source enterprise ready database. There are many tools available for interacting with this database, but as with most enterprise databases most access happens via Structured Query Language. There are a few options to export data to CSV from MySQL. Some require third part tools, while another uses a command line tool (useful on Unix machines) and a final option via SQL. Lets take a look at the last two options:
MySQL command line tool (on Unix):
mysql -u exampleuser -p letmein exampledb -B -e "select * from
\'person\';" | sed 's/\t/","/g;s/^/"/;s/$/"/;s/\n//g' > filename.csv
So what does all of that mean?
- mysql : the executable file (should be where MySQL is installed)
- -u exampleuser : a switch and option for the username to execute the SQL with
- -p letmein : 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.
- > filename.csv : outputs the results to the file named filename.csv
MySQL SQL Syntax:
SELECT column_a,column_b,column_c+column_d INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
The MySQL select statement is using an INTO OUTFILE command to output the results of the SQL select into a file (in this case /tmp/result.csv).
A couple of notes:
- In both cases the file is saved to a location local to where the database is being ran from (on the same machine, or to a mount/drive available to the machine).
- The header row will need to be added to both files (which can be done using any text editor or spreadsheet program).
back to top