0

I have two column delimited files and I need to create two new files of only the records where the transaction identifiers were found in both files. The identifier is in a field spanning from columns 9 to 14, but each record in each file has unique data both before and after that 9-14 range and that variable data needs to be carried over to the output files. Each identifier is guaranteed to occur either once or not at all.

I could roll my own with a python script, but I don't see much in the way of native support for column delimited files in libraries and I feel like any *nix system (I'm on Ubuntu 20.04) should have command line utilities built for previous eras of computing to handle this simply. Of course if that tool is too archaic, then I'll write myself a python script (unless you know a package that handles column delimited files that I didn't see).

Thank you for your help.


Example: One line with the characters ID:525 in columns 9-14 is found in each file, so the lines are written to their respective output files. Both input files had records not found in the other.

input_file1.txt

Record1 ID:525 DATA A
Record2 ID:232 DATA B
Record3 ID:811 DATA C
Record4 ID:400 DATA D

input_file2.txt

Record1 ID:448 DATA E
Record2 ID:525 DATA F

output_file1.txt

Record1 ID:525 DATA A

output_file2.txt

Record2 ID:525 DATA F
0

3 Answers 3

2

GNU awk (aka gawk) is available on Ubuntu 20.04, and can handle fixed-width data via its FIELDWIDTHS variable.

Ex. at its simplest, given

$ cat input_file1.txt
Record1 ID:525 DATA A
Record2 ID:232 DATA B
Record3 ID:811 DATA C
Record4 ID:400 DATA D

then

$ gawk 'BEGIN{FIELDWIDTHS="8 6 *"} {print $2}' input_file1.txt
ID:525
ID:232
ID:811
ID:400

Based on that, you could do something like

$ gawk '
  BEGIN {FIELDWIDTHS = "8 6 *"}
  BEGINFILE {outfile[ARGIND] = gensub("input","output","1",FILENAME)}
  NR==FNR {a[$2] = $0; next}
  ($2 in a){
    print a[$2] > outfile[1]
    print $0    > outfile[2]
  }
' input_file1.txt input_file2.txt

giving

$ head output_file?.txt
==> output_file1.txt <==
Record1 ID:525 DATA A

==> output_file2.txt <==
Record2 ID:525 DATA F

See for example The GNU Awk User's Guide: 4.6 Reading Fixed-Width Data

0

If you set the each line as a string var, than you can check columns 9-14 like this

myLine="Record1 ID:525 DATA A"
echo ${myLine:8:6}

${myLine:8:6} starts on the 9th column (8+1 since index starts at 0) and goes forward for 6 characters.

After that, you just perform checks in each file (probably with grep)


You could even use awk to get the 2nd column (set of characters, separated by white space) and perform checks on other files like that.

Off the top of my head (and not tested this) you could do something like the following:

#!/bin/bash
// This is based on the example you gave, only using 2 input files

files=("input_file1.txt" "input_file2.txt")

out_baseName="output_file"
index=1
adder=1
for file in ${files[@]}; do
    for line in $(cat $file); do
        id_string=`echo $line | awk '{ print $2 }'` // gets the 2nd column "ID:525"

        for subfile in ${files[@]}; do
            if [ "$subfile" == "$file" ]; then
                continue
            fi
            
            if grep "$id_string" $subfile; then
               echo $line >> ${out_baseName}${index}.txt
               grep "$id_string" $subfile >> ${out_baseName}$(($index+$adder)).txt
               
               index=$(($index+1))
               adder=$(($adder-1))
            fi
        done
    done
done


-1

First method1:

As checked sample input we can do it by considering column2

awk 'NR==FNR{a[$2];next}($2 in a){print $0}' inputfile1.txt inputfile2.txt  >output_file2.txt

awk 'NR==FNR{a[$2];next}($2 in a){print $0}' inputfile2.txt inputfile1.txt  >output_file1.txt

Second Method:

awk 'NR==FNR{a[substr($0,9,6)];next}(substr($0,9,6) in a){print $0}' inputfile2.txt inputfile1.txt >output_file1.txt

awk 'NR==FNR{a[substr($0,9,6)];next}(substr($0,9,6) in a){print $0}' inputfile1.txt inputfile2.txt  >output_file2.txt

output

cat output_file1.txt 
Record1 ID:525 DATA A

cat output_file2.txt 
Record2 ID:525 DATA F

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .