Cross multiply text data in php

Question

I have two excel sheets-

First Sheet Columns:

ColA ColB ColC ColD ColE (containing M rows) ~700 rows  
A     B    C    D    E  
AA   BB   CC    DD   EE

Second Sheet Columns:

Col1 Col2 Col3 Col4 (containing N rows) ~ 100 rows  
1     2    3    4
11    22   33   44 

Final Result Set:

ColA ColB ColC ColD ColE Col 1 Col2 Col3 Col4  
A    B     C    D    E   1      2    3    4  
A    B     C    D    E   11    22    33   44  
AA   BB   CC   DD   EE   1      2     3    4  
AA   BB   CC   DD   EE   11    22    33    44  

I want a resultant spreadsheet which will be a cross multiplication of the above two sheets: M X N rows and copy it back to csv/excel.

I am using PHP to cross join the two sheets. I have converted both the sheets in csv so my first sheet is csv1 and second is csv2.

<?php
$csv1 = array_map('str_getcsv', file('Book1.csv'));
$csv2 = array_map('str_getcsv', file('Book2.csv'));

$ans = array();

foreach ($csv1 as $key1) {
    foreach ($csv2 as $key2) {
        $ans[] = $key1 . ' ' . $key2;
    }
}

$fp = fopen('file.csv','w');

foreach($ans as $fields) {
    fputcsv($fp, $fields);
}
?>

For some reason, this is not working. The server is not responding and the file is not populating.

Answer 1

You will have to write some VBA code. If by quicker you mean easier, with almost no development, then paste-append the two sheets in Access as separate tables and create a query with an open join between the two tables, selecting all fields.

Answer 2

If you are wanting to do this purely in excel, you can do it with the INDIRECT(), ROW() and MOD() operators. You should know that doing it entirely in Excel is not the best way to calculate around 70,000 rows of data.