Line breaks, inside of string, inside of pasted excel data-CodePudding

I'm hoping this can be some regex magic here...

I'm pasting in data from excel, and for simple "stuff" its fine. However when there is line breaks inside of a cell, my code falls apart.

My current code is below:

$rows = explode( chr(10), $input);
$i = 0;

foreach ($rows as $value) {
    $sheet[$i] = explode(chr(9), $value); //explode the columns on 'tab'
    $i  ;
}

The issue is that the first explode, is targeting all new lines (and correctly so based upon my large hammer approach!) hence breaking it mid-way through a row into a new array part.

What I'd like to do, is say "only break by new lines, if it's at the end of this pasted in row".

I've had a good look and experimented with several regex's however to no luck. I seem to just be removing all line breaks.

Many thanks to anyone that can assist in advance.

Example

Code to paste in: (Formatting may be lost due to inputting into RTE)

Title 1 Comment 1
Title 2 "Comment 2 
Thats over 2 lines"
Title 3 Comment 3

This currently returns

Array
(
    [0] => Title 1  Comment 1
    [1] => Title 2  "Comment 2 
    [2] => Thats over 2 lines"
    [3] => Title 3  Comment 3
)

What I'm looking for is:

Array
(
    [0] => Title 1  Comment 1
    [1] => Title 2  Comment 2 Thats over 2 lines
    [2] => Title 3  Comment 3
)

I have noticed that when copying from excel/sheets, it wraps multi-line "stuff" in quotation marks. Unsure if this could help a preg-match/replace?

Many thanks,

CodePudding user response：

$input = "Title 1\tComment 1\nTitle 2\t\"Comment 2 \nThats over 2 lines\"\nTitle 3\tComment 3";

$rows = explode( chr(10), $input);

$NrOfLines = count($rows);
$i = 0;
$NrOfFields = 0;
for($l=0; $l<$NrOfLines; $l  ) {
    $tmp = explode(chr(9),$rows[$l]);
    if (count($tmp)>$NrOfFields) $NrOfFields=count($tmp);
}
print "NrOfLines: $NrOfLines, NrOfFields: $NrOfFields \n";

for ($l=0; $l<$NrOfLines; $l  ) {
    $tmp = explode(chr(9), $rows[$l]);
    if (count($tmp)<>$NrOfFields) {
        $addWithPreviousLine = true; // TODO: find out if this belongs to previous or next line.
        if ($addWithPreviousLine) {
            $tmp = explode(chr(9), $rows[$l-1].$rows[$l]);
            $i--;
        }
        else {
            $tmp = explode(chr(9), $rows[$l].$rows[$l 1]);
            $l  ;
        }
    }
    $sheet[$i] = $tmp;
    $i  ;
}
var_dump($sheet);

NOTE: The TODO is left open... NOTE2: This code takes only care for 1 extra line break. (It is not tested with more line breaks).

result with this code:

array(3) {
  [0] =>
  array(2) {
    [0] =>
    string(7) "Title 1"
    [1] =>
    string(9) "Comment 1"
  }
  [1] =>
  array(2) {
    [0] =>
    string(7) "Title 2"
    [1] =>
    string(30) ""Comment 2 Thats over 2 lines""
  }
  [2] =>
  array(2) {
    [0] =>
    string(7) "Title 3"
    [1] =>
    string(9) "Comment 3"
  }
}