Solved

Merge Tab Delimited file records with PHP

Posted on 2014-04-11
27
400 Views
Last Modified: 2014-04-24
Hi,
I need some help with the following:

In the attached "Tab delimited data" file i have records with the same customer number (3rd column) but with different account number (1st column).

I am in need of a PHP script that can merge some of the columns with the same customer number into one record as shown in the attached "needed result" file (tab delimited).
The columns that need to be merge based on the customer number column are "account number", "balance", "past due" and "total due".
The customer number is everything before the "/" in the column.
Tab-delimited-data.txt
needed-result.txt
0
Comment
Question by:eaweb
  • 14
  • 10
  • 2
27 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39994231
How come you have this marked as classic asp?
0
 

Author Comment

by:eaweb
ID: 39994251
Oh sorry. I selected asp by mistake. But, if you have something in asp that can help is also welcome.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39994324
Can we assume that all fields will always be filled in?  And can we assume you you can present the data sorted by the customer number?  Or do we need to do a look up to see if the customer number has been used already?

In other words, last_record="1234", go to next record, if current_record=last_record, then merge?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39994331
I removed the classic asp because your question called for php.   If you prefer in asp, I can do that?
0
 

Author Comment

by:eaweb
ID: 39994375
Hi Scott,

Can we assume that all fields will always be filled in?
>>>Yes<<<
Can we assume you you can present the data sorted by the customer number?
>>>Yes<<<

PHP is ok.

Thanks
0
 

Author Comment

by:eaweb
ID: 39995327
Hi Scott,

Were you able to look into my request?
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39995919
I got pulled away.  I am about to get back on it.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 39996580
Based on the text you gave me, this is tested and seems to work nicely.  Let me know how it goes.
<?PHP
function readCSV($csvFile){	// READ CSV FILE
	$file_handle = fopen($csvFile, 'r');
	while (!feof($file_handle) ) {
		$line_of_text[] = fgetcsv($file_handle, "\t");	// USE TAB DELIMITER
	}
	fclose($file_handle);
	return $line_of_text;
}

function getCustNo($full_acct)	// EXTRACT THE CUSTOMER NUMBER
{
  return stristr($full_acct, '/', true); //http://www.php.net/manual/en/function.stristr.php
}
function printRow($array){	//PRINT A ROW FROM AN ARRAY
	
 	foreach ($array as $key => $value) {
		echo($value)." ";
	}
	echo("<hr>");
}

$csvFile = 'tab.txt';	// SET OUR CSV FILE
$csv = readCSV($csvFile);	// READ CSV FILE

foreach ($csv as $key => $value) {
	
	foreach($value as $item){	// CONVERT $CSV TO ARRAY AND ADD A FIELD FOR THE CUSTOMER  NUMBER
		$fields = explode("\t", $item);
		$csv[$key][0] = $fields[0];
		$csv[$key][1] = $fields[1];
		$csv[$key][2] = $fields[2];
		$csv[$key][3] = $fields[3];
		$csv[$key][4] = $fields[4];
		$csv[$key][5] = $fields[5];
		$csv[$key][6] = $fields[6];
		$csv[$key][7] = $fields[7];
		$csv[$key][8] = $fields[8];
		$csv[$key][9] = $fields[9];
		$csv[$key][10] = $fields[10];
		$csv[$key][11] = $fields[11];
		$csv[$key][12] = $fields[12];
		if ($key === 0){
			$csv[$key][13] = "CUSTOMER NUMBER";
		}
		else
		{
			$csv[$key][13] = getCustNo($fields[2]);  // NEW CUSTOMER NUMBER
		}
	}
}

// FIND MATCH
$matches=array();	// HOLD MATCHES
$temp_cust="0";  	// TEST CUSTOMER NUMBER
foreach ($csv as $key => $value) {
	$cust = $value[13];  // THIS IS THE NEW FIELD FOR THE CUSTOMER NUMBER
	if ($key > 0){
		if (trim($cust)===trim($temp_cust)){	//  PUSH THE MATCHING KEY VALUE TO MATCHES ARRAY
			array_push($matches, $key);
		}
		$temp_cust = $cust;	// TEMPORARY HOLD TO TEST CUSTOMER NUMBER
	}
}

