LinuxQuestions.org - [SOLVED] Replace content of one column to another if match found

- Linux - Newbie (https://www.linuxquestions.org/questions/linux-newbie-8/)

- - Replace content of one column to another if match found (https://www.linuxquestions.org/questions/linux-newbie-8/replace-content-of-one-column-to-another-if-match-found-851784/)

Replace content of one column to another if match found

Hi All,

I have two files one having some transaction IDs only and sample content as follows:

Code:

SDP-DM-100755485

SDP-DM-100755504

SDP-DM-100755508

SDP-DM-100755716

And in second file complete details of all transaction IDS and sample content as follows and delimeter used is comma(,):

Code:

SDP-DM-100689014,2010-12-21 19:24:29 GMT+05:30,CRBT,18,UK,Default,B_11170244,9058629605,405818120653083,,Pre-Paid,Comviva,default_provider,009114500001366,,0.0,code0,0,,Caller Tune,,UK#11561723,,1292939663242,VAS0003ALL,52211,,,UK,,0.0,Grace,

SDP-DM-100689202,2010-12-21 19:24:34 GMT+05:30,Subscription,4,BJ,,B_12326406,9122828964,405876120814560,,Pre-Paid,,default_provider,4945,,40.0,,0,,,,BJ#12106840,,-2206657c%3A12d08f49330%3A4812,VAS0003ALL,,OBD,RECURRING,BJ,,,,

SDP-DM-100689203,2010-12-21 19:24:34 GMT+05:30,Subscription,10,KE,Default,B_32617426,9061093191,405821121458274,,Pre-Paid,,default_provider,6027,,30.0,,0,,,,KE#26425193,,-3d916f23%3A12d08f2214e%3A505d,JokesMal197,,OBD,RECURRING,KE,,,,

And i want is if transaction ID from first file matches with transaction ID located in first field of second file then column number 18 is replaced with column number 16.

Please guide me for the above.
Thanks in advance

Hi,

Try the Perl script between the ======================================= lines, supposing you called it script.pl and made it executable.

Launch the line below from shell prompt

script.pl first_file second_file > wanted_file
=======================================

#!/usr/bin/perl

use strict;

my ($IDs, $fields, @IDs, @fields);

$IDs = shift;
open (IDs, $IDs);
@IDs = <IDs>;
chomp @IDs;
$IDs = join(',', '', @IDs, '');

$fields = shift;
open (FIELDS, $fields);
while (<FIELDS>) {
chomp;
@fields = split /,/;
if ($IDs =~ /,$fields[0],/) {
$fields[17] = $fields[15];
}
print join(',', @fields), "\n";
}

=======================================

Regards,

Tshimanga.

So as you are some 30+ questions in and you have been helped several times, what have you come up with so far?

Let us know where you are getting stuck?

An awk solution:

Code:

BEGIN {

  FS = OFS = ","

  while ( getline < "transaction_ids" > 0 )

      _[$0]++

}



{  

  if ( $1 in _ )

    $18 = $16

  print

}

Hi Colucix,

Please guide me where i can mention the two filenames used in the following code provided by you:

Code:

BEGIN {

  FS = OFS = ","

  while ( getline < "transaction_ids" > 0 )

      _[$0]++

}



{  

  if ( $1 in _ )

    $18 = $16

  print

}

I assumed the file containing the transaction IDs (that is the patterns to look for) is "transaction_ids". If you put the suggested code in a file, e.g. "test.awk" you can run it passing the file with transaction details as argument:

Code:

awk -f test.awk transaction_details_file

In alternative you can add a sha-bang at the very first line of the code:

Code:

#!/bin/awk -f

make it executable and run as

Code:

./test.awk transaction_details_file

Finally, if you want to insert the awk code in a shell script or run it directly on the command line, you can do:

Code:

awk '

BEGIN {

  FS = OFS = ","

  while ( getline < "transaction_ids" > 0 )

      _[$0]++

}



{  

  if ( $1 in _ )

    $18 = $16

  print

}' transaction_details_file

Hope this helps.