Load a CSV file into a MySQL table when some fields are not enclosed with double quotes and contain new lines

I have a CSV file that I want to load into a MySQL table using the following command:

LOAD DATA LOCAL INFILE '/path/to/file.csv'
INTO TABLE items
CHARACTER SET utf8
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(field1, field2, field3, field4, field5);

The problem I face is that the csv file is not properly formatted because some fields are not enclosed by double quotes ("") and开发者_如何学运维 have also new lines. e.g.: (third line)

"field1","field2","field3","field4","field5"
"aaaaa","bbbbb","ccccc","ddddd","eeeeee"
aaaa
aaaa,bbbbbbbb
bbbbb,"ccccc","dddddd","eeeee"

When I import the csv file into MySQL, newlines inside field contents are interpreted as a line termination.

So... how can I sort it out? Regex? Some CSV editor (I tried CSVed with no luck)? Thank you.

Quick and dirty fix attemt:

$csv = str_replace("\r", "", $csv);

$data = array(array());

while (!empty($csv)) {

  // if in quotes
  if (substr($csv, 0, 1) == '"') {
    $found = preg_match('~[^\\\\]"~', $csv, $matches, PREG_OFFSET_CAPTURE, 1);

    if (!$found)
      die("No closing quote found");

    $data[count($data)-1][] = substr($csv, 1, $matches[0][1]);
    $csv = substr($csv, $matches[0][1] + 2);

  // if not in quotes
  } else {
    $pos = strpos($csv, ',');

    if ($pos === FALSE) {
      $data[count($data)-1][] = $csv;
      $csv = "";

    } else {
      $data[count($data)-1][] = substr($csv, 0, $pos);
      $csv = substr($csv, $pos);
    }
  }

  // comma => not the end of the row
  if (substr($csv, 0, 1) == ',') {
    $csv = substr($csv, 1);

  // newline => end of the row
  } else if (substr($csv, 0, 1) == "\n") {
    $csv = ltrim($csv);

    $data[] = array(); // new row

  } else if (!empty($csv)) {
    die("unexpected error in csv");
  }
}

print_r($data);

Applied on your data snippet outputs:

Array
(
    [0] => Array
        (
            [0] => field1
            [1] => field2
            [2] => field3
            [3] => field4
            [4] => field5
        )

    [1] => Array
        (
            [0] => aaaaa
            [1] => bbbbb
            [2] => ccccc
            [3] => ddddd
            [4] => eeeeee
        )

    [2] => Array
        (
            [0] => aaaa
aaaa
            [1] => bbbbbbbb
bbbbb
            [2] => ccccc
            [3] => dddddd
            [4] => eeeee
        )

)