Export from PHPMyAdmin in Pipe Delimited or Tab Delimited

Please below example, you need to write in SQL in mysql

SELECT * FROM TABLE
ORDER BY ID INTO OUTFILE 'out.csv'
FIELDS TERMINATED BY '|';

SELECT * FROM TABLE ORDER BY ID INTO OUTFILE 'out.file' FIELDS TERMINATED BY '|';

Simply add INTO OUTFILE [FILE_PATH] in your query and you are done. Here is a simple example:

SELECT * FROM [TABLE]
INTO OUTFILE 'X:/[FILE]'

Above statement uses default options for file export of result set of a query,
 but it is simple to override them. By default file will be TAB DELIMITED file.
 We can always override as showed in example below:

SELECT * FROM  [TABLE] INTO OUTFILE 'X:/[FILE]'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'OR

SELECT * INTO OUTFILE 'X:/[FILE]'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM  [TABLE]The beauty is it don't really matter where you place FROM TABLE clause,
 any of the style would work from above example. Don't forget that we can use all our complex join or
 where conditions which we really use with SELECT

Here is my teaching example of how to export a CSV file from a table.  It creates a row with the column names at the top, and the values in rows below the associated column names.  This function is very helpful, in fact it was invented for exactly the requirement you have here!
http://php.net/manual/en/function.fputcsv.php

You can install this script on your server and run it as-is.  Just put the table name you want on line 10 and add your data base credentials in line 15-17.

Best of luck with the project, ~Ray

<?php // RAY_db_to_excel.php
error_reporting(E_ALL);
echo "<pre>\n";


// DEMONSTRATE HOW TO EXPORT A TABLE SO THAT IT CAN BE USED IN EXCEL


// SET YOUR TABLE NAME HERE - OR MAYBE USE THE URL GET ARGUMENT?
$table_name = '???';


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";

// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}

// OPEN THE CSV FILE - PUT YOUR FAVORITE NAME HERE
$csv = 'EXPORT_' . date('Ymdhis') . "_$table_name" . '.csv';
$fp  = fopen($csv, 'w');

// GET THE COLUMN NAMES
$sql = "SHOW COLUMNS FROM $table_name";
if (!$res = mysql_query($sql))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}
if (mysql_num_rows($res) == 0)
{
    die("WTF? $table_name HAS NO COLUMNS");
}
else
{
    // MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
    while ($show_columns = mysql_fetch_assoc($res))
    {
        $my_columns[] = $show_columns["Field"];
    }
    // var_dump($my_columns); ACTIVATE THIS TO SEE THE COLUMNS
}

// WRITE THE COLUMN NAMES TO THE CSV
if (!fputcsv($fp, $my_columns)) die('DISASTER');

// GET THE ROWS OF DATA
$sql = "SELECT * FROM $table_name";
$res = mysql_query($sql);
if (!$res)
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($errmsg);
}

// ITERATE OVER THE DATA SET
while ($row = mysql_fetch_row($res))
{
    // WRITE THE COMMA-SEPARATED VALUES.  MAN PAGE http://php.net/manual/en/function.fputcsv.php
    if (!fputcsv($fp, $row)) die('CATASTROPHE');
}

// ALL DONE
fclose($fp);

// SHOW THE CLIENT A LINK
echo "<p><a href=\"$csv\">$csv</a></p>\n";

Select allOpen in new window

Thanks for the suggestions, everyone! I was able to export a pipe delimited CSV, but when I open it in Excel it is still separating cells by comma and ignoring the pipes. I've been searching all over for a way to change Excel to separate by pipes. Any one have any ideas?

Thanks!

Never mind, I figured it out with help from above.

Pipes?  What's wrong with commas?  I create CSV files separated by commas and use them in Excel.  It works fine.

Thank you!

The purpose of the post was to find a way to export without using commas as the separator.

Thanks for the help, everyone! Got it all figured out. Every response was helpful, this thread got a 10.0 rating! Hopefully it helps someone else out there, as I was unable to find anything very clear on the topic.