How to make MySQL spit out CSV’s

mysqlDon’t you hate it when you know you solved a problem three years ago but you can’t remember what the solution was? I ran into that recently. I was trying to make mysql generate a csv directly from query results. I remembered that you can do it, and I had an example of the correct syntax to use in my old code, and I had a query that did it just fine when running in the mysql CLI as root. I had to piece together the answer from several different sources; and none of them told the whole story. So here is the full story, along with all the stumbling blocks that I know of.

Here is the general format for the query that you can find in the mysql documentation. You write your query as usual and you insert the “INTO OUTFILE….” immediately after the list of columns you wish to select.

SELECT ....      INTO OUTFILE  '/tmp/out.csv'
 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
  LINES TERMINATED BY '\n'  FROM .....  WHERE ..... etc.  

Potential Stumbling Block #1: Wherever on the file system you decide to write the file to, the mysql user (usually called mysql) must have write permissions to that place, because the file will be owned by mysql. In my example I am writing to /tmp, which is usually writable by anyone.

Potential Stumbling Block #2: If you are going to embed one of these queries into PHP code, a few extra escapes are necessary to get what you want. In PHP I ended up doing the following: (Note: I omitted all the particular details of my query.)

$q = "SELECT .....
INTO OUTFILE '/tmp/out.csv' FIELDS TERMINATED BY ',' 
OPTIONALLY ENCLOSED BY " . "'\\\"'" . " 
LINES TERMINATED BY '\\n' FROM  ..... WHERE .....";

Potential Stumbling Block #3 Do not get the mysql system user confused with the mysql user who connects to your particular database. My db server runs several applications, each with its own database and mysql user, to keep the different users from tromping all over eachother’s databases. It is customary to GRANT ALL privileges on the database to a user whom the application uses in connecting to it. But ALL does not include the rights to create files on the file system. That is set as a GLOBAL attribute, which makes sense when you think about it: either a mysql user can create files on the filesystem or he can’t. Here is a discussion about it. You would issue a command like this:

 
 GRANT FILE on *.* to 'dorkage'@'localhost';

and to look at who has this permission, do

use mysql;
select Host, User, File_priv from user;

Stumbling Block #4: I include code to explicitly delete the file if it previously existed. If you don’t do this, you might find yourself using last week’s copy and never getting an updated one.

Stumbling Block #5Be very very careful who you give FILE permissions to on your server. Most of the places I surfed that told you how to do it didn’t even mention the security issues involved with this at all. If I were hosting for other people I wouldn’t let them have FILE permission. If they wanted to create CSV’s I’d provide a special place where certain vetted queries could run as me. Huge security concern. Keep your eyes open on this one or hackers may be able to DoS your server up with a bunch of useless files.