foreach ($matches as $key => $value) {	// UPDATE REPLACEMENT IN THE ARRAY
	$match1 = $value-1;	//IF WE FOUND A MATCH, THIS IS THE FIRST VALUE
	$match2 = $value;	//IF WE FOUND A MATCH, THIS IS THE SECOND VALUE
	
	$csv[$match2][0]= $csv[$match1][0]."~".$csv[$match2][0];
	$csv[$match2][5]= $csv[$match1][5]."~".$csv[$match2][5];
	$csv[$match2][7]= $csv[$match1][7]."~".$csv[$match2][7];
	$csv[$match2][8]= $csv[$match1][8]."~".$csv[$match2][8];

	unset($csv[$match1]);	// PURGE THE ROW/KEY FROM TEH ARRAY THAT WE DID NOT UPDATE IN THE MATCH


}


foreach ($csv as $key => $value) {	// SHOW OUR NEW DATA
	printRow($value);
}



//account_number			0*
//name_line_1				1
//customer_number			2
//cycle						3
//block						4
//balance					5*	
//last_payment				6
//past_due					7*
//total_due					8*
//DD_DB_NBR					9
//home_phone				10
//employer_phone			11
//cel_phone					12

?>

Open in new window

0
 

Author Comment

by:eaweb
ID: 40001854
Hi Scott,

Running the script i got the errors in the attached error.txt file.

Regards,

Evmel
script-errors.txt
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40001921
Please look at line 23.  $csvFile = 'tab.txt';      // SET OUR CSV FILE

What did you set that to?  That needs to point to your text file
0
 

Author Comment

by:eaweb
ID: 40001942
i have it set to the following
$csvFile = 'tab.txt';      // SET OUR CSV FILE
as i rename the the same test file you have tab.txt
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40001948
This is what I used to test.
tab.txt
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40001956
If you copy and paste this code, it works with the file I uploaded.  I wonder if there is something I missed in your file.


<?PHP
function readCSV($csvFile){	// READ CSV FILE
	$file_handle = fopen($csvFile, 'r');
	while (!feof($file_handle) ) {
		$line_of_text[] = fgetcsv($file_handle, "\t");	// USE TAB DELIMITER
	}
	fclose($file_handle);
	return $line_of_text;
}

function getCustNo($full_acct)	// EXTRACT THE CUSTOMER NUMBER
{
  return stristr($full_acct, '/', true); //http://www.php.net/manual/en/function.stristr.php
}
function printRow($array){	//PRINT A ROW FROM AN ARRAY
	
 	foreach ($array as $key => $value) {
		echo($value)." ";
	}
	echo("<hr>");
}

#$csvFile = 'tab.txt';	// SET OUR CSV FILE
$csvFile = 'http://filedb.experts-exchange.com/incoming/2014/04_w16/845184/tab.txt';
$csv = readCSV($csvFile);	// READ CSV FILE

foreach ($csv as $key => $value) {
	
	foreach($value as $item){	// CONVERT $CSV TO ARRAY AND ADD A FIELD FOR THE CUSTOMER  NUMBER
		$fields = explode("\t", $item);
		$csv[$key][0] = $fields[0];
		$csv[$key][1] = $fields[1];
		$csv[$key][2] = $fields[2];
		$csv[$key][3] = $fields[3];
		$csv[$key][4] = $fields[4];
		$csv[$key][5] = $fields[5];
		$csv[$key][6] = $fields[6];
		$csv[$key][7] = $fields[7];
		$csv[$key][8] = $fields[8];
		$csv[$key][9] = $fields[9];
		$csv[$key][10] = $fields[10];
		$csv[$key][11] = $fields[11];
		$csv[$key][12] = $fields[12];
		if ($key === 0){
			$csv[$key][13] = "CUSTOMER NUMBER";
		}
		else
		{
			$csv[$key][13] = getCustNo($fields[2]);  // NEW CUSTOMER NUMBER
		}
	}
}

