Creating a v-lookup style script for manipulating 2 text files

[mattiep]

Hi,

I have 2 text files (with aproximately 1000 lines in each), and I need a way of looking at a field in one of the text files based on a field with the same value in another text file.

I hope this has made sense to somebody. I cant really explain easily what I need, but if anyone is familiar with excel its a vlookup style script.

TIA

Mattie P

 

[jpadie]

if it is only a 1000 lines in each I'd recommend trying out pre-loading a couple of arrays (i assume they are csv files)

e.g.

$fh1 = fopen("fullpathtofile1", 'r');  //the 'r' argument specifies that the file will be opened for read only
$fh2= fopen("fullpathtofile2", 'r');

//parse the first file

while ($var1 = fgetcsv($fh1))
{
 //don't need to do anything here
}

//parse the second file

while ($var2 = fgetcsb($fh2)) //assume this is the lookup file
{
 // ditto
}

//transform $var2 into an easy lookup 
foreach ($var2 as $key => $val)
{
 $lookup[$val[0]] = $val[1];
}
unset ($var2) //destroy the variable to free memory

//close the csv files
fclose($fh1);
fclose($fh2);

//assuming this is a standard vlookup with a single input column 
// you can now lookup each item in the first file and find its corresponding value using something like this:
//you could also choose just to read the first file now and perform the lookup on the incoming data

foreach ($var1 as $key=>$val) 
{
  echo "$val[0] has a lookup equivalent of $lookup[$val[0]]<br>";
}

hth
Justin

 

[mattiep]

Hi Justin,

They are TAB seperated files - and I can do nothing to change this.

What I need to do is copy all of the values from the first text file, and the value from the second column from the second text file and display that in a table. But if there is no match, then display only the first set of values, and a blank for the data which should have come from the second table.

An example of the data is:

Text File One:

001 CustomerA AddressLineA1 AddressLineA2
002 CustomerB AddressLineB1 AddressLineB2
003 CustomerC AddressLineC1 AddressLineC2

etc...

Text File Two:

001 SomeDataA1 SomeDataA2
002 SomeDataB1 SomeDataB2
004 SomeDataD1 SomeDataD2

This output will then need to be saved into another TAB file for another application.

BR

Mattie P

 

[jpadie]

Thanks for the update

I still think that the approach i outlined above works fine - albeit a little tweak is needed.

try the code below (replacing the two filenames as necessary).

<?
$fh1 = fopen("c:/users.txt", 'r');  //the 'r' argument specifies that the file will be opened for read only
$fh2= fopen("c:/users2.txt", 'r');

//parse the first file
while ($var1[] = fgetcsv($fh1,1024,"\t")) //assume this is the lookup file
{
 // bad coding but dont need to do anything
}

//now have an array or arrays which need cleaning up
foreach ($var1 as $key=>$val)
{
	$customerdata[$val[0]]=array("customername"=>$val[1],"addressline1"=>$val[2],"addressline2"=>$val[2]);
}
unset($var1);
//parse the second file

while ($var2[] = fgetcsv($fh2,1024,"\t")) //assume this is the lookup file
{
 // ditto
}

//transform $var2 into an easy lookup 
foreach ($var2 as $key => $val)
{
	 $customerdata[$val[0]]["somedata1"] = $val[1];	//this takes the second col and assigns it to the earlier variable with the ssame key as in the first col.
}
unset ($var2); //destroy the variable to free memory

//close the csv files
fclose($fh1);
fclose($fh2);

//assuming this is a standard vlookup with a single input column 
// you can now lookup each item in the first file and find its corresponding value using something like this:
//you could also choose just to read the first file now and perform the lookup on the incoming data
 echo "<table border=\"1\">"; 
 echo "<tr>";
 echo "<td>CustomerID</td>";
 echo "<td>customer name</td>";
 echo "<td>address line 1</td>";
 echo "<td>address line 2</td>";
 echo "<td>some data 1</td>";
 echo"</tr>";

foreach ($customerdata as $key=>$val) 
{
 extract($val);
 echo "<tr>";
 echo "<td>$key</td>";
 echo "<td>$customername</td>";
 echo "<td>$addressline1</td>";
 echo "<td>$addressline2</td>";
 echo "<td>$somedata1</td>";
 echo"</tr>";
}
echo "<table>";
?>