0

In PHP I'm using the fputcsv function to produce a comma separated file for import in a MySQL-table (using LOAD DATA INFILE) :

fputcsv($fcsv, array( 
    $id,
    $name,
    $optional
));

$optional can be empty so in MySQL this should be NULL

In the MySQL documentation I read that to import a NULL value, it should be defined as \N in the csv-file.

But the fputcsv function puts quotes around it, so MySQL thinks it's the string "\N".

Is there a solution for this, or should I forget about using fputcsv ?

hexacyanide
  • 88,222
  • 31
  • 159
  • 162
Dylan
  • 9,129
  • 20
  • 96
  • 153

1 Answers1

0

Bit of a hack, but you could just str_replace the "\N" with \N in the output:

//open temporary datastream for CSV output    
$fp = fopen("php://temp", "w");

//assuming $data is an array of arrays each of which is a record with \N 
//in place of nulls
foreach($data as $line) {
  fputcsv($fp, $line);
}

//read data back in to a string
rewind($fp);
$csv_data = fgets($fp);

//replace "\N" with \N
$csv_data = str_replace('"\N"', '\N', $csv_data);

//save to file
file_put_contents($csv_file_path, $csv_data);

Might not be the most memory-efficient either so be careful for large datasets, but should work...

james-geldart
  • 709
  • 7
  • 9