CSV Challenge

kazer

Update:
If you're interested in giving this program a run I finished it, and it is here:
http://www.mustangmapping.com/sort.php
If you have a file that matches the layout below and for some strange odd reason you need to sort it the same way I did, give it a shot! Let me know what you think 🙂.

-K

ORGINAL POST:
I am looking for a quick way to sort & consolodate some information from a CSV file with PHP. Here is an example of what I need to happen.

Input:
1st St,Pg 3
1st St,Pg 15
Abbot St,Pg 4
Arlington Rd,Pg 8
Jackson Rd,Pg 1
Jackson Rd,Pg 2
Jackson Rd,Pg 5

Output:
1st St,Pg 3.15
Abbot St,Pg 4
Arlington Rd,Pg 8
Jackson Rd,Pg 1.2.5

Basically I need to take every roadname and have it consolodate all of the pages it appears on to one line. (the "Pg's" wont be there however, I just put them in there as reference as to what the numbers were all about, they can easily be added at the end when the information is outputted)
I've been able to shortcut it quite a bit using some basic Excel functions... but that doesn't really help me out as far as dealing with dynamic information on the web. Does anyone have any ideas on how to go about this sort of thing? I do have the option of using a MySQL database if needed, though doing it without would be preferable.

-K

kazer

This is how far i've gotten:

<table border=1>
<?
$fp = fopen ("test.csv","r");
while ($data = fgetcsv ($fp, 1000, ",")) {
$num = count($data); //If there are more than 2 vars per row, fail
if($num > 2) echo "Check your csv, there is an error.<br>";
else
{
if($lastname == $data[0]) { //if the previous roadname matches, save the page number to a var and print with new.
if(!$new) $new = $lastnumber;
if($new) $new = $new . "," . $data[1];
print "<tr><td width=200>" . $data[0] . "</td><td width=20>" . $new ."</td></tr>";
}

if($lastname != $data[0]) { //if the previous roadname does not match, clear out the saved vars
print "<tr><td width=200>" . $data[0] . $dup . "</td><td width=20>" . $data[1] ."</td></tr>";
$lastname = $data[0];
$lastnumber = $data[1];
$new = "";
}
}
}
fclose ($fp);
?>
</table>

(test.csv)
Jackson Rd,1
Jackson Rd,2
Jackson Rd,3
Jackson Rd,4
Jackson Rd,5
Maple Ln,2
March Dr,1
March Dr,2

-K

How would I go about sorting the information from here?
I'll be trying to figure this out on my own as I wait for a response. If what i've already done can somehow be done more efficiently, help would be greatly appreciated.

kazer

New Code:

<table border=1>
<?
$fp = fopen ("test.csv","r");
while ($data = fgetcsv ($fp, 1000, ",")) {
$num = count($data);
if($num > 2) echo "Check your csv, there is an error.<br>";
else
{
if($lastname == $data[0]) { //If the previous roadname matched store page # in a variable
if(!$new) { $new = $lastnumber; $lastname = $data[0]; }
if($new) $new = $new . "," . $data[1];
}
if($lastname != $data[0]) { //If the previous roadname did not match, print saved vars & non dup road
if($new) print "<tr><td width=200>" . $lastname . "</td><td width=20>" . $new ."</td></tr>";
print "<tr><td width=200>" . $data[0] . "</td><td width=20>" . $data[1] ."</td></tr>";
$lastname = $data[0];      //Clear saved vars
$lastnumber = $data[1];
$new = null;
}
}
}
fclose ($fp);
?>
</table>

The only problem with this is that unless there is a non duplicate at the end, it doesn't print the final value. I'm using the same csv file as above.

kazer

<table border=1>
<?
$fp = fopen ("test.csv","r");
while ($data = fgetcsv($fp, 1000, ",")) { $x++;
$name[$x]=$data[0];                                   //Parse csv into an array
$page[$x]=$data[1];
}
fclose ($fp);

for($y=0; $y<sizeof($name); $y++)                     //Run through the array and print
{
if($lastname == $name[$y])                            //Previous roadname match: Store page # in var
{
if(!$new) { $new = $lastnumber; $lastname = $name[$y]; }
if($new) $new = $new . "," . $page[$y];
}

if($lastname != $name[$y])                            //Previous roadname mismatch: Print saved vars & non dup road
{ 
if($new) print "<tr><td width=200>" . $lastname . "</td><td width=20>" . $new ."</td></tr>";
print "<tr><td width=200>" . $name[$y] . "</td><td width=20>" . $page[$y] ."</td></tr>";
$lastname = $name[$y];      //Clear saved vars
$lastnumber = $page[$y];
$new = null;
}
}
?>
</table>

Parse the entire csv into an array before reading through for duplicates. This way I can query above and below the last entry. Hopefully this will aid in wrapping up that final roadname bug.

Still hoping for some help, i'll keep updating as I progress.

-K

kazer

<table border=1>
<?
$fp = fopen ("test.csv","r");
while ($data = fgetcsv($fp, 1000, ",")) { $x++;
$name[$x]=$data[0];                               //Parse csv into an array
$page[$x]=$data[1];
}
fclose ($fp);

for($y=0; $y-1<sizeof($name); $y++)               //Run through the array and print
{
if($name[$y-1] == $name[$y])                      //Previous roadname match: Store page # in var
{
if(!$all) { $all = $page[$y-1]; }
if($all) $all = $all . "," . $page[$y];
}

if($name[$y-1] != $name[$y])                      //Previous roadname mismatch: Print saved vars & non dup road
{
if($all) print "<tr><td width=200>" . $name[$y-1] . "</td><td width=20>" . $all ."</td></tr>";
print "<tr><td width=200>" . $name[$y] . "</td><td width=20>" . $page[$y] ."</td></tr>";
$all = null;                                      //Clear all var for new set
}

if($all && $y+1 > sizeof($name))                  //Catch the last var if it is a duplicate
{
print "<tr><td width=200>" . $name[$y-1] . "</td><td width=20>" . $all ."</td></tr>";
}
}
?>
</table>

Removed the $lastname and $lastnumber vars, they are accessible through the array now. Added a check to catch the last entry and print it.

Still hoping for some help on the rest. 😕

-K

kazer

Well, it works! Thanks for all the help guys! 😉

<table border=1>
<?
$fp = fopen ("test.csv","r");
while ($data = fgetcsv($fp, 1000, ",")) { $x++;
$name[$x]=$data[0];                               //Parse csv into an array
$page[$x]=$data[1];
}
fclose ($fp);

for($y=0; $y-1<sizeof($name); $y++)               //Run through the array and print
{
if($name[$y-1] == $name[$y])                      //Previous roadname match: Store page # in var
{
if(!$all) { $all = $page[$y-1]; }
if($all) $all = $all . "," . $page[$y];
}

if($name[$y-1] != $name[$y])                      //Previous roadname mismatch: Print saved vars & non dup road
{
if($all) print "<tr><td width=200>" . $name[$y-1] . "</td><td width=20>" . $all ."</td></tr>";
if($name[$y] != $name[$y+1])
print "<tr><td width=200>" . $name[$y] . "</td><td width=20>" . $page[$y] ."</td></tr>";
$all = null;                                      //Clear all var for new set
}

if($all && $y+1 > sizeof($name))                  //Catch the last var if it is a duplicate
{
print "<tr><td width=200>" . $name[$y-1] . "</td><td width=20>" . $all ."</td></tr>";
}
}
?>
</table>

-K