// FIND MATCH
$matches=array();	// HOLD MATCHES
$temp_cust="0";  	// TEST CUSTOMER NUMBER
foreach ($csv as $key => $value) {
	$cust = $value[13];  // THIS IS THE NEW FIELD FOR THE CUSTOMER NUMBER
	if ($key > 0){
		if (trim($cust)===trim($temp_cust)){	//  PUSH THE MATCHING KEY VALUE TO MATCHES ARRAY
			array_push($matches, $key);
		}
		$temp_cust = $cust;	// TEMPORARY HOLD TO TEST CUSTOMER NUMBER
	}
}

foreach ($matches as $key => $value) {	// UPDATE REPLACEMENT IN THE ARRAY
	$match1 = $value-1;	//IF WE FOUND A MATCH, THIS IS THE FIRST VALUE
	$match2 = $value;	//IF WE FOUND A MATCH, THIS IS THE SECOND VALUE
	
	$csv[$match2][0]= $csv[$match1][0]."~".$csv[$match2][0];
	$csv[$match2][5]= $csv[$match1][5]."~".$csv[$match2][5];
	$csv[$match2][7]= $csv[$match1][7]."~".$csv[$match2][7];
	$csv[$match2][8]= $csv[$match1][8]."~".$csv[$match2][8];

	unset($csv[$match1]);	// PURGE THE ROW/KEY FROM TEH ARRAY THAT WE DID NOT UPDATE IN THE MATCH


}


foreach ($csv as $key => $value) {	// SHOW OUR NEW DATA
	printRow($value);
}



//account_number			0*
//name_line_1				1
//customer_number			2
//cycle						3
//block						4
//balance					5*	
//last_payment				6
//past_due					7*
//total_due					8*
//DD_DB_NBR					9
//home_phone				10
//employer_phone			11
//cel_phone					12

?>

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40001965
Ok, I see an issue.  When I change the url to your original code http://filedb.experts-exchange.com/incoming/2014/04_w15/844672/Tab-delimited-data.txt, I get the same error.  When I simply copy and paste the data to a text editor and save it works.  When I uploaded to EE and used the url http://filedb.experts-exchange.com/incoming/2014/04_w16/845184/tab.txt it worked.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40001987
Ok, the issue is the blank line in your txt file.
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40002054
Updated

<?PHP
function readCSV($csvFile){	// READ CSV FILE
	$file_handle = fopen($csvFile, 'r');
	while (!feof($file_handle) ) {
		while (($result = fgetcsv($file_handle, "\t")) !== false){
				if (array(null) !== $result){
					$line_of_text[] = $result;	// USE TAB DELIMITER
				}
		}
		
	}
	fclose($file_handle);
	return $line_of_text;
}

function getCustNo($full_acct)	// EXTRACT THE CUSTOMER NUMBER
{
  return stristr($full_acct, '/', true); //http://www.php.net/manual/en/function.stristr.php
}
function printRow($array){	//PRINT A ROW FROM AN ARRAY
	
 	foreach ($array as $key => $value) {
		echo($value)." ";
	}
	echo("<hr>");
}

#$csvFile = 'tab2.txt';	// SET OUR CSV FILE
$csvFile = 'http://filedb.experts-exchange.com/incoming/2014/04_w15/844672/Tab-delimited-data.txt';
$csv = readCSV($csvFile);	// READ CSV FILE

foreach ($csv as $key => $value) {
	
	foreach($value as $item){	// CONVERT $CSV TO ARRAY AND ADD A FIELD FOR THE CUSTOMER  NUMBER
		$fields = explode("\t", $item);
		$csv[$key][0] = $fields[0];
		$csv[$key][1] = $fields[1];
		$csv[$key][2] = $fields[2];
		$csv[$key][3] = $fields[3];
		$csv[$key][4] = $fields[4];
		$csv[$key][5] = $fields[5];
		$csv[$key][6] = $fields[6];
		$csv[$key][7] = $fields[7];
		$csv[$key][8] = $fields[8];
		$csv[$key][9] = $fields[9];
		$csv[$key][10] = $fields[10];
		$csv[$key][11] = $fields[11];
		$csv[$key][12] = $fields[12];
		if ($key === 0){
			$csv[$key][13] = "CUSTOMER NUMBER";
		}
		else
		{
			$csv[$key][13] = getCustNo($fields[2]);  // NEW CUSTOMER NUMBER
		}
	}
}

