CODEWITHSUNDEEP
bashawk
user8717305
user8717305

Reputation:

Merge files based on first column

I am trying to merge the columns of two CSV file. Both the CSV files are having column 1 equal. I want to do via awk command only

file1.csv

 abc.co.in, AB, ABR,
 def.co.in, DE, DEFF,
 ghi.co.in, AB DEE, GHI,
.....................................

file2.csv

abc.co.in, abc, Linux abc 3.0.101-default 22 16:42:57 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
def.co.in, def, Linux def 3.0.101-default #1 SMP Mon Aug 13 18:53:23 UTC 2018x86_64 x86_64 x86_64 GNU/Linux
ghi.co.in, ghi1, Linux ghi1 3.0.default #1 SMP Fri Feb 22 16:42:57 UTC 2019x86_64 x86_64 x86_64 GNU/Linux


Expected (final csv file)

abc.co.in, AB, ABR,  abc, Linux abc 3.0.101-default 22 16:42:57 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
def.co.in, DE, DEFF,  def, Linux def 3.0.101-default #1 SMP Mon Aug 13 18:53:23 UTC 2018x86_64 x86_64 x86_64 GNU/Linux
ghi.co.in, AB DEE, GHI,  ghi1, Linux ghi1 3.0.default #1 SMP Fri Feb 22 16:42:57 UTC 2019x86_64 x86_64 x86_64 GNU/Linux
.....................................

I have tried below but didn't help me fully

awk 'FNR==NR{a[$1]=$2 FS $3;next}{ print $0, a[$1]}' file2.csv file1.csv

Upvotes: 1

Views: 197

Answers (1)

RavinderSingh13
RavinderSingh13

Reputation: 133518

Could you please try following.

awk '
BEGIN{
  FS=OFS=","
}
FNR==NR{
  sub(/^ /,"")
  val=$1
  $1=""
  sub(/,/,"")
  sub(/,$/,"")
  a[val]=$0
  next
}
$1 in a{
  $1=$1 OFS a[$1]
  print $0
}'  file1  file2

Explanation: Adding explanation for above command here.

awk '                      ##Starting awk program here.
BEGIN{                     ##Starting BEGIN section of code here.
  FS=OFS=","               ##Setting FS and OFS as comma for all lines of Input_file here.
}                          ##Closing BEGIN BLOCK of this code here.
FNR==NR{                   ##Checking condition FNR==NR which will be TRUE when Input_file1 is being read.
  sub(/^ /,"")             ##Substituting initial space of line with NULL for Input_file1.
  val=$1                   ##Creating variable val to $1 of current line.
  $1=""                    ##Nullifying $1 to NULL now.
  sub(/,/,"")              ##Substituting comma very first comma with NULL in current line.
  sub(/,$/,"")             ##Substituting last comma with NULL here.
  a[val]=$0                ##Creating n array with name a whose index is val and value if current line.
  next                     ##next will skip all further statements from here.
}
$1 in a{                   ##Checking condition if $1 of current line is present in array a then do following.
  $1=$1 OFS a[$1]          ##Setting $1 value to $1 OFS a[$1] value here as per OP need.
  print $0                 ##Printing edited current line now.
}'  file1  file2           ##Mentioning Input_file names here.

Upvotes: 2

Related Questions