0

I've looked at similar posts on this site, but have not been able to make them work for my problem.

I have an input file (ids.txt) that holds 8 digit ID numbers, 1 per line. Something like this:

11111111
22222222

Then I have a CSV file (users.csv) like this:

username_prefix user_type   expiry_date comments
11111111    SomeTypeHere    31/12/2017  EDT0029448
22222222    SomeTypeHere    31/12/2017  EDT0029448
33333333    SomeTypeHere    31/12/2017  EDT0029448
44444444    SomeTypeHere    31/12/2017  EDT0029448
55555555    SomeTypeHere    31/12/2017  EDT0029448

I want to use the IDs file as input to a sed command (or something) so that any lines in the CSV file that match the username_prefix field to an ID get moved to a new file. So the expected output of this process would be that the original CSV file would look like this:

username_prefix user_type   expiry_date comments
33333333    SomeTypeHere    31/12/2017  EDT0029448
44444444    SomeTypeHere    31/12/2017  EDT0029448
55555555    SomeTypeHere    31/12/2017  EDT0029448

And there will be a new CSV file that contains the removed lines, like:

username_prefix user_type   expiry_date comments
11111111    SomeTypeHere    31/12/2017  EDT0029448
22222222    SomeTypeHere    31/12/2017  EDT0029448

I tried the following command, but it generated a 0 byte output file.

cat ids.txt | sed "/$/d" ./adhocUsers_upload_EDT0029448.csv > removed.csv

BTW, solution doesn't have to use sed. It just seemed to be the appropriate tool from what I googled.

  • IMHO it would be far simpler to use awk e.g. something like awk 'NR==FNR {a[$0]++; next} FNR==1 || ($1 in a) {print > "removed.csv"} FNR==1 || !($1 in a) {print}' idfile input.csv – steeldriver Aug 4 '17 at 2:26
  • Instead of AWK it might be possible to save the output into a variable and then use the variable – ADDB Aug 4 '17 at 2:28
  • Are the files CSV or whitespace-separated? – Scott Aug 4 '17 at 4:29
  • The ID file is just a text file with 1 ID per line. The users file is a CSV. The commas didn't come across when I copied the above sample from Excel. – nedlud Aug 4 '17 at 4:31
  • Two invocations of the join command (one to get the matches and one to get the misses) would be enough, with the following caveats: 1) the header line would have to be stripped before processing and added back afterwards and 2) the files have to be sorted. – NickD Aug 4 '17 at 5:29
1

Here's a ruby solution. It's a "bit" longer, but it also works when the IDs are in an order that is different from the users file. (And it's a bit more maintanable than sed)

#!/usr/bin/env ruby
ids = []
users = {}
header = ""
# Read the contents of the user-file into a Hash
File.open('users.csv','r') do |users_file|
    header = users_file.gets
    users_file.each do |line|
        id, *content = line.chomp.split(',')
        users[id] = content
    end
end
# For each line in the id-file, add an appropriate line to the removed-file.
File.open('ids.txt','r') do |ids_file|
    File.open('removed.csv', 'a') do |removed_file|
        removed_file.puts header
        ids_file.each do |line|
            id = line.chomp
            if users[id]
                removed_file.puts "#{id},#{users[id].join(',')}"
                users.delete id
            end
        end
    end
end
# Finally fill the original file with what's left
File.open('original.csv', 'a') do |original_file|
    original_file.puts header
    users.each_pair do |id, line|
        original_file.puts "#{id},#{line.join(',')}"
    end
end

Your output will be in the files "original.csv" and "removed.csv". If this isn't where you envisioned it to be, just move the files around ;)

  • Thanks. This worked perfectly. I thought I could be cleaver and do this as some one line SED command, but it's all gone pear shaped. I was actually half way through writing a python script instead, but you beat me with your ruby script. Thanks heaps. – nedlud Aug 4 '17 at 5:30
  • 1
    Oh, I also updated your post to include the commas I needed. – nedlud Aug 4 '17 at 5:30
2

Sound it's pretty easy for awk command:

awk -F, 'NR==FNR{IDs[$0]++;next} 
            { print >(($1 in IDs)?"removed.csv":"Updated.csv")}
' IDs.txt Users.csv
  • I've tried this, but it's failing to match anything. The "out.csv" file is exactly the same size as CSV.csv, and no "removed.csv" is being generated. I don't know enough about awk to know how to debug this. – nedlud Aug 4 '17 at 4:13
  • @AFSHIN: The OP says that the .csv file is — wait for it — really a CSV file, and it is just shown wrong in the question. You need to add -F,. – Scott Aug 4 '17 at 4:47
  • Nice one. With that update, this code is now doing exactly what I was looking for. – nedlud Aug 6 '17 at 23:35
0

Try with this script, it will save matched lines in removed.csv and everything else will be send to stdout. Note that the username_prefix user_type expiry_date comments line will not be send to removed.csv file, so you must add it manually.

#!/usr/bin/env bash

id_file=${1}
csv_file=${2}
removed_file=${3}

while read -r user; do

  matched=''

  while read -r id; do
      if <<< "${user}" grep -F -e "${id}" > '/dev/null'; then
        matched='yes'
        break
      else
        matched='no'
      fi
  done < "${id_file}"

  [[ "${matched}" == 'yes' ]] && echo "${user}" >> "${removed_file}"
  [[ "${matched}" == 'no' ]] && echo "${user}"

done < "${csv_file}"

Example:

$ myscript.sh ids.txt users.csv removed.csv
username_prefix user_type   expiry_date comments
33333333    SomeTypeHere    31/12/2017  EDT0029448
44444444    SomeTypeHere    31/12/2017  EDT0029448
55555555    SomeTypeHere    31/12/2017  EDT0029448
$ cat removed.csv
11111111    SomeTypeHere    31/12/2017  EDT0029448
22222222    SomeTypeHere    31/12/2017  EDT0029448

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.