// FIND MATCH
$matches=array();	// HOLD MATCHES
$temp_cust="0";  	// TEST CUSTOMER NUMBER
foreach ($csv as $key => $value) {
	$cust = $value[13];  // THIS IS THE NEW FIELD FOR THE CUSTOMER NUMBER
	if ($key > 0){
		if (trim($cust)===trim($temp_cust)){	//  PUSH THE MATCHING KEY VALUE TO MATCHES ARRAY
			array_push($matches, $key);
		}
		$temp_cust = $cust;	// TEMPORARY HOLD TO TEST CUSTOMER NUMBER
	}
}

foreach ($matches as $key => $value) {	// UPDATE REPLACEMENT IN THE ARRAY
	$match1 = $value-1;	//IF WE FOUND A MATCH, THIS IS THE FIRST VALUE
	$match2 = $value;	//IF WE FOUND A MATCH, THIS IS THE SECOND VALUE
	
	$csv[$match2][0]= $csv[$match1][0]."~".$csv[$match2][0];
	$csv[$match2][5]= $csv[$match1][5]."~".$csv[$match2][5];
	$csv[$match2][7]= $csv[$match1][7]."~".$csv[$match2][7];
	$csv[$match2][8]= $csv[$match1][8]."~".$csv[$match2][8];

	unset($csv[$match1]);	// PURGE THE ROW/KEY FROM TEH ARRAY THAT WE DID NOT UPDATE IN THE MATCH


}


foreach ($csv as $key => $value) {	// SHOW OUR NEW DATA
	printRow($value);
}



//account_number			0*
//name_line_1				1
//customer_number			2
//cycle						3
//block						4
//balance					5*	
//last_payment				6
//past_due					7*
//total_due					8*
//DD_DB_NBR					9
//home_phone				10
//employer_phone			11
//cel_phone					12

?>

Open in new window

0
 

Author Comment

by:eaweb
ID: 40002250
Hi Scott,

I am still getting an error using:

$csvFile = 'tab2.txt';      // SET OUR CSV FILE
//$csvFile = 'http://filedb.experts-exchange.com/incoming/2014/04_w15/844672/Tab-delimited-data.txt';


Warning: Wrong parameter count for stristr() in E:\BCSDEVELOPMENT\BCSDEV\COLLECTION\Collection.php on line 18

etc...
etc...
etc...
account_number name_line_1 customer_number cycle block balance last_payment past_due total_due DD_DB_NBR home_phone employer_phone cel_phone CUSTOMER NUMBER
--------------------------------------------------------------------------------
35637~81485~26112~31139~07570~58251~21337 GERMUS ADAMGERA0041079/411 7 U 4284.08~1709.58~1175.78~90.02~435.28~1646.46~1851.42 3/31/2014 204.76~ 82.63 ~ 312.31 ~90.02~25~ 80.30 ~ 466.23 418.96~168.11~371.1~90.02~50~162.62~558.8 59990 0 59914
--------------------------------------------------------------------------------

The code doesn't create a new file with the changes right ? It's supposed to update the same file right?
0
 
LVL 52

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 100 total points
ID: 40002311
When I tested in code pad it runs http://codepad.viper-7.com/7k1u5r and that is pulling in http://filedb.experts-exchange.com/incoming/2014/04_w15/844672/Tab-delimited-data.txt

At this point, it is not writing anything, just echo to the screen.   Where you see it calling the printRow function.  Instead of or in addition to sending to the screen you can write to a new file.   For now, just test to get it to print to the screen.  
foreach ($csv as $key => $value) {	// SHOW OUR NEW DATA
	printRow($value);
}

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40002318
What version of php are you using?
0
 

Author Comment

by:eaweb
ID: 40002435
Hi Scott,

