[SOLVED] Help with CSV file and empty fields

[tqla]

Hi. I was hoping someone can help me.

 

I am using this script:

$file = fopen('file.csv', 'w+');
$text = "";
while ($Fields = mysql_fetch_array($result)) {
  $text .= $Fields['FName'] . " ";
  $text .= $Fields['LName'] . ", ";
  $text .= $Fields['CompanyName'] . ", ";
  $text .= $Fields['MAddress'] . " ";
  $text .= $Fields['MAddress2'] . ", ";
  $text .= $Fields['MCity'] . ", ";
  $text .= $Fields['MState'] . " ";
  $text .= $Fields['MZip'] . "\n\r";
}
fwrite($file, $text);
fclose($file);

 

It works fine but the problem is that some of the fields in the database are empty so the resulting Excel file doesn't line up. What I mean is there are last names in the first name column and zip codes in the address column, etc.

 

Is there a way to compensate for empty fields? So that all of the types will line up correctly.

 

Thanks!

[MadTechie]

a blank field should just be just blank

ie

testa, ,testc

 

biggest problem is quotes

maybe this

 

$data = str_replace('"', '""', $Fields['MCity']);
$text .= '"'.$data.'", ';

[tqla]

I changed it to this just to test it. (there's a comma after each filed) but it still doesn't work!

$file = fopen('file.csv', 'w+');
$text = "";
while ($Fields = mysql_fetch_array($result)) {
  $text .= $Fields['FName'] . ", ";
  $text .= $Fields['LName'] . ", ";
  $text .= $Fields['CompanyName'] . ", ";
  $text .= $Fields['MAddress'] . ", ";
  $text .= $Fields['MAddress2'] . ", ";
  $text .= $Fields['MCity'] . ", ";
  $text .= $Fields['MState'] . ", ";
  $text .= $Fields['MZip'] . ",\n\r";
}
fwrite($file, $text);
fclose($file);

 

MadTechie, I'm confused. How would I integrate your solution into my code?

 

[tqla]

bump

[Barand]

try

<?php
$file = fopen('file.csv', 'w');

while ($Fields = mysql_fetch_row($result)) {
  $text = '"' . join ('","', $Fields) . '"' . "\r\n";
  fwrite($file, $text); 
}

fclose($file);
?>

[roopurt18]

Why don't you just do this:

$file = '/path/to/file/file.csv';
$sql = "SELECT your_statement_here "
       . "INTO OUTFILE '{$file}' "
       . "FIELDS TERMINATED BY ',' "
       . "LINES TERMINATED BY '\n'";
if(mysql_query($sql)){
  // Just a test
  echo file_get_contents($file);
}

 

(EDIT) Never tried this approach, but why do it in PHP when MySQL will do it faster!

[tqla]

 

Thanks Barand! That worked. (Now I gotta try and understand what it is you did)

 

Roopurt18 I'm sure your mysql solution works too but I need to learn php so I must stick with it.

 

Thanks all!

[roopurt18]

Part of programming is using the proper tool for the job.  There's a lot of functionality that you will needlessly write into your PHP programs that can be handled more efficiently by the database, especially things like statistics or date / time functions.

 

So learn both!  Muahahahah

[tqla]

I'll try roopurt18, thanks agian!