Search code examples
mysqlcsvexportfile-location

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

Solution

  • 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.