Exporting to a CSV file

dcjones

Hi all,

I am working on soe code I found in the forum and trying to get it to work. I am exporting data in to a CSV file for use in excel.

The output puts a "," each side of a data field like ,test street,,test town,,test_city, and so on.

$export = mysql_query($select); 
$fields = mysql_num_fields($export); 


for ($i = 0; $i < $fields; $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); 

header("Content-type: application/x-msdownload"); 
header("Content-Disposition: attachment; filename=extraction.xls"); 
header("Pragma: no-cache"); 
header("Expires: 0"); 
print "$header\n$data";  [/php

laserlight

If you are using PHP 5.1 or later, you can use [man]fputcsv/man. Besides that, what is the problem that you encountered with your code?

dcjones

Hi laserlight and thank you for your reply.

When I run the code the output is like this:

"1337874" "Daphne" "Lin" "3F., No. 149-3, Changchuen Rd" "Taipei 104" "TW" "2007-02-07 21:44:14"

What I am trying for is this:

1337874,Daphne,Lin,3F, No. 149-3, Changchuen Rd,Taipei 104,TW,2007-02-07 21:44:14

The other problem I have is the output is all on one line rather than each record from that database being on a line of its own so I can use it in MS excel.

laserlight

I am a little confused here. Shouldn't the output be:

1337874,Daphne,Lin,3F,"No. 149-3","Changchuen Rd","Taipei 104",TW,"2007-02-07 21:44:14"

I had the impression that CSV format encloses fields with whitespace in double quotes.

The other problem I have is the output is all on one line rather than each record from that database being on a line of its own so I can use it in MS excel.

What is the MS excel format?

laserlight

I remember giving advice on the use of fputcsv() some time ago, but it does seem that having a CSV formatting function that works on an array and returns a string may be easier to work with. Using the specifications from How To: The Comma Separated Value (CSV) File Format, I decided to give it a try:

function formatRecordAsCsv($record) {
    foreach ($record as &$field) {
        // Escape double quotes.
        $field = str_replace('"', '""', $field);
        // Delimit field by double quotes if necessary.
        if (preg_match('/[\s,"]/', $field)) {
            $field = '"' . $field . '"';
        }
    }
    return implode(',', $record) . "\n";
}

$export = mysql_query($select);
$num_fields = mysql_num_fields($export);

$header = array();
for ($i = 0; $i < $num_fields; $i++) {
    $header[] = mysql_field_name($export, $i);
}
$header = formatRecordAsCsv($header);

$data = '';
while ($row = mysql_fetch_row($export)) {
    $data .= formatRecordAsCsv($row);
}

header("Content-type: application/x-msdownload");
header("Content-Disposition: attachment; filename=extraction.xls");
header("Pragma: no-cache");
header("Expires: 0");
echo $header . $data;

dcjones

Hi laserlight and thank you again for your help.

I read the page which your link referred to and I now understand more about CSV file format, thank for that.

I also used the code you kindly provided but I am getting an error.
The error is

Parse error: parse error, unexpected '&', expecting T_VARIABLE or '$' in csv.php on line 43..

Line 43 contains:

LINE 42 function formatRecordAsCsv($record) {
LINE 43 foreach ($record as &$field) {
LINE 44 // Escape double quotes.
LINE 45 $field = str_replace('"', '""', $field);
LINE 46 // Delimit field by double quotes if necessary.
LINE 47 if (preg_match('/[\s,"]/', $field)) {
LINE 48 $field = '"' . $field . '"';
LINE 49 }
LINE 50 }
LINE 51 return implode(',', $record) . "\n";
LINE 52 }

I removed the "&" and the script run but when it opened Excel I got the error the the file could not be read.

Any ideas.

laserlight

Ah, you must be using PHP4. The workaround for PHP4 would be:

function formatRecordAsCsv($record) {]
    $fields = array();
    foreach ($record as $field) {
        // Escape double quotes.
        $field = str_replace('"', '""', $field);
        // Delimit field by double quotes if necessary.
        if (preg_match('/[\s,"]/', $field)) {
            $field = '"' . $field . '"';
        }
        $fields[] = $field;
    }
    return implode(',', $fields) . "\n";
}

dcjones

Hi again.

Still looking very odd.

This is the test data I am working with after I have run the script. all of the fields are in the same cell when viewed in Excel.

