Friday, September 30, 2011

Ways to Export Output from MySQL

Different ways to export or take output from MySQL command line client.

1. Photo
The most simple way is to capture the photo of the table and paste it in your document.
→ Press the print screen button
→ Crop it according to your needs using any photo editor
→ Paste it in your document

2. Mark
Second way is to mark the commands or table to get the output
→ Right click on MySQL command line client and select 'Mark'.
→ Mark the desired portion and press Enter to copy(or ctrl + c)
→ Now just paste it anywhere you want.
Marking in MySQL Client


3. Outfile
This allow you to export output that will be the result of a Select statement by just adding the INTO OUTFILE command in the query. You can also export the table in csv(excel) format.
→ Type your select statement with the suitables clauses.
→ At the end of the statement you can add the following commands - INTO OUTFILE 'file name.txt'
mysql> SELECT * from staff where age>25 order by staff_no INTO OUTFILE 'd:\staff.txt' ;
staff.txt-Data not arranged

This will give the output in staff.txt file that you can open in notepad. But the problem here is that the rows will be in continuation and there will be separations between column values. And if you try to export it as .csv file, it will combine the result in a single coloumn. To tackle this we use some export options.
mysql> SELECT * INTO OUTFILE '\staff.txt' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' from staff; 
staff.txt-Data arranged
Now, the fileds will be seperated by a comma(,) and row will be seperated be a new line.
By using these export options you can also export output to a csv file without the values getting combined.
mysql> SELECT * INTO OUTFILE '\staff.csv' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' from staff;
Now, if you open the staff.csv file you can see the all your data arranged properly into columns without the data being combined. OPTIONALLY ENCLOSED BY '"' ensures that each string value is enclosed in double-quotes("").



4. Tee command
Entire Session of MySQL Client
If you want to save output for more than one query you can use the tee command. Using this command you can save your entire MySQL client session or part of it. Moreover, you will be able to store both the query passed and its result.
mysql>tee /staff.txt;
Logging to file '/staff.txt'
After, you write this code you will be logged to staff.txt and your entire session will be stored in the text file.

To disable logging or to log out of tee command use \t


2 comments:

  1. Dude its awesome. But the '\n' command doesn't work in both netbeans & MySQL command line client.
    Could you please check that one....

    ReplyDelete