Output a MySQL result set to a .csv file
This is quite sweet. You need to get some table data straight out to a CSV for manipulating in OpenOffice Calc, or similar products. But how?
Connect to your SQL server using a command line, ususally
mysql -u root -p
enter your password. Show your databases using
SHOW DATABASES;
Switch to your database using
USE my_database_name;
Now show your tables if you like..
SHOW TABLES;
Now get your CSV out from the table results of a query thus..
SELECT Name, Surname, Address, Postcode, Telephone
FROM Customers
INTO OUTFILE '/tmp/customers.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Et voila, data dumped to /tmp/customers.csv! Nice.
OK, funny / cool but how do I get .CSV file to my PC?
Understood. I use Linux Mint, but in any Debian, Ubuntu, Mint system, open your file manager (or Thunar in Mint-speak) and put
sftp://your_username@your.server.name.com/tmp/
Enter any password required for the target server and voilà, you’re on the /tmp directory on the target server! From there, you can copy / paste the file as you wish,