CODEWITHSUNDEEP
phpmysqlfilefile-io
user1460822
user1460822

Reputation: 573

Can't find the file created by outfile in MySQL

I am using the following query to create a CSV file 

SELECT email INTO OUTFILE "mydata.csv"
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY "\n"
FROM users;

But i am unable to find the mydata.csv file anywhere when i am searching through filezilla.

Any idea where this file is getting stored?

The query runs successfully without any errors! Any Help?

Upvotes: 54

Views: 69780

Answers (8)

Rick
Rick

Reputation: 7506

If you don't specify an absoulte path but use something like INTO OUTFILE 'output.csv' or INTO OUTFILE './output.csv', it will store the output file to the directory specified by show variables like 'datadir';.

Upvotes: 1

WEBjuju
WEBjuju

Reputation: 6581

+1 to @michael-berkowski and the answer i needed for windows was (as he stated)

MariaDB's data directory

to be specific, mine was in the data folder for database named "mydatabase"

C:\Program Files\MariaDB 10.3\data\mydatabase

Upvotes: 0

seeker_of_bacon
seeker_of_bacon

Reputation: 2429

In case anyone on OS X has this problem, mysql installed with homebrew has default output located at /usr/local/var/mysql/database-name/

Upvotes: 2

Michael Berkowski
Michael Berkowski

Reputation: 270627

MySQL may be writing the file into its own data directory, like /var/lib/mysql/<databasename> for example. To specify the path, use a full path.

However, it must be a directory that is writable by the user account the MySQL server daemon is running under. For that reason, I'll often use /tmp:

Specify the path you want to write to as in:

INTO OUTFILE '/tmp/mydata.csv'

And note that MySQL will write the file on the MySQL server, not on your client machine. Therefore remote connections will create output files on the remote server. See also SELECT INTO OUTFILE local ? for more details and workarounds.

Systemd & Linux

A note about writing to /tmp on a Linux system running systemd:

Some years after originally posting this, I found myself unable to locate a file written to /tmp via

...INTO OUTFILE '/tmp/outfile.csv'

on a MariaDB 5.5 server running Fedora Linux with systemd. Instead of writing the file directly to /tmp/outfile.csv as specified, that directory and file were created beneath a systemd directory in /tmp:

/tmp/systemd-mariadb.service-XXXXXXX/tmp/outfile.csv

While the file outfile.csv itself and the tmp/ subdirectory were both created world-writable, the systemd service directory itself has 700 permissions and is root-owned, requiring sudo access to retrieve the file within it.

Rather than specifying the absolute path in MariaDB as /tmp/outfile.csv and specifying it relatively as outfile.csv, the file was written as expected into MariaDB's data directory for the currently selected database.

Upvotes: 82

StackG
StackG

Reputation: 2858

I found my INTO OUTFILE files, created with the following command

select name from users into outfile "name.csv"

at the following location

C:\ProgramData\MySQL\MySQL Server 5.6\data\name.csv

This is probably the default location, as I've never changed anything.

Upvotes: 2

Nono
Nono

Reputation: 7302

Just for Example:

I have run below query in MySQL Workbench & I found my file in

SELECT * FROM `db`.`table` INTO OUTFILE 'file.csv' FIELDS TERMINATED BY ',';

D:\wamp\bin\mysql\mysql5.6.17\data\db\file.csv

You can use below example to set your file path:

SELECT * FROM `db`.`table` INTO OUTFILE 'C:\\file.csv' FIELDS TERMINATED BY ',';

Upvotes: 5

Naren
Naren

Reputation: 1

You find the file in C:\wamp\bin\mysql\mysql5.5.24\data, but the name will be UsersNamecarro.txt if you gave following select * from carro where id_cor<4 INTO OUTFILE 'C:\Users\Name\carro.txt';

So just write .....OUTFILE 'C:\carro.txt' to get file called carro.txt in data folder

Upvotes: 0

Shlomi Noach
Shlomi Noach

Reputation: 9384

I suspect you are looking for the file on your client machine.

SELECT .. INTO OUTFILE writes the file on the server machine.

Upvotes: 19

Related Questions