• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1911
  • Last Modified:

Export from PHPMyAdmin in Pipe Delimited or Tab Delimited

Hello,

I have a database that includes commas in addresses. It's causing problems when I export in CSV, because when I use the file and a comma is encountered in an address, it starts a new cell.

Does anyone know how to export an table in pipe or tab delimited format? Thanks!
0
Spiderstave
Asked:
Spiderstave
  • 4
  • 3
  • 2
  • +1
1 Solution
 
Dave BaldwinFixer of ProblemsCommented:
Normally all you have to do is use Fields Enclosed by ".  MySQL and phpmyadmin do not seem to do tab-delimited.  In the CSV dialog, you can change to Fields terminated by | to use pipe-delimited.
0
 
ukerandiCommented:
Please below example, you need to write in SQL in mysql

SELECT * FROM TABLE
ORDER BY ID INTO OUTFILE 'out.csv'
FIELDS TERMINATED BY '|';
0
 
ukerandiCommented:
SELECT * FROM TABLE ORDER BY ID INTO OUTFILE 'out.file' FIELDS TERMINATED BY '|';
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
ukerandiCommented:
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
0
 
Ray PaseurCommented:
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";

Open in new window

0
 
SpiderstaveAuthor Commented:
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!
0
 
SpiderstaveAuthor Commented:
Never mind, I figured it out with help from above.
0
 
Ray PaseurCommented:
Pipes?  What's wrong with commas?  I create CSV files separated by commas and use them in Excel.  It works fine.
0
 
SpiderstaveAuthor Commented:
Thank you!
0
 
SpiderstaveAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now