I got it to work. I was on 5.2.5 and upgraded to 5.4.24

Here the results:

account_number name_line_1 customer_number cycle block balance last_payment past_due total_due DD_DB_NBR home_phone employer_phone cel_phone CUSTOMER NUMBER
35637~81485 LOUR TUS ALBELOUF0060772/411 2 P 4284.08~1709.58 2/26/2014 204.76~ 82.63 418.96~168.11 00011690804 59939 59903 0 ALBELOUF0060772
26112 EUG ATH ABATEUGM0221070/411 8 Q 1175.78 9/25/2013 312.31 371.1 00023270704 59972 0 59986 ABATEUGM0221070
31139 JAC REZ ABDUJAC 0011266/410 6 G 90.02 2/26/2014 90.02 90.02 00121342410 59954 59981 0 ABDUJAC 0011266
07570~58251 AST DER ALEXASTL0130351/411 2 P 435.28~1646.46 2/26/2014 25~ 80.30 50~162.62 00016338702 59974 59958 0 ALEXASTL0130351
21337 GERMUS ADAMGERA0041079/411 7 U 1851.42 3/31/2014 466.23 558.8 59990 0 59914 ADAMGERA0041079

What i noticed is that the customer number re-appears as the last column.
would it be possible to remove that part and have the process produce another tab delimited file with the new data?

Thanks,

Evmel
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 40002456
The last column is JUST the customer number parsed out. You don't have to use it.
0
 

Author Comment

by:eaweb
ID: 40002546
Could you help me in removing the customer number part and have the process to produce another tab delimited file with the new data?
0
 
LVL 108

Expert Comment

by:Ray Paseur
ID: 40002794
Here is my take on it, given the test data.  This could probably be made more efficient, and if it has to run hundreds of times or has hundreds of thousands of input rows, I would put some thought into greater efficiency.  Hopefully the comments will show what I was thinking as I was writing.  I am not sure what this would do if there were more than two input rows with the same customer numbers.  If you have a more robust test data set and you want to post that, we could test with the new test case and perhaps refine the script.
http://www.iconoun.com/demo/temp_eaweb.php

At the end of this script you would use fputcsv() to write the $new array into the output file.

<?php // demo/temp_eaweb.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28410490.html

// READ THE TEST DATA
$tsv = 'http://filedb.experts-exchange.com/incoming/2014/04_w15/844672/Tab-delimited-data.txt';
$fpr = fopen($tsv, 'r');
if (!$fpr) trigger_error("UNABLE TO OPEN $tsv", E_USER_ERROR);

// HEADER ROW
$hed = fgetcsv($fpr, 0, "\t");

// READ THE REST OF THE ROWS
while (!feof($fpr))
{
    $arr = fgetcsv($fpr, 0, "\t");

    // SKIP EMPTY ROWS - ADD KEYS
    if (!empty($arr[0])) $dat[] = array_combine($hed, $arr);
}
// print_r($dat);
fclose($fpr);

// LOCATE AND ISOLATE THE CUSTOMER NUMBERS, PRESERVING NUMERIC KEYS TO PRESERVE ORIGINAL ORDER
foreach ($dat as $key => $arr)
{
    $xxx = explode('/', $arr['customer_number']);
    $dat[$key]['my_cust_no'] = $xxx[0] . '_' . str_pad($key, 5, '0', STR_PAD_LEFT);
}
// print_r($dat);

// SORT THE ARRAY
function my_csort($a, $b)
{
    if ($a["my_cust_no"] == $b["my_cust_no"]) return 0;
    return ($a["my_cust_no"] < $b["my_cust_no"]) ? -1 : 1;
}
usort($dat, 'my_csort');
// print_r($dat);

// REMOVE THE KEYS FROM MY CUSTOMER NUMBER
foreach ($dat as $key => $arr)
{
    $xxx = explode('_', $arr['my_cust_no']);
    $arr['my_cust_no'] = $xxx[0];
    $dat[$key] = $arr;
}
// print_r($dat);

