How to save MySQL query output to excel or .txt file? [duplicate]

by

From Save MySQL query results into a text or CSV file:

MySQL provides an easy mechanism for writing the results of a select
statement into a text file on the server. Using extended options of
the INTO OUTFILE nomenclature, it is possible to create a comma
separated value (CSV) which can be imported into a spreadsheet
application such as OpenOffice or Excel or any other application which
accepts data in CSV format.

Given a query such as

SELECT order_id,product_name,qty FROM orders

which returns three columns of data, the results can be placed into
the file /tmp/orders.txt using the query:

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.txt'

This will create a tab-separated file, each row on its own line. To
alter this behavior, it is possible to add modifiers to the query:

SELECT order_id,product_name,qty FROM orders
INTO OUTFILE '/tmp/orders.csv'
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

In this example, each field will be enclosed in double quotes, the
fields will be separated by commas, and each row will be output on a
new line separated by a newline (\n). Sample output of this command
would look like:

"1","Tech-Recipes sock puppet","14.95" "2","Tech-Recipes chef's hat","18.95"

Keep in mind that the output file must not already exist and that the
user MySQL is running as has write permissions to the directory MySQL
is attempting to write the file to.

Syntax

   SELECT Your_Column_Name
    FROM Your_Table_Name
    INTO OUTFILE 'Filename.csv'
    FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
    LINES TERMINATED BY '\n'

Or you could try to grab the output via the client:

You could try executing the query from the your local client and
redirect the output to a local file destination:

mysql -user -pass -e "select cols from table where cols not null" > /tmp/output

Hint: If you don’t specify an absoulte path but use something like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv', it will store the output file to the directory specified by show variables like 'datadir';.

More Related Contents:

  1. How to reduce the size of google spreadsheet using SQL?
  2. How to import an excel file in to a MySQL database
  3. Exporting results of a Mysql query to excel?
  4. How can VBA connect to MySQL database in Excel?
  5. MySQL export into outfile : CSV escaping chars
  6. How to use IN & NOT IN same query [closed]
  7. SQL Print the name of all Employees together with the name of their supervisor
  8. MySQL: Large VARCHAR vs. TEXT?
  9. MySQL offset infinite rows
  10. Multiple-column foreign key in MySQL?
  11. What is the question mark’s significance in MySQL at “WHERE column = ?”?
  12. How do write IF ELSE statement in a MySQL query
  13. mysql – making a mechanism similar to Oracle’s seqences
  14. How do you uninstall MySQL from Mac OS X?
  15. Get the latest date from grouped MySQL data
  16. UUID performance in MySQL?
  17. MySQL: NULL vs “”
  18. Optimal MySQL settings for queries that deliver large amounts of data?
  19. mysql configuration stops at “starting server”
  20. MySQL Error 1264: out of range value for column
  21. Like Case Sensitive in MySQL
  22. What is the best way to delete old rows from MySQL on a rolling basis?
  23. What are the known ways to store a tree structure in a relational DB? [closed]
  24. Illegal mix of collations error in MySql
  25. mysql equivalent data types
  26. Get difference in years between two dates in MySQL as an integer
  27. use a variable for table name in mysql sproc
  28. INSERT INTO Table from multiple tables
  29. Mysql How do you create a clustered index?
  30. Counting associated entries with Sequelize

Leave a Comment