Grab from CSV, serialise selected fields and modify CSV

I have an Excel/CSV file with relatively simple format.

1|2|3|4|10|20|30
 | | | |40|50|60
 | | | |70|80|90
9|8|7|6|01|02|03
 | | | |04|05|06
 | | | |07|08|09
 | | | |10|11|12

The complete rows of 7 fields are single items, while the repeating columns of 5, 6 and 7 represent the data I want to serialise. Each main row can have any number of partial rows associated.

So for the above, item 1, the data to serialise is:

 | | | |10|20|30
 | | | |40|50|60
 | | | |70|80|90

Same for item 2, although the cell content is completely arbitrary, except for the number of items.

Output would be below to get the serialised data, except rather than print to page it needs to go back in to the CSV, as column 8, with the additional rows (2-3, 5-6 in this case) deleted.

开发者_如何学运维

If I can achieve the below, then it would be great:

<?
$line_1 = array(
    array('1' => '10', '20', '30'), 
    array('1' => '40', '50', '60'), 
    array('1' => '70', '80', '90'), 
);
$line_2 = array(
    array('1' => '01', '02', '03'), 
    array('1' => '04', '05', '06'), 
    array('1' => '07', '08', '09'), 
    array('1' => '10', '11', '12'), 
);
echo serialize($line_1);
echo serialize($line_2);
?>  

So finally:

1|2|3|4|10|20|30|serialize($line_1)
9|8|7|6|01|02|03|serialize($line_2)

Then it would just be a case of getting it written to the parent line.

Output of the first line would be as follows and written to col H:

1|2|3|4|10|20|30|a:3:{i:0;a:3:{i:1;s:2:"10";i:2;s:2:"20";i:3;s:2:"30";}i:1;a:3:{i:1;s:2:"40";i:2;s:2:"50";i:3;s:2:"60";}i:2;a:3:{i:1;s:2:"70";i:2;s:2:"80";i:3;s:2:"90";}}

Is it possible to do that in Excel without PHP intervention, and if not how can it be done in PHP?

---

Because the question changed a bit, I'm going to give a new answer. Hope this helps.

<?php

  $array_to_serialize = array();
  $output_array = array();
  $in_lines = array();

  $in_csv = '1,2,3,4,10,20,30' . "\r\n" .
            ',,,,40,50,60' . "\r\n" .
            ',,,,70,80,90' . "\r\n" .
            '9,8,7,0,01,02,03' . "\r\n" .
            ',,,,40,11,60' . "\r\n" .
            ',,,,70,80,90' . "\r\n" .
            '9,8,7,6,01,02,03' . "\r\n" .
            ',,,,04,05,06' . "\r\n" .
            ',,,,07,08,09';


  $in_lines = explode("\n", $in_csv);

  while (list(, $line) = each($in_lines)) {

    $line_parts = explode(",", trim($line));

    if ($line_parts[0] != '') {

      if (!empty($beginning_numbers)) {
        $output_array[] = implode(',', $beginning_numbers) . ',' . serialize($matrix_part);
      }

      $beginning_numbers = array_slice($line_parts, 0, 4);
      $matrix_part = array(array_slice($line_parts, 4));

      $output_line = $line_parts;
      $array_to_serialize = array();
    } else {
      $matrix_part[] = array_slice($line_parts, 4);
    }

  }

  $output_array[] = implode(',', $beginning_numbers) . ',' . serialize($matrix_part);

  $out_text = implode("\r\n", $output_array);

  echo $out_text;

?>

---

In excel, copy this line on h1:

=IF(A1<>"";IF(A2="";IF(A3="";TEXT(E2;"0")&","&TEXT(F2;"0")&","&TEXT(G2;"0")&","&TEXT(E3;"0")&","&TEXT(F3;"0")&","&TEXT(G3;"0");"");""); "")

then copy that line on the lines below it by dragging down from the lower right corner of the cell cursor.

if you want to do it in php, here's code for that (minus file reading):

<?php

  $array_to_serialize = array();
  $output_array = array();
  $in_lines = array();

  $in_csv = '1,2,3,4,10,20,30' . "\r\n" .
            ',,,,40,50,60' . "\r\n" .
            ',,,,70,80,90' . "\r\n" .
            '9,8,7,6,01,02,03' . "\r\n" .
            ',,,,04,05,06' . "\r\n" .
            ',,,,07,08,09';


  $in_lines = explode("\n", $in_csv);

  while (list(, $line) = each($in_lines)) {

    $line_parts = explode(",", trim($line));

    if ($line_parts[0] != '') {
      if (!empty($output_line)) {
        $output_array[] = implode(',', $output_line) . ',' . implode(',', $array_to_serialize);
      }

      $output_line = $line_parts;
      $array_to_serialize = array();
    } else {
      $array_to_serialize[] = $line_parts[4];
      $array_to_serialize[] = $line_parts[5];
      $array_to_serialize[] = $line_parts[6];
    }

  }

  $output_array[] = implode(',', $output_line) . ',' . implode(',', $array_to_serialize);

  $out_csv = implode("\r\n", $output_array);

  echo $out_csv;

?>