Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
543 views
in Technique[技术] by (71.8m points)

php - How can you parse excel CSV data that contains linebreaks in the data?

I'm attempting to parse a set of CSV data using PHP, but having a major issue. One of the fields is a long description field, which itself contains linebreaks within the enclosures.

My primary issue is writing a piece of code that can split the data line by line, but also recognize when linebreaks within the data should not be used. The linebreaks within this field are not properly escaped, making them hard to distinguish from legitimate linebreaks.

I've tried to come up with a regular expression that can properly handle it, but had no luck so far. Any ideas?

CSV format:

"####","text data here", "text data 
 with linebreaks 
 here"

"####","more text data", "more data 
 with 
 linebreaks 
 here"

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

According to aleske, a commenter in the documentation for PHP's fgetcsv function:

The PHP's CSV handling stuff is non-standard and contradicts with RFC4180, thus fgetcsv() cannot properly deal with files [that contain line breaks] ...

And he offered up the following function to get around this limitation:

function csvstring_to_array(&$string, $CSV_SEPARATOR = ';', $CSV_ENCLOSURE = '"', $CSV_LINEBREAK = "
") { 
  $o = array(); 

  $cnt = strlen($string); 
  $esc = false; 
  $escesc = false; 
  $num = 0; 
  $i = 0; 
  while ($i < $cnt) { 
$s = $string[$i]; 

if ($s == $CSV_LINEBREAK) { 
  if ($esc) { 
    $o[$num] .= $s; 
  } else { 
    $i++; 
    break; 
  } 
} elseif ($s == $CSV_SEPARATOR) { 
  if ($esc) { 
    $o[$num] .= $s; 
  } else { 
    $num++; 
    $esc = false; 
    $escesc = false; 
  } 
} elseif ($s == $CSV_ENCLOSURE) { 
  if ($escesc) { 
    $o[$num] .= $CSV_ENCLOSURE; 
    $escesc = false; 
  } 

  if ($esc) { 
    $esc = false; 
    $escesc = true; 
  } else { 
    $esc = true; 
    $escesc = false; 
  } 
} else { 
  if ($escesc) { 
    $o[$num] .= $CSV_ENCLOSURE; 
    $escesc = false; 
  } 

  $o[$num] .= $s; 
} 

$i++; 
  } 

//  $string = substr($string, $i); 

  return $o; 
} 

That looks like it will do the trick.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...