3

I am using Sequel pro to connect to a remote server on which the mysql database is running. I am using Mac and the server has Linux installed. I want to store that result (i.e. the table that you can see in the picture) as a text file on my Mac. How can I do that? Attached is the screen shot of my query in sequel pro.

Screeon shot of the sequel pro

the Tin Man
  • 158,662
  • 42
  • 215
  • 303
HimanAB
  • 2,443
  • 8
  • 29
  • 43

6 Answers6

5

After you have run the query you can export it under "Datei"-> Export and select the fileformat that you want

Bernd Buffen
  • 14,525
  • 2
  • 24
  • 39
5

There is an export menu at the bottom of query results:

Export query from Sequel Pro

hoju
  • 28,392
  • 37
  • 134
  • 178
2

Based on MySQL Documentation there two ways for this

SELECT ... INTO OUTFILE

writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format.

SELECT ... INTO DUMPFILE

writes a single row to a file without any formatting.

Example Query

SELECT * INTO OUTFILE '/some_path/results.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM `test_table`;
  • 1
    Does this store the file on the server (the machine that the database is running on) or on my Mac? – HimanAB Jun 24 '16 at 18:17
  • @HimanUCC This stores it to the server where MySQL is installed, you can download that provided that you can access the folder where it is stored. –  Jun 24 '16 at 18:20
  • The problem is I don't want to store it on the server. – HimanAB Jun 24 '16 at 18:21
  • For example `/some_path/results.txt` is a path on the remote server that you have to access (in case of web server this could be inside public_html folder) @HimanUCC –  Jun 24 '16 at 18:22
  • If sequel pro can export table schema therefor it should be able to export your query results @HimanUCC –  Jun 24 '16 at 18:26
  • 1
    From sequel pro documentation, `Optional: You can click the little Gear button at the bottom of the results and and Export Result as CSV.` @HimanUCC –  Jun 24 '16 at 18:29
2

Workaround to export your custom result set as SQL:

I came here wondering how to export the custom results as SQL because the Export Results menu only provides the option for CSV or XML. The export as SQL option is only for an entire table or columns within a table. So what I ended up doing as a workaround was first exporting as CSV, then re-importing that CSV data into Sequel Pro into a separate table (or an empty table that already exists), and then finally exporting the table (now populated with my custom results set) as a SQL export.

Allen
  • 2,717
  • 2
  • 30
  • 34
1

If you want the query results saved as a ready to import SQL statement you can:

  1. select/highlight the rows you want from the search results window
  2. right/context click the selection
  3. select the "Copy as SQL INSERT" option
  4. paste the contents of your system clipboard into a text file
  5. save the new file with the .sql extension

Credit - Greg Davidson https://www.endpointdev.com/blog/2014/01/copy-data-between-mysql-databases-with/

Jim
  • 11
  • 3
  • Although it is unclear that SQL was the desired export format of the original poster, I just wanted to say that this was exactly what I was looking how to do and it's super easy. Thank you so much! I couldn't even find this functionality described on the sequelpro.com website. – bou2you May 06 '20 at 13:37
  • @Jim Note that the article you reference, "Copy Data Between MySQL Databases with Sequel Pro", has moved to: https://www.endpointdev.com/blog/2014/01/copy-data-between-mysql-databases-with/ – Jon Jensen Dec 05 '21 at 01:37
  • @JonJensen - Thanks for letting me know. I have updated my post to reflect the updated URL. – Jim Dec 06 '21 at 18:17
0

In my case:

  1. Duplicate Table
  2. Run SQL
INSERT INTO {{table_duplicated}} (column1, column2, ...) FROM {{table}} where {{WHERE CONDITIONS...}}}
  1. Export the duplicated table
Codus
  • 1,433
  • 1
  • 14
  • 18