member_id,title,name_f,name_l,houseno,street,city,state,zip,country,added
1337874,,Daphne,Lin,,"3F., No. 149-3, Changchuen Rd","Taipei 104",,,TW,"2007-02-07 21:44:14"
1337987,Mr,Dereck,Jones,"Rosebank Cottage","Wheelbarrow Lane",Claverdon,,"CV35 8NN",UK,"2007-04-09 19:09:03"
1337888,,Stuart,Davenport,,"High Street","Great Dunmow",Essex,"cm7 5ha",UK,"2007-02-12 19:02:25"
1337889,Mr,Alex,Bomberg,"The Leas","The Leas","Eastington Bridge, Stonehouse",Gloucestershire,"GL10 3SQ",UK,"0000-00-00 00:00:00"
1337910,,michael,norman,,"3 knights way","great dunmow",Essex,cm61ul,UK,"2007-03-01 19:08:41"
1337930,Mr,Agent,Demo,34,"Long Lane",Cambridge,,"CM1 2VC",UK,"2007-03-09 16:29:15"
1337983,mr,charles,havelock,233a,"westbourne drive",southlands,essex,"es3 4ff",UK,"2007-04-08 11:03:42"
1338022,Mr,Thomas,Jones,44,"Oakhill Crescent","Acocks Green",Birmingham,"B27 1YT",UK,"2007-05-15 12:50:58"
1337988,,James,Quinn,1,"PO Box 365",Harmondsworth,SS,"UB7 0GB",UK,"2007-04-11 00:24:19"
1337989,Mr,BMI,agent,"BMI House","Agent lane","Agent Town",WA,"CV35 *nn",UK,"2007-04-11 09:51:08"
1337990,Mr,Virgin,Agent,"Virgin House","Virgin Street","Virgin Town",WA,"CV35 8NN",UK,"2007-04-11 09:53:22"
1337991,Mr,"Air Berlin",Agent,"Air Berlin House",Berlin,Lane,WA,"CV35 8NN",DE,"2007-04-11 10:05:22"
1337992,Mr,Swissport,Agent,"Swissport House","swiss lane","swiss town",WA,"CV35 8NN",CH,"2007-04-11 10:21:26"
1338020,Mr,David,Pearce,59,"Woodlands Park Drive","Great Dunmow",Essex,Cm61wt,UK,"2007-05-07 22:35:36"
1338013,Mr,Jonathan,Steever,12,"Oakhill Crescent",Oxford,OX,OX2,UK,"2007-04-28 16:58:24"
1338014,Mr,Amy,Patton,34,"Cox Lane",Northampton,NR,"NH1 7YT",UK,"2007-04-28 17:02:43"
1338015,Miss,Lynn,Jones,1,"Any Street","Any City",HA,"SP9 7BB",UK,"2007-04-28 17:18:02"
1338018,Mr,Jonathan,Spooner,59,"Woodlands Park Drive","Great Dunmow",,"CM6 1WT",UK,"2007-05-02 22:15:50"
1338019,Mr,Kenneth,Gillies,"286 Ralph road",Shirley,Solihull,"West Midlands","B90 3LF",UK,"2007-05-07 18:10:48"
1338021,Miss,Mary,Smith,22,"Any Street","Any City",hampshire,"SP10 4JZ",UK,"2007-05-08 23:39:44"
1338023,Miss,Margrit,Dale,"The Kingswood Arms","Waterhouse Lane",Kingswood,Surrey,"KT20 6EB",UK,"2007-05-15 15:43:19"
1338028,Mr,Christopher,Jones,"Rosebank Cottage","Wheelbarrow Lane",Claverdon,Warwickshire,"Cv35 8NN",UK,"2007-05-24 11:56:17"

what do you think.

laserlight

It looks okay to me, but then MS Excel might expect some unusual CSV format.

dcjones

Hi,

I noticed that some of the lines have ",," should not this be ","

Example:

1337874,,Daphne,Lin,,"3F., No. 149-3, Changchuen Rd","Taipei 104",,,TW,"2007-02-07 21:44:14"

I don't know what this is

laserlight

I noticed that some of the lines have ",," should not this be ","

That is due to a blank field.

dcjones

Hi again laserlight.

I sorted out the blank field problem but now I have another. When the data gets export to excel all each row of data is placed into one cell in excel.

Should it not be one field to one cell.

Can you help please.