Writing to CSV using bash sed not working as expected

Question

I'm having some trouble getting this code to work, and I have no idea why it's not, Maybe one of you gurus can lend me a hand.

To begin with I have two CSV files structured as such:

Book1.csv:

Desc,asset,asset name,something,waiver,waiver name,init date,wrong date,blah,blah,target
akdhfa,2014,adskf,kadsfjh,123-4567,none,none,none,none,none,BOOP

Book2.csv

Desc,asset,asset name,something,waiver,waiver name,init date,wrong date,blah,blah,target
akdhfa,2014,adskf,kadsfjh,123-4567,none,none,none,none,none

(Lack of "BOOP" on the second book)

What I want is to scan Book1.csv for column 11. If it's there, find the matching row in Book2.csv based on asset and waiver. Then simply append the target to that line.

Here's what I've tried so far:

#!/bin/bash
oldIFS=IFS
IFS=$'\n'
HOME=($(cat Book1.csv))
for i in "${HOME[@]}"
do
    target=`echo $i | cut -d "," -f 11`
    asset=`echo $i | cut -d "," -f 2`
    waiv=`echo $i | cut -d "," -f 5`
    if [ "$target" != "target" ]
    then
        sed -i '/*${asset}*${waiv}*/s/$/,${target}/' Book2.csv
        fi
done
IFS=oldIFS

Everything seems to be working except for the sed command. Any suggestions?

Hm, should those *ś be .*'s?

Wrikken
– Wrikken

2014-02-11 00:26:17 +00:00
Commented Feb 11, 2014 at 0:26 — Wrikken
– Wrikken, Commented Feb 11, 2014 at 0:26

Floris · Accepted Answer · 2014-02-11 00:43:02Z

1

You are using

sed -i '/*${asset}*${waiv}*/s/$/,${target}/' Book2.csv

which means that the variables are not expanded (the ' quotes "hide" them).

Also the * needs something "in front of it" - probably you meant to use .* (otherwise you are looking for "any number of repeats of the last character in asset, etc.).

Just change it to

sed -i "/.*${asset}.*${waiv}.*/s/$/,${target}/" Book2.csv

Now the variables will be replaced with their value before the sed command runs, and the quantifier (*) should work properly, as it has something to quantify (.)...

answered Feb 11, 2014 at 0:43

Floris

46.6k7 gold badges73 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Patman Over a year ago

Thank you sir! You're a life saver! I knew it was probably something stupid.

Floris Over a year ago

Not really stupid. There are two types of mistakes: the ones you have made, and the ones you are going to make. The best you can do is hope that there are few repeats of type 1... I am most likely a few years ahead of you in the making of mistakes (and learning from them).

glenn jackman · Accepted Answer · 2014-02-11 00:58:43Z

0

~~You're using single quotes, which inhibit variable expansion. Change to double quotes.~~

This awk might be tidier:

awk -F, -v OFS=, '
    NR == FNR {boop[$2,$5] = $11; next}
    NF != 11  {$11 = boop[$2,$5]}
    {print}
' Book1.csv Book2.csv > tmpfile && mv tmpfile Book2.csv

awk does not have a -i option.

edited Feb 11, 2014 at 0:58

answered Feb 11, 2014 at 0:26

glenn jackman

249k42 gold badges233 silver badges362 bronze badges

4 Comments

glenn jackman Over a year ago

"target" would be the more logical array name to use.

Patman Over a year ago

Neither double quotes or the awk worked. Using cat and opening the file with excel provided nothing. Could you explain the awk step by step for me please?

glenn jackman Over a year ago

The first line reads Book1 and stores the 11th column in an associative array keyed by the 1st column. The second and third lines are processing Book2, inserting the 11th column based on what was in Book1, only if a line has less than 11 columns. You say excel: are you using cygwin? Do the csv files have \r\n line endings?

glenn jackman Over a year ago

Oops, I had the key wrong, you wanted asset and waiver, not desc.

Collectives™ on Stack Overflow

Writing to CSV using bash sed not working as expected

2 Answers 2

2 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related