11

File file1.txt contains lines like:

/api/purchase/<hash>/index.html

For example:

/api/purchase/12ab09f46/index.html

File file2.csv contains lines like:

<hash>,timestamp,ip_address

For example:

12ab09f46,20150812235200,22.231.113.64 
a77b3ff22,20150812235959,194.66.82.11

I want to filter file2.csv removing all lines where the value of hash is present also in file1.txt. That's to say:

cat file1.txt | extract <hash> | sed '/<hash>/d' file2.csv

or something like this.

It should be straightforward, but I seem unable to make it work.

Can anyone please provide a working pipeline for this task?

13

cut -d / -f 4 file1.txt | paste -sd '|' | xargs -I{} grep -v -E {} file2.csv

Explanation:

cut -d / -f 4 file1.txt will select the hashes from the first file

paste -sd '|' will join all the hashes into a regular expression ex. H1|H2|H3

xargs -I{} grep -v -E {} file2.csv will invoke grep with the previous pattern as an argument, xargs will replace {} with the content of the STDIN

If you don't have paste you could replace it with tr "\\n" "|" | sed 's/|$//'

  • 3
    +1 but no need for cat, just cut -d / -f 4 file1.txt. Or if you prefer the sequential look, <file1.txt cut -d / -f 4 – Sparhawk Aug 18 '15 at 6:22
  • @Sparhawk thanks! I didn't know ;-) solution updated :-) – Gabriele Lana Aug 19 '15 at 7:54
11

Possible awk solution:

awk 'NR == FNR { x[$4] = 1; next; } { if (!($1 in x)) print $0; }' FS="/" file1.txt FS="," file2.txt

First we read file1.txt using FS (field separator) "/" and create array x with keys values from field $4 which is the hash you want. Next we read second file file2.txt setting FS to be , and check if value of field $1 does not exist as key in array x and if it does not we print it.
The same more idiomatic as proposed in comments could be:

awk 'NR == FNR { x[$4] = 1; next; } !($1 in x)' FS="/" file1.txt FS="," file2.txt
  • I appreciate your effort, but I'm afraid this flies way above my head. I keep hoping a solution based on some sed/grep/cat mixture will be possible. – Marco Faustinelli Aug 17 '15 at 12:12
  • 1
    I'll add an explanation, it is simple. And may be someone will propose a solution with tools you want. – taliezin Aug 17 '15 at 12:16
  • Why not just !($1 in x) instead of { if (!($1 in x)) print $0; } – iruvar Aug 17 '15 at 12:26
  • @1_CR it is my bad habit, I know it could be more idiomatic but I always think it will be simpler for explanation to OP. – taliezin Aug 17 '15 at 12:28
  • @Muzietto still, I think there's no harm starting to learn other tools such as this awk-based solution... in the long run, you'll learn to gravitate towards solutions that can be achieved using lesser pipes for simplicity... :) – h.j.k. Aug 18 '15 at 1:55
5

For GNU sed

sed -z 's%.*/\([^/]*\)/index.html\n%\1\\|%g;s%^%/%;s%\\|$%/d%' file1.csv |
sed -f - file2.csv

where first sed produce list of hashes in sed-command-format like /12ab09f46\|a77b3ff22\|..../d and transfer it to next sed-script which reads above command from input therefore -f - option.
Same with grep

grep -oP '[^/]*(?=/index.html$)' file1.csv | grep -Fvf - file2.csv

or without perl-expresions:

grep -o '[^/]*/index.html$' file1.csv | 
grep -o '^[^/]*' | 
grep -Fvf - file2.csv

or even better with cut:

cut -d/ -f4 file1.csv | grep -Fvf - file2.csv
  • This looks to me what I was looking for. Can you please illustrate it a bit? I cannot see how the second command will remove lines from file2.csv. – Marco Faustinelli Aug 17 '15 at 13:36
  • @Muzietto See updated – Costas Aug 17 '15 at 14:51
2
#!/bin/bash
cut -d, -f1 file2 | while read key ; do 
   #check for appearance in file1 with successful grep:
   #exit status is 0 if pattern is found, only search for at least 1
   #appearance -> to speed it up
   if [[ $(grep -m 1 "/$key/" file1) ]] ; then
      sed "/^$key,/d" -i file2
      #note that we are gradually overwriting file2 (-i option),
      #so make a backup!
   fi
done

Note that the search stings are /$key/ and ^$key, to reduce results to be either between two slashes (file 1) or to be the first entry of a line and followed by a comma (file 2). This should make it safe if keys look like

a,values
a1,values

in file 2, or like

/api/../a1/../
/api/../a/../

in file 1

2

I've just tried the following one liner, and it seems to do the job:

 for i in `cat file1.txt  | awk -F"/" '{print $4}'`; do echo "\n $i" ; sed -ri "/^$i,/d" file2.csv ; done

Please replace first -ri with -re to test it. -re does a dry run, and if all is ok you can run it with -ri

  • mmmh, I've redirected the output of your code to a temporary file and it contains about 30k lines, whereas file2.csv has initially 240 and it is supposed to get filtered. – Marco Faustinelli Aug 17 '15 at 12:50
  • Well, I think that is because I print every hash in the first file, when I do the substitution(the echo "\n" $i part). Anyhow if you run it with -ri you don't have to redirect, because it does the substitution in place – primero Aug 17 '15 at 13:07
  • Also If you run with -re and redirect you will have file2 repeated for as many hashes you have in the first file. Basically for each hash in the first file it replaces it in the second file and prints the result, so that is why you have so many lines. – primero Aug 17 '15 at 13:10
1

In addition to Gabriele Lana's answer please note that BSD paste command needs dash to be specified to read content from standard input.

manual of paste command

If '-' is specified for one or more of the input files, the standard input is used; standard input is read one line at a time, circularly, for each instance of '-'.

So final need to be change like below

cut -d / -f 4 file1.txt | paste -sd '|' - | xargs -I{} grep -v -E {} file2.csv

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.