40

Can I generate a CSV file from phpMyAdmin based on a MySQL query?

For example, let's say I queried a table to return results for the word "image". Could I then produce a CSV with all of the records containing the word "image"?

random
  • 9,324
  • 10
  • 63
  • 77
Jane
  • 897
  • 3
  • 9
  • 23
  • possible duplicate of [How to output MySQL query results in csv format?](http://stackoverflow.com/questions/356578/how-to-output-mysql-query-results-in-csv-format) – FerranB Jun 04 '11 at 23:17
  • 1
    This is specific to PhpMyAdmin. – SamT Jun 04 '11 at 23:20

5 Answers5

64

In PhpMyAdmin, go into the SQL tab and enter your query in there. Hit go, then click Export at the bottom of your results. You can select to export as a CSV.

In case you're interested, here's how to do it via SQL without PMA: How to output MySQL query results in CSV format?

Community
  • 1
  • 1
SamT
  • 9,464
  • 2
  • 29
  • 37
  • 6
    id does not work for big amount of data, when I click export: No records were selected. I cannot select 20k records because firefox crash(out of mem) – Kamil Nekanowicz Apr 11 '17 at 10:07
  • 11
    @user3871754 and for other that encounter this problem, there are 2 export buttons below the results, one that export the selected rows, the other one, in a second bar below, that export everything. It's next to "print" and "copy to clipboard". – Marco Martinelli Jun 11 '18 at 07:50
  • 1
    To add to @MarcoMartinelli's helpful comment, the "Export" link you want is located in a box labeled “Query results operations”. (There are at least two other "Export" links on the results page that invoke other behaviors, so it's important to find the right one.) This answer has a helpful screenshot if you're still looking: https://serverfault.com/a/300342/481427 – Sarah Lewis Aug 01 '18 at 18:54
21

You may be able to use the SELECT ... INTO OUTFILE... functionality. Although this will place the CSV file on the server. That's a long page, because it's the page for the whole "Select" syntax, but the basics are below:

SELECT col1,col2,col3 INTO OUTFILE '/tmp/result.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM MyTable;
Yugal Jindle
  • 39,295
  • 39
  • 121
  • 191
Kibbee
  • 62,900
  • 26
  • 139
  • 178
7
create table tmp_export
SELECT * from table_name WHERE column_name .....

It created a table and then I exported the table as CSV. This solution worked fine for me.

Brian Tompsett - 汤莱恩
  • 5,195
  • 62
  • 50
  • 120
Elango Paul Victor
  • 71
  • 1
  • 2
2

What also works well is creating a table with the query and then export the table as usual, having all the options of phpmyadmin export available. Simply do something like this in SQL box of phpmyadmin

create table tmp_export
select * from xxxx

No problems with complex queries and large datasets using this approach.

Beatroot
  • 464
  • 4
  • 6
-1

use adminer, adminer has option to export query results: https://www.adminer.org/

Shahbaz
  • 306
  • 5
  • 15