MySQL Database

ID #135

How can I export data in CSV or tab delimited format?

Applies to: Grid System

Since you do not have the MySQL FILE priv, you cannot SELECT INTO OUTFILE.

However, you can still export your data in any format you want and read it back in using your favorite programming language:

  • You could write a script to dump out the data in the form of valid SQL statements. To read data back in, just execute all the SQL statements.

  • Or you could make a script to write a file with one row of table data per line, with table columns on each line separated by a delimiter character like a semicolon. To read the data back in, just execute a LOAD DATA LOCAL INFILE statement in your MySQL Monitor.

  • The best way to get mysql data into a file is with the mysqldump command. However, this is only useful if you intend to import it back into another MySQL server later.

    • Last update: 2010-09-29 17:05
    Author: FAQ Admin
    Revision: 1.2

    Digg it! Share on Facebook Print this record Send FAQ to a friend Show this as PDF file
    Please rate this FAQ:

    Average rating: 3.18 (17 Votes)

    completely useless 1 2 3 4 5 most valuable

    You can comment this FAQ

    Comment of Anonymous:
    Examples in PHP:


    Added at: 2002-04-09 15:48

    Comment of Anonymous:
    header("Content-disposition: attachment; filename=" . date("Y-m-d").".xls");
    // attachment disposition will force prompt save to filename.
    Added at: 2002-04-09 17:15

    Comment of Anonymous:
    The previous posts where helpful but would not work for me. I am running Apache in Win XP.

    This was the winning header code for creating a csv file that opens correctly in excel:

    header("Content-type: application/vnd.ms-excel");
    header("Content-disposition: attachment; filename=" . date("Y-m-d").".csv");

    I changed the file extnesion from .xls to .csv
    everything else the same. Thanks for the help.
    Added at: 2003-02-06 10:39

    Comment of Anonymous:
    header( "Content-disposition: filename=whatever.some-ext");

    also works to get the breowser to think it is rendering or the named file instead of the filename of your php script

    Added at: 2002-05-23 15:59

    Comment of Anonymous:
    From the Unix commandline (Bourne Shell compatible):

    output tab-seperated:
    echo "select * from example;" | mysql -u user -h host -ppass

    Want another delimeter? Add
    | tr '^V^I' ';'
    to the pipe and you will get csv or any other delimeter you want.

    Skipping the first row (Table Header): add
    | tail -n +2

    Want DOS-CRLF? Simply add
    | sed -e 's/$/^V^M/g'

    Write to a file: add
    >filename.ext


    Please Note: to get ^M (CR) or ^I(tab) as a single(!) control-character (in vi or bash), you have to type [Ctrl]+[V] [Ctrl]+[M] (or [I])
    Added at: 2003-05-20 06:59

    Comment of Anonymous:
    If you use OpenOffice or StarOffice, you might have to use the file type "csv" as "xls" might not work.
    Added at: 2004-08-15 05:14

    Comment of Anonymous:
    it is also good practice to send a size argument. If the file is very small and you are on a fast connection it won't matter. But if the file is very large and you do not add a size parameter, the browser will not show a progress bar because it won't know how many bytes of how many bytes total it is downloading. Example:

    $size_in_bytes = strlen($csv_output);
    header("Content-disposition: attachment; filename=" .
    date("Y-m-d").".csv; size=$size_in_bytes");
    Added at: 2004-10-27 09:25

    Comment of Anonymous:
    The only problem with just using the tr command is it doesn't address commas (or the new separator of your choice) that are embedded in fields.

    CSV is simple but slightly more tricky than straight text manipulation will permit.
    Added at: 2005-10-06 11:58

    Comment of Anonymous:
    to build on first post here is an example of a better CSV file. It includes field delimiters:


    Added at: 2006-02-16 10:15

    Comment of Anonymous:
    Thank you Mike !

    Just two little changes in the code you submitted (typos probably...)



    First change at the end of line 7, where a dot was missing and quatation marks where wrong.

    Second, at line 13, where the "; filename=..." part was missing. I also modified the date format and file extension, as opening csv files directly in Excel with .xls file extension would put each entire line in a cell.
    Added at: 2006-02-18 07:05

    Comment of Anonymous:
    This is a much more versatile way of doing this task, all you have to do is supply a table name, and it will create a csv of that table including it's column names.

    $table = 'table_name';
    $csv = NULL;
    /* link identifier from db connection */
    $conn_id = db_connect();
    $r = mysql_query("SHOW COLUMNS FROM ".$table, $conn_id);
    while ($row = mysql_fetch_assoc($r)) {
    $csv .= $row['Field'].',';
    }
    $csv = substr($csv, 0, -1)."\n";
    $r = mysql_query("SELECT * FROM ".$table, $conn_id);
    while ($row = mysql_fetch_assoc($r)) {
    $csv .= join(',', $row)."\n";
    }
    header("Content-type: application/vnd.ms-excel");
    header("Content-disposition: csv; filename=" . date("Y-m-d") . "_".$table.".csv; size=".strlen($csv));
    echo $csv;
    exit;
    Added at: 2006-11-27 12:35

    Comment of Anonymous:
    hi, it is better to have line 12 of above example read as

    $csv .= '"'.join('","', str_replace('"', '""', $row))."\"\n";

    it is safer to enclose fields in double-quotes as some of them may have line breaks or commas. and any double-quotes appearing inside a field must be escaped by preceding it with another double-quote. see: http://www.rfc-editor.org/rfc/rfc4180.txt
    Added at: 2007-03-11 15:30

    Comment of Anonymous:
    Simple bash shell command to extract MySQL data into a tab-delimited file with windows carriage returns. The resulting file can then be imported into an Excel spreadsheet.

    echo "select * from [table];" | mysql -u [username] -p [database] | sed -e 's/^M\\n/\\r/g' > filename.txt

    To get ^M, type [CTRL]+V, [CTRL]+M

    Brad
    Added at: 2007-07-12 11:38

    Comment of Anonymous:
    SELECT INTO OUTFILE '/tmp/result.text'
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'
    FROM WHERE
    Added at: 2007-07-25 02:17

    Comment of Anonymous:
    Or Try this tool at http://www.dbload.com
    Added at: 2007-08-18 11:19

    Comment of Anonymous:
    To extract data from a web site and convert it to delimited columns (and rows), here is good script to do just that - http://www.biterscripting.com/SS_WebPageToCSV.html . It takes a table from a web page and puts it into a CSV. The script is in biterscripting language ( http://www.biterscripting.com ). I find it useful to use in addition to php. Biter scripting language has some excellent automated editing commands - lex, sen, etc.
    Added at: 2009-12-14 10:31