?
Solved

Export from PHPMyAdmin in Pipe Delimited or Tab Delimited

Posted on 2011-10-25
10
Medium Priority
?
1,805 Views
Last Modified: 2012-05-12
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
Comment
Question by:Spiderstave
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 84

Accepted Solution

by:
Dave Baldwin earned 2000 total points
ID: 37027930
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
 
LVL 10

Expert Comment

by:ukerandi
ID: 37029820
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
 
LVL 10

Expert Comment

by:ukerandi
ID: 37029826
SELECT * FROM TABLE ORDER BY ID INTO OUTFILE 'out.file' FIELDS TERMINATED BY '|';
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 10

Expert Comment

by:ukerandi
ID: 37029841
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
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37030866
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
 

Author Comment

by:Spiderstave
ID: 37032816
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
 

Author Comment

by:Spiderstave
ID: 37033111
Never mind, I figured it out with help from above.
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 37033113
Pipes?  What's wrong with commas?  I create CSV files separated by commas and use them in Excel.  It works fine.
0
 

Author Closing Comment

by:Spiderstave
ID: 37033114
Thank you!
0
 

Author Comment

by:Spiderstave
ID: 37033131
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Originally, this post was published on Monitis Blog, you can check it here . In business circles, we sometimes hear that today is the “age of the customer.” And so it is. Thanks to the enormous advances over the past few years in consumer techno…
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
Suggested Courses
Course of the Month15 days, 12 hours left to enroll

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question