CODEWITHSUNDEEP
mysqlcsvexportfile-location
TCulos
TCulos

Reputation: 183

MySQL(5.6) "select * into outfile.." not creating files

When I use this command on my local host as the root user it runs without issue but I can't seem to find the actual file.

SELECT * INTO OUTFILE 'C:\...\tableName.txt' 
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
FROM tableName;

When I try to run the query again it says the file is already created even when it's obviously not.

EDIT: fixed my query syntax

Upvotes: 1

Views: 4383

Answers (2)

Drew
Drew

Reputation: 24949

SELECT r INTO OUTFILE 'c:\dev\myA2.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM a2;

-- leads me to believe it works first time with rowcount output
-- but it does not put file in dev
-- run it again it says error 1086: File 'c:devmyA2.txt' already exists

so that means it wrote it to c:

the default of what mysql query engine has for c: at that time

I did not hunt for it !

the following works great (note the double slashes \\):

SELECT r INTO OUTFILE 'c:\\dev\\drew_so_answers\\myA2.txt'
  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
  LINES TERMINATED BY '\n'
  FROM a2;

Upvotes: 1

BK435
BK435

Reputation: 3176

There is an unnecessary FROM after your *. your query should look more like this: 

SELECT * INTO OUTFILE 'C:\...\tableName.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\r\n'
FROM tableName;

Note: make sure that mysql has permission to write to 'C:\...\tableName.txt'

As for the file already being created error:

The file may have been created in another directory where mysql actually does have permission to write to, such as the data directory. This is why you are getting the message that the file has already been created now that you have run the query more than once.

From mysql command line run show variables like '%dirdata%';, you should see output that looks something like: 

mysql> show variables like '%datadir%';
+---------------+-------------------------------------+
| Variable_name | Value                               |
+---------------+-------------------------------------+
| datadir       | c:\wamp\bin\mysql\mysql5.6.17\data\ |
+---------------+-------------------------------------+
1 row in set (0.35 sec)

Navigate in windows to that folder and you should find your file there.

Upvotes: 1

Related Questions