Questions tagged [into-outfile]

MySQL's `SELECT ... INTO OUTFILE` command lets you quickly dump the results of a MySQL query into a file on the MySQL server. It generally should not be used for production use.

MySQL's SELECT ... INTO OUTFILE command lets you quickly dump the results of a MySQL query into a file on the MySQL server. It generally should not be used for production use.

From the MySQL documentation

The SELECT ... INTO OUTFILE 'file_name' form of SELECT writes the selected rows to a file. The file is created on the server host, so you must have the FILE privilege to use this syntax. file_name cannot be an existing file, which among other things prevents files such as /etc/passwd and database tables from being destroyed.

One must be able have access to the MySQL server machine, and permissions to operate on files as the mysql user in order to access and manage the dump files. These restrictions mean the SELECT ... INTO OUTFILE command is generally suitable only for testing and debugging.

If you want a dump of a table from a MySQL database on your client machine, you can call the mysqldump binary on the client. If you want a CSV of the results of a MySQL query, you generally need to roll your own code to create that file in your client application, though there are libraries that can help with the CSV format.

139 questions
9
votes
4 answers

MySQL how do you append to a file with INTO OUTFILE?

I have the following code: SELECT * INTO OUTFILE'~/TestInput/Results.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' FROM Results; Desired results are to continually keep on appending to Results.csv
stackoverflow
  • 18,348
  • 50
  • 129
  • 196
9
votes
1 answer

MySQL SELECT INTO OUTFILE Export options

Does anyone know where can I find the documentation for all the export options of the SELECT ... OUTFILE statement of MySQL? I have noticed in multiple questions parameters such as FIELDS ENCLOSED BY delimiter FIELDS ESCAPED BY delimiter FIELDS…
georgepsarakis
  • 1,927
  • 3
  • 20
  • 24
9
votes
4 answers

When Selecting everything from MySQL can I rename specific fields and still select everything else as is with the *

Hey again guys. So, I'm trying to find out how to select every field across multiple tables in a mysql database and output the resulting table to a .csv file for Excel. I've found the infamous stackoverflow question dealing with .csv ouput and have…
Ryan
  • 970
  • 15
  • 36
9
votes
2 answers

exporting a table in MySQL with columns that have newline characters

I am pretty inexperienced in SQL, so there should be a simple solution to my problem: I am selecting a table into a comma-separated file, and the column of type TEXT has newline characters, so when I try to import my csv into Excel, it creates…
Lenik
  • 1,518
  • 1
  • 16
  • 24
9
votes
2 answers

MySQL SELECT INTO OUTFILE to a different server?

I have a shell script on server a. The script spits out a csv file to a local directory. The problem is the database server is on server b. How do I use select * into outfile in a setup like this? I get Can't create/write to file…
ThinkCode
  • 7,841
  • 21
  • 73
  • 92
7
votes
4 answers

How to append data from SQL to an existing file

SQL has the option to dump data into a file, using the INTO OUTFILE option, for exmaple SELECT * from FIshReport INTO OUTFILE './FishyFile' The problem is, this command is only allowed if the file didn't exist before it. It creates the file and…
n00b programmer
  • 2,671
  • 7
  • 41
  • 56
7
votes
2 answers

Show Databases/Tables INTO OUTFILE

Is there a way of getting the output of a SHOW DATABASES or SHOW TABLES command to output to a text file, similar to how the SELECT ... INTO OUTFILE works? The INTO OUTFILE produces a syntax error when used on a SHOW command. I am open to external…
Hearth
  • 383
  • 1
  • 4
  • 13
6
votes
1 answer

MySQL problema. Can't locate file generated by select into outfile '/tmp/aa.txt'

I ran grant file on my user. I'm using absolute path. Mysql user have privileges to write at /tmp. I just can't locate the file. I ran updatedb and then locate aa.txt, with no result. I'm running: select 'aaaaaa' into outfile '/tmp/aa.txt'; Mysql…
Diogo Melo
  • 1,735
  • 3
  • 20
  • 29
6
votes
2 answers

MySQL select into outfile does not write the file to the directory that I choose

The following command: select * INTO OUTFILE '\home\user1\NetBeansProjects\project1\dumps\theData.csv' FIELDS TERMINATED by ',' LINES TERMINATED BY '\n' from database1 Does not write a file called theData.csv to the specified directory.…
CodeKingPlusPlus
  • 15,383
  • 51
  • 135
  • 216
5
votes
2 answers

How to change secure-file-priv option in MySQL on Ubuntu 20.04

I am trying to use OUTFILE on Ubuntu 20.04 and getting this error: MySQL server version: 8.0.21 Code: mysql> select * into OUTFILE '/home/yash/Desktop/data2.txt' from ticket; ERROR 1290 (HY000): The MySQL server is running with the…
Yash Panchal
  • 51
  • 1
  • 1
  • 2
5
votes
2 answers

Remove "\N"s when doing SELECT INTO OUTFILE

I'm doing a SELECT INTO OUTFILE and it's showing a "\N" for every NULL value. Is there any way for me to make it just be blank instead? I'm on MySQL.
Jason Swett
  • 43,526
  • 67
  • 220
  • 351
5
votes
4 answers

Where does MySQL on OSX write outfiles by default?

I'm trying to write some data from a MySQL select statement to a file on a Mac running Snow Leopard. select date_base, fractile_v2, gics, count(gvkey_iid) from master where fractile_v2 <= 15 and fractile_v2 != 0 group by date_base, gics,…
getting-there
  • 1,409
  • 4
  • 18
  • 23
4
votes
2 answers

MySQL syntax problem in "SELECT * FROM INTO FILE"

I'm trying to move tables between two databases and I'm using this command that is given by MySQL : SELECT * INTO OUTFILE '/tmp/result.txt' FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' FROM…
Morano88
  • 2,047
  • 4
  • 25
  • 44
4
votes
1 answer

mysql outfile column with leading zeros

I am trying to export data into a csv file but my leading zeros are getting chopped... I CANNOT modify the data after it is pulled so LPAD is not an option. Also, ID is not a set length... it could be 6 chars long, or up to 11. It may have letters…
Katherine C
  • 245
  • 2
  • 11
3
votes
2 answers

MySQLdump with WINDOWS10 : OS errno 13 - (Permission denied) when executing 'SELECT INTO OUTFILE'

I am unable to write tab separated text files into specified TSV/ folder using mysqldump on WINDOWS10 (MySQL80 service). The Command prompt (or powershell) is in admin mode. secure-file-priv ="" in my.ini MySQL config file. MySQL was installed…
Alexis
  • 1,343
  • 12
  • 15
1
2
3
9 10