csv export formatting problem

[biggieuk]

Hi all,

 

I am using the following code to export a list of user data from my mysql database:

 

<?php require_once('../Connections/conn.php'); 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=users.csv");
header("Pragma: no-cache");
header("Expires: 0");


mysql_select_db($database_conf, $userconf);
$select = "SELECT * FROM users";
$export = mysql_query($select);
$count = mysql_num_fields($export);
for ($i = 0; $i < $count; $i++) {
$header .= mysql_field_name($export, $i)."\t";
}
while($row = mysql_fetch_row($export)) {
$line = '';
foreach($row as $value) {
if ((!isset($value)) OR ($value == "")) {
$value = "\t";
} else {
$value = str_replace('"', '""', $value);
$value = '"' . $value . '"' . "\t";
}
$line .= $value;
}
$data .= trim($line)."\n";
}
$data = str_replace("\r", "", $data);
if ($data == "") {
$data = "\n(0) Records Found!\n";
}
print "$header\n$data";
exit;
?> 

 

This exports to a file okay but it is being displayed in a single line, for instance the column headers are showing as:

 

useridusernameemailorganisationselections

 

 

Is it possible for the user to export from mysql and have everything sorted into columns?

 

P.s. im using Excel 2008 if that makes a difference?

 

thanks very much.

 

[mhodge_txs]

Hey

 

As far as I know CSV files default with a seperator of a "," and not a tab as you have like ."\t" so I think its irgnoring the "\t" hence why its putting your column names in the same cell.

 

I think tutorialized has just what you looking for

http://www.tutorialized.com/view/tutorial/Export-MySQL-to-CSV-Excel/16050

 

In terms of exporting as CSV from mysql. I use MySQL Query browser on Ubuntu and when you get a result set back from executing a SQL statment you can export it as CSV.

 

Hope this helps you.

[MatthewJ]

Good concept, but poorly written tutorial at best.

[jonsjava]

This is how I wrote my sql to csv:

<?php
$dbuser = 'USER';
$dbpass = 'PASS';
$db = 'DB';
$host = 'localhost';

$link = mysql_connect($host, $dbuser, $dbpass) or die ("Connection failed due to: " . mysql_error());
mysql_select_db ($db) or die("Selection failed due to: " . mysql_error());
$filename = "ip_export_".date("Y-n-j_Hi_s").".csv";
$full_loc = "export_csv/".$filename;
$fp1 = fopen("export_csv/$filename", "w+");
$content1 = "IP address, Description, Company/Department \n";
fwrite($fp1, $content1);
fclose($fp1);
$result = mysql_query("SELECT * FROM `TABLE`") or die("Uh Oh:".mysql_error());
while ($row = mysql_fetch_array($result)){
        $long_ip = $row['ipaddr'];
        $ip = long2ip($row['ipaddr']);
        $userinfo = $row['userinf'];
        $description = $row['descrip'];
        $fp = fopen("export_csv/$filename", "a");
        $content = $ip.", ".$description.", ".$userinfo."\n";
        fwrite($fp, $content);
        fclose($fp);
}

header("location:".$full_loc);
exit();
?>

I'll be glad to explain it.

[kenrbnsn]

First the term "CSV" originally stood for "comma separated value", but has come to mean a file or string which consists of values separated by a single character. In the OP's case this character is a tab. In most cases the character is a comma, but I have seen CSV files which use other characters as separators.

 

Now back to the problem. The original code is much too complicated. Try something like this:

<?php
require_once('../Connections/conn.php'); 
header("Content-type: application/octet-stream");
header("Content-Disposition: attachment; filename=users.csv");
header("Pragma: no-cache");
header("Expires: 0");

function x($s) {
    return (str_replace('"','""',$s));
}

$head = false;
$tmp = array();
mysql_select_db($database_conf, $userconf);
$q = "SELECT * FROM users";
$rs = mysql_query($q) or die("Problem with the query: $q<br>" . mysql_error());
while ($rw = mysql_fetch_assoc($rs)) {
    if (!$head) {
           $tmp[] = implode("\t",array_keys($rw));
           $head = true;
    }
    $tmp[] = '"' . implode('"' . "\t" . '"',array_map('x',$rw)) . '"';
}
echo implode("\r\n",$tmp) . "\r\n";
?>

 

Ken

 

[biggieuk]

Thanks for your help,

 

I changed the /t to , and its exporting as i wanted now.

 

[biggieuk]

Hi again,

 

Just want to re-open this thread and ask another question..

 

In my database the final column stores values either like  21,11,40  or a single number.

 

Is it possible to output the values stored with commas into separate columns. e.g...

 

|Selections|        |        |

--------------------------

|    21    |  11  |  40  |

 

 

thanks.

[biggieuk]

Dont think i explained myself properly so here is images of what im trying to achieve:

 

Excel output looks like this:

 

 

I need it to split the values into cells like this:

 

 

thanks!