// THE FIELDS TO "MERGE" (CONCATENATE)
$qqq = array
( 'account_number'
, 'balance'
, 'past_due'
, 'total_due'
)
;

$old = FALSE;
foreach ($dat as $key => $arr)
{
    // IF NOT CONSECUTIVE, CREATE A NEW ENTRY IN THE OUTPUT ARRAY
    if ($old != $arr['my_cust_no'])
    {
        $new[$key] = $arr;
        $old = $arr['my_cust_no'];
    }
    // IF THESE ARE CONSECUTIVE
    else
    {
        // APPEND THESE VALUES TO THE END OF THE PRIOR ARRAY ENTRY
        foreach ($qqq as $arg)
        {
            $new[$key-1][$arg] .= '~' . trim($arr[$arg]);
        }
    }
    // REMOVE THE ADDED SORT KEY
    unset($new[$key]['my_cust_no']);
}
// SHOW THE WORK PRODUCT
print_r($new);

Open in new window

HTH, ~Ray
0
 

Author Comment

by:eaweb
ID: 40004086
Hi Ray,

I tested your code with fputcsv() and the file got produced. Thanks.
However when reviewing the file i notice that for a customer number with more than two  accounts, the accounts are not being merged. it is being added as a new array record.

Furthermore is there a way to enable and disable the first header row from being displayed or not? (account_number, name_line_1 etc..)

See attached results file.

--------------------------------- array results

Array
(
    [0] => Array
        (
            [account_number] => 26112
            [name_line_1] => EUG ATH
            [customer_number] => ABATEUGM0221070/411
            [cycle] => 8
            [block] => Q
            [balance] => 1175.78
            [last_payment] => 9/25/2013
            [past_due] =>  312.31
            [total_due] => 371.1
            [DD_DB_NBR] => 00023270704
            [home_phone] => 59972
            [employer_phone] => 0
            [cel_phone] => 59986
        )

    [1] => Array
        (
            [account_number] => 31139
            [name_line_1] => JAC REZ
            [customer_number] => ABDUJAC 0011266/410
            [cycle] => 6
            [block] => G
            [balance] => 90.02
            [last_payment] => 2/26/2014
            [past_due] => 90.02
            [total_due] => 90.02
            [DD_DB_NBR] => 00121342410
            [home_phone] => 59954
            [employer_phone] => 59981
            [cel_phone] => 0
        )

    [2] => Array
        (
            [account_number] => 21337~25337
            [name_line_1] => GERMUS
            [customer_number] => ADAMGERA0041079/411
            [cycle] => 7
            [block] => U
            [balance] => 1851.42~181.42
            [last_payment] => 3/31/2014
            [past_due] =>  466.23 ~46.23
            [total_due] => 558.8~58.8
            [DD_DB_NBR] =>
            [home_phone] => 59990
            [employer_phone] => 0
            [cel_phone] => 59914
        )

    [4] => Array
        (
            [account_number] => 35637~81485
            [name_line_1] => LOUR TUS
            [customer_number] => ALBELOUF0060772/410
            [cycle] => 2
            [block] => P
            [balance] => 4284.08~1709.58
            [last_payment] => 2/26/2014
            [past_due] => 204.76~82.63
            [total_due] => 418.96~168.11
            [DD_DB_NBR] => 00011690804
            [home_phone] => 59939
            [employer_phone] => 0
            [cel_phone] => 0
        )

    [5] => Array
        (
            [account_number] => ~45585
            [balance] => ~17.58
            [past_due] => ~2.63
            [total_due] => ~18.11
        )

    [7] => Array
        (
            [account_number] => 07570~58251
            [name_line_1] => AST DER
            [customer_number] => ALEXASTL0130351/410
            [cycle] => 2
            [block] => P
            [balance] => 435.28~1646.46
            [last_payment] => 2/26/2014
            [past_due] => 25~80.30
            [total_due] => 50~162.62
            [DD_DB_NBR] => 00016338702
            [home_phone] => 59917
            [employer_phone] => 59974
            [cel_phone] => 0
        )

    [8] => Array
        (
            [account_number] => ~45251
            [balance] => ~46.46
            [past_due] => ~8.30
            [total_due] => ~12.62
        )

    [9] => Array
        (
            [account_number] => ~46251
            [balance] => ~6.46
            [past_due] => ~9.90
            [total_due] => ~2.62
        )

)
Tab-delimited-data.txt
Result-Merge-20140416-09-51-01.txt
0
 

