1

In bash I have to ask for each row from first csv file, second csv file and the answers put to third csv file ...

first file. e.g. aaa.csv:

aaa
bbb
aaa
aaa
ccc
fff
aaa

second file, e.g. bbb.csv:

aaa;111
bbb;222
ccc;333
ddd;444
eee;555
ggg;666

third file, e.g. ccc.csv:

aaa;111
bbb;222
aaa;111
aaa;111
ccc;333
fff;
aaa;111

Is this possible? At this blank place can be FALSE word or something. When I use the cat bbb.csv | grep -f aaa.csv > ccc.csv then take the answer with part of the common the aaa.csv ... only once there is aaa with the 111.

closed as too broad by Braiam, cuonglm, Anthon, don_crissti, jasonwryan May 6 '15 at 23:40

Please edit the question to limit it to a specific problem with enough detail to identify an adequate answer. Avoid asking multiple distinct questions at once. See the How to Ask page for help clarifying this question. If this question can be reworded to fit the rules in the help center, please edit the question.

  • 1
    Is it possible in bash? Yes, but I think you'll want to use awk instead. I'm not familiar enough with it to answer; however, my understanding is the man page is really really useful to learn awk. – SailorCire May 6 '15 at 13:39
  • 4
    Search the site for join, awk, sed etc solutions. This has been beaten to death. – don_crissti May 6 '15 at 14:29
4

Another possible approach as in comments could be using awk with changed order of files:

awk -F';' 'FNR == NR { m[$1] = $2; next; } { if ($1 in m) { print $1 ";" m[$1]; } else { print $1 ";FALSE"; } }' bbb aaa

First we read file bbb store values in array with keys values of first column, than we read file aaa and check if key exists in array and print accordingly.
Result:

aaa;111
bbb;222
aaa;111
aaa;111
ccc;333
fff;FALSE
aaa;111
  • I need to learn awk ... it's very hard for me :( – dadu May 12 '15 at 6:10
3

You can do something like this:

while read linea
do
grep "$linea" bbb.csv >> ccc.csv || echo "$linea:FALSE" >> ccc.csv
done < aaa.csv

$ cat ccc.csv 
aaa;111 
bbb;222 
aaa;111 
aaa;111 
ccc;333 
fff:FALSE
aaa;111
  • the best way for me :) thx – dadu May 12 '15 at 6:09
1

How about some meta-sed:

sed "$(sed 's^\(.*\);\(.*\)^s/\1/\1;\2/;tx^;$as/.*/&;FALSE/\n:x' bbb.csv)" aaa.csv

The inner sed expression transforms bbb.csv into a sed program like this:

s/aaa/aaa;111/;tx
s/bbb/bbb;222/;tx
s/ccc/ccc;333/;tx
s/ddd/ddd;444/;tx
s/eee/eee;555/;tx
s/ggg/ggg;666/;tx
s/.*/&;FALSE/
:x

This is then interpreted by the outer sed to transform aaa.csv to the required output:

aaa;111
bbb;222
aaa;111
aaa;111
ccc;333
fff;FALSE
aaa;111

Note the tx in the generated sed program. These are sed conditional jumps to the x: label if the preceding s substitution matched. This allows a catch-all statement to be inserted last to substitute the "FALSE" text.

The caveat with this answer is it won't scale to large bbb.csv files, as the whole of the generated program is passsed on the command line. If this is a concern, the generated program could be put in a temporary file instead:

sed 's^\(.*\);\(.*\)^s/\1/\1;\2/;tx^;$as/.*/&;FALSE/\n:x' bbb.csv > bbb.tmp.sed
sed -f bbb.tmp.sed aaa.csv > ccc.csv

This will still be limited if bbb.tmp.sed ends up really big - I think sed will attempt to read the whole of this file into memory.

0

I strongly recommend writing this particular script in Python instead of any shell tool, simply because Python's standard library includes a CSV parser that actually handles all of the wacky wrinkles in CSV syntax. You would do something like this:

import csv
import sys

# sys.argv[1] = "aaa.csv", sys.argv[2] = "bbb.csv"
# sys.argv[3] = default value for mapping
# output written to stdout

with open(sys.argv[2], "rt") as f:
    rd = csv.reader(f, dialect="unix", delimiter=";")
    mapping = { row[0] : row[1] for row in rd }

with open(sys.argv[1], "rt") as f:
    rd = csv.reader(f, dialect="unix", delimiter=";")
    wr = csv.writer(sys.stdout, dialect="unix", delimiter=";",
                    quoting=csv.QUOTE_MINIMAL)

    for row in rd:
        wr.writerow([row[0], mapping.get(row[0], sys.argv[3])])

and then run it as e.g. python munge.py aaa.csv bbb.csv "" > ccc.csv.

In general, if it is not obvious how to do something in a shell script, consider not doing it in a shell script. Unless portability to arbitrarily crusty installations is more important than anything else (e.g. in the guts of an Autoconf macro) you will be happier writing Perl, Python, Ruby, node.js, etc.

  • your comments are very helpful .. – dadu May 7 '15 at 7:15

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