22 comments to How to make MySQL spit out CSV’s

  • Oh yeah, I’ve only got it to work by having the csv file in the same directory as the script. The “file” input wasn’t working for me, probably just because I was using it wrong.

  • admin

    Well if your mysql user hasn’t got FILE rights you can’t create *any* file on the filesystem no matter what filesystem privileges you give to the mysql *system* user and that is what is a bit confusing.

  • Nice. And you can import the CSV file like this:

    load data local infile ‘out.csv’ into table user
    fields terminated by ‘,’
    enclosed by ” . “‘\\\”‘” . ”
    lines terminated by ‘\n’

  • admin

    And I bet you dont need FILE rights to import a CSV, in fact I’m pretty sure I have done it.

  • Wow I didn’t know sql can create a .csv file from a query. This is definitely guna come in handy in the future, saves me time manually creating the csv file using results in the query browser.

  • Chandralekha

    i’m working on website using ASP.NET 2.0 (C# ) & mysql combination
    my mysql command LOAD DATA LOCAL INFILE ‘C:/Documents and Settings/User/Desktop/MSN.csv’ IGNORE INTO TABLE m1 FIELDS TERMINATED BY ‘|’ LINES STARTING BY ‘’ TERMINATED BY ‘\r\n’ IGNORE 1 LINES; “;
    works fine on my system,
    when i upload this page to the server
    file path will become ‘C:\Documents and Settings\User\Desktop\MSN.csv’ & file not found error will come, if anybody know’s the answer please help me

    how to give file privilages to mysql

    Regards
    Chandralekha

  • admin

    Chandralekha,
    are you sure the file is in the exact same place on the server? Are you running the query direct or through an app? Is there any escapes you need to do? How to give file privileges to a mysql user is in the post. It doesnt seem like you need FILE privilege for this because you are loading an existing file, not creating one. Why is one with backslashes and one with forward slashes.

  • Chandralekha

    Thank u for ur replay
    i’m running the query through web application
    my reqirement is.. i’ve to upload data’s from the csv file to mysql table,
    these csv files will be located in client page
    i’m using FileUpload UI to get the file path from client
    Suppose my file is in the Desktop my file path will be reprasented by window’s like this c:\Documents and Settings\Chandralekha\Desktop\MSN.csv
    but Load Data command never work for backslashes
    i’ve to replace backslashes with forward slashes
    after replacing file path ‘\’ to ‘/’ i used to call Load Command, it works on my system but after deploying the pages to the server file path ‘\’ will be never converted into ‘/’ & file not found err will come,
    after so many trial & error, i just hardcoded the path as given above & run the page, it works fine on my s/m but after deployment filepath with ‘\’ never replace to ‘/’ & same old err will come.
    file is exixsting in the desktop only, file path is correct i’ve deployed this page on my s/m itself & my page is in desktop
    tell me where i’m going wrong?
    as for my knowledge Load Command will work for file path having “/” ,”//”,”////” & not for “\”

  • admin

    What is s/m? I assume it is your development system or localhost.
    Is both systems Windows?
    Are you both uploading file from remote client AND importing to mysql? It sounds like you confirm that the file exists in the server where you expect it exists.
    I am not great with Windows, I really hate it. as far as I know you have to use unix style file paths with mysql commands but I am not 100% sure of it.

    It looks to me like the problem is however you are massaging the pathname BEFORE you get to mySQL is not working. If you give mySQL a path with \ I think it will not find the file. Try a little test on the server if u can where you hard code the path name with / and no c: and I guess from this link http://www.modwest.com/help/kb.phtml?qid=253&cat=6 you DO have to have FILE even to import.

  • Chandralekha

    Admin Sir, Thank for ur replay,
    i’m struggling with this from past 1 week,i’m new to mySql.
    actully my requirement is, the csv file data which i want to convert into table will be exist in client P.C & not in server.
    server & client P.C will be networked through a Webserver,
    tell me how to make mysql read file path from the server,
    shall i’ve to concatenate TCP of client with client file path.
    for example file MSN.csv is on my desktop my path in windows will be like this
    \\\192.0.28\\c$\c:\Documents and settings\Chandralekha\Desktop\MSN.csv

    tell me what shall i do to get file path from the client?

  • Chandralekha

    My csv file will de in client Desktop sir.

  • admin

    I don’t know how to make mysql load data directly from network client via a webserver and it would be a huge security issue just allowing arbitrary data file from remote stations. You can do it direct if your client is allowed to connect remotely to the mysql server but that is very poor practice. I would upload the data to a temp file first. Then you can either use the LOAD FROM infile in mysql, or use your application language to read the file line by line. You also might consider the csv storage engine if you work with spreadsheets a lot.

  • Chandralekha

    what u r telling is correct Sir..
    i’ll follow your comment.
    now i’m thinking of getting the file from the client & storing in tempfile of server, then executing mySql Load Data command for that tempfile.
    i’m not aware of csv Storage engine sir.

    Regards
    Chandralekha

  • admin

    BTW I’m not “sir” I’m a woman. I know, I know…. (blush) most people assume a woman can’t be as blazingly smart as I am, that’s why I don’t advertise my gender. Recently I made a blog post talks about the csv engine. I wasn’t aware of it either until a few months ago. Read the post, it might save you writing some code. 🙂

  • Chandralekha

    Nice Knowing u Madam(My Life Saver..)
    i’m also a woman.
    Thank u for your help..
    i’m not getting that csv engine url mam, if possible send me the address

    May All Your Dreams Come TRUE
    Chandralekha

  • admin

    CSV engine may or may not be option for you. Depends on ur management structure, how much control you have, (your boss, your policy, your version and OS)

    http://dev.mysql.com/tech-resources/articles/csv-storage-engine.html

    in case Google is blocked where you are

  • Jaggi

    Hi,

    Can i append the csv file created using the above query. If u can help me in this will be grateful to u.

    Thanx
    Jaggi

  • admin

    as far as I know you can’t append it to anything automatically, as it is created by mysql on the server and as far as I know the query syntax has no options for append. But you could run the command in program and in the program you could append it. It would be like:
    your program code to generate whatever you want
    your program code to call the mysql query
    your program code to append the csv file to whatever else you want

  • Chandralekha

    Hello Madam,

    I have a csv file which contains Doctor’s File details.
    one of the field name is DoctorName,values in the DoctorName is stored in this format FirstName,LastName of the doctor.
    Now i want to load this csv files to mysql table,
    as you know my DoctorName field contains comma(,)
    when i load the above csv file doctor’s FirstName is stored in one field & LastName is stored in Second field.
    i want to store DoctorName field as FirstName,LastName in mysql table madam
    Do you know any method to store DoctorName as it is in the csv page, while executing Load Data Infile statement.

    Regards
    Chandralekha

    Regards
    Chandralekha

  • Chandralekha

    Got the answer Madam used following statement in LOAD DATA INFILE statement
    (@Doctor) set Doctor=replace(@Doctor, ‘,’, ‘,’);

  • Nice blog here! Also your website loads up fast! What host are you using? I wish my website loaded up as fast as yours lol

  • Can you send me tips getting ripped and bigger biceps? I’m almost 15 years old, i use 10kg dumbbells to train with. Would be great if you could

A sample text widget

Etiam pulvinar consectetur dolor sed malesuada. Ut convallis euismod dolor nec pretium. Nunc ut tristique massa.

Nam sodales mi vitae dolor ullamcorper et vulputate enim accumsan. Morbi orci magna, tincidunt vitae molestie nec, molestie at mi. Nulla nulla lorem, suscipit in posuere in, interdum non magna.