Author Comment

by:eaweb
ID: 40004098
I resolved the header part with fputcsv($fh, $hed, "\t");
-------
$fh = fopen($CreateMergeFile, 'a') or die("can't open file");
fputcsv($fh, $hed, "\t");
foreach ($new as $fields) {
    fputcsv($fh, $fields, "\t");
}
fclose($fh);
0
 
LVL 108

Accepted Solution

by:
Ray Paseur earned 400 total points
ID: 40004108
Yes, as I wrote above,
I am not sure what this would do if there were more than two input rows with the same customer numbers.
You might want to try this version.  I have not tested it because I do not have your test data set.

The first header row will be found in the $hed array.  You can feed this to fputcsv() in front of the rest of the data if you want the headers in the output document.

<?php // demo/temp_eaweb.php
error_reporting(E_ALL);
echo '<pre>';

// SEE http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/Q_28410490.html

// READ THE TEST DATA
$tsv = 'http://filedb.experts-exchange.com/incoming/2014/04_w16/845357/Tab-delimited-data.txt';
$fpr = fopen($tsv, 'r');
if (!$fpr) trigger_error("UNABLE TO OPEN $tsv", E_USER_ERROR);

// HEADER ROW
$hed = fgetcsv($fpr, 0, "\t");

// READ THE REST OF THE ROWS
while (!feof($fpr))
{
    $arr = fgetcsv($fpr, 0, "\t");

    // SKIP EMPTY ROWS - ADD KEYS
    if (!empty($arr[0])) $dat[] = array_combine($hed, $arr);
}
// print_r($dat);
fclose($fpr);

// LOCATE AND ISOLATE THE CUSTOMER NUMBERS, PRESERVING NUMERIC KEYS TO PRESERVE ORIGINAL ORDER
foreach ($dat as $key => $arr)
{
    $xxx = explode('/', $arr['customer_number']);
    $dat[$key]['my_cust_no'] = $xxx[0] . '_' . str_pad($key, 5, '0', STR_PAD_LEFT);
}
// print_r($dat);

// SORT THE ARRAY
function my_csort($a, $b)
{
    if ($a["my_cust_no"] == $b["my_cust_no"]) return 0;
    return ($a["my_cust_no"] < $b["my_cust_no"]) ? -1 : 1;
}
usort($dat, 'my_csort');
// print_r($dat);

// REMOVE THE KEYS FROM MY CUSTOMER NUMBER
foreach ($dat as $key => $arr)
{
    $xxx = explode('_', $arr['my_cust_no']);
    $arr['my_cust_no'] = $xxx[0];
    $dat[$key] = $arr;
}
// print_r($dat);

// THE FIELDS TO "MERGE" (CONCATENATE)
$qqq = array
( 'account_number'
, 'balance'
, 'past_due'
, 'total_due'
)
;

$old = FALSE;
$oky = FALSE;
foreach ($dat as $key => $arr)
{
    // IF NOT CONSECUTIVE, CREATE A NEW ENTRY IN THE OUTPUT ARRAY
    if ($old != $arr['my_cust_no'])
    {
        $new[$key] = $arr;
        $oky = $key;
        $old = $arr['my_cust_no'];
    }
    // IF THESE ARE CONSECUTIVE
    else
    {
        // APPEND THESE VALUES TO THE END OF THE PRIOR ARRAY ENTRY
        foreach ($qqq as $arg)
        {
            $new[$oky][$arg] .= '~' . trim($arr[$arg]);
        }
    }
    // REMOVE THE ADDED SORT KEY
    unset($new[$key]['my_cust_no']);
}
// SHOW THE WORK PRODUCT
print_r($new);

Open in new window

0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
These days socially coordinated efforts have turned into a critical requirement for enterprises.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now