shell script to find and replace value from csv file

sajesh.pp · 08-01-2017, 07:20 AM

Could some one please help me with requirement below ?
I am looking for a shell script to search a cell value in csv file and replace corresponding cell in same column.
Below is my csv file (report.csv)
#,Device,Status
1,Device1,Used
2,Device2,Free
3,Device3,Free

The search variable will take as input while executing the script.

eg: Enter the device name
user enters "Device2"

Next step : script should search for Device2 in report.csv. If it find Device2, check value in status column corresponding to it and if the value is Free replace it as Used else exit the script saying Device2 already in use

Thanks,
Sajesh

TB0ne · 08-01-2017, 07:41 AM

Quote:

Originally Posted by sajesh.pp

Could some one please help me with requirement below ? I am looking for a shell script to search a cell value in csv file and replace corresponding cell in same column. Below is my csv file (report.csv)
#,Device,Status
1,Device1,Used
2,Device2,Free
3,Device3,Free

The search variable will take as input while executing the script.

eg: Enter the device name user enters "Device2"

Next step : script should search for Device2 in report.csv. If it find Device2, check value in status column corresponding to it and if the value is Free replace it as Used else exit the script saying Device2 already in use

Ok...so now that we know your 'requirement', can you show us what YOU have done/written/tried of your own, and tell us where you're stuck?? We are happy to help you if you're stuck, but we will not write your scripts for you. There are many bash scripting tutorials you can find with a brief Internet search, many of which have examples to help you get started.

Please read the LQ Rules and the "Question Guidelines" link in my posting signature.

Turbocapitalist · 08-01-2017, 07:49 AM

I'd start with awk or perl. If you have a really strict formatting with only commas between the fields and nowhere else, then use awk. If it is more complex, with commas sometimes in quoted fields, then use perl with a CPAN module.

Show us your code so far and where you've gotten stuck and we'll be able to help you move forward.

TheEzekielProject · 08-01-2017, 02:23 PM

I would think that sed would also make a good candidate here

sajesh.pp · 08-02-2017, 05:58 AM

Quote:

Originally Posted by TB0ne

Ok...so now that we know your 'requirement', can you show us what YOU have done/written/tried of your own, and tell us where you're stuck?? We are happy to help you if you're stuck, but we will not write your scripts for you. There are many bash scripting tutorials you can find with a brief Internet search, many of which have examples to help you get started.

Please read the LQ Rules and the "Question Guidelines" link in my posting signature.

Her the code I have tried
#!/bin/bash
dvc=""
FILE_HOME="/tmp/test/"

update_value (){
awk -F, -v d="$dvc" '$2==d{if($3=="Free")$3="Used"; else print "In use"; input_value;}1' OFS=, $FILE_HOME/file > $FILE_HOME/file1
mv $FILE_HOME/file1 $FILE_HOME/file
}
input_value (){
echo -e "Enter device\n"
read dvc
update_value
}
input_value

This code is fine when the "if statement" is true. But when it is false, it is not calling function "input_value" from else section. It just print "In use" along with other entries in csv file to new file.
Is this the right approach I am doing ? I would be happy to see if there a different/better approach to achieve the requirement.

Thanks,
Sajesh

Turbocapitalist · 08-02-2017, 06:31 AM

It looks like you're mixing shell scripts with awk scripts. I'd stick with just awk for this.

Also, it helps readability when code is posted between [code] and [/code] tags.

sajesh.pp · 08-02-2017, 06:40 AM

Quote:

Originally Posted by Turbocapitalist

It looks like you're mixing shell scripts with awk scripts. I'd stick with just awk for this.

Also, it helps readability when code is posted between [code] and [/code] tags.

but, how can I get user input with in awk and also exit the program when if statement is false

Turbocapitalist · 08-02-2017, 07:32 AM

Ok. You'll need a wrapper in shell to get input from the user. In that case maybe something like this:

Code:

dvc="";
FILE_HOME=".";

echo -n "Enter device\n"
read dvc

while ! awk -v d="$dvc" \
    'BEGIN { FS="[, ]+"; }

    $2==d {
        if($3=="Free") { $3="Used"; }
        else { fail++; }
    }
    { $0=$0; print; }

    END { if(fail) { exit(1); } }' \
    $FILE_HOME/file > $FILE_HOME/file1;
do
    echo "Device in use";
    echo -n "Enter device\n";
    read dvc;
done;

cat $FILE_HOME/file1;

exit 0;

scasey · 08-02-2017, 05:28 PM

Quote:

Originally Posted by sajesh.pp

Her the code I have tried

Code:

#!/bin/bash
dvc=""
FILE_HOME="/tmp/test/"

update_value (){
        awk -F, -v d="$dvc" '$2==d{if($3=="Free")$3="Used"; else print "In use"; input_value;}1' OFS=, $FILE_HOME/file > $FILE_HOME/file1
        mv $FILE_HOME/file1 $FILE_HOME/file
}
input_value (){
        echo -e "Enter device\n"
        read dvc
        update_value
}
input_value

This code is fine when the "if statement" is true. But when it is false, it is not calling function "input_value" from else section. It just print "In use" along with other entries in csv file to new file.
Is this the right approach I am doing ? I would be happy to see if there a different/better approach to achieve the requirement.

Thanks,
Sajesh

Doesn't the semi-colon after "In use" terminate the else clause? Shouldn't there be brackets?: (untested)

Code:

awk -F, -v d="$dvc" '$2==d{if($3=="Free")$3="Used"; else {print "In use"; input_value;}...

MPH426 · 08-04-2017, 12:16 PM

You could try this. Note: You'll need vim-minimal installed and your input will be case sensitive

Code:

#! /bin/ksh
printf "Enter Device: "
read device
print '%s/'$device',Free/'$device',Used\nwq' | ex Report.csv

MadeInGermany · 08-05-2017, 01:07 AM

Quote:

Originally Posted by scasey

Doesn't the semi-colon after "In use" terminate the else clause? Shouldn't there be brackets?: (untested)

Code:

awk -F, -v d="$dvc" '$2==d{if($3=="Free")$3="Used"; else {print "In use"; input_value;}...

As said before, you cannot run a shell function from awk.

MadeInGermany · 08-05-2017, 01:32 AM

Quote:

Originally Posted by Turbocapitalist

Ok. You'll need a wrapper in shell to get input from the user. In that case maybe something like this:

Code:

dvc="";
FILE_HOME=".";

echo -n "Enter device\n"
read dvc

while ! awk -v d="$dvc" \
    'BEGIN { FS="[, ]+"; }

    $2==d {
        if($3=="Free") { $3="Used"; }
        else { fail++; }
    }
    { $0=$0; print; }

    END { if(fail) { exit(1); } }' \
    $FILE_HOME/file > $FILE_HOME/file1;
do
    echo "Device in use";
    echo -n "Enter device\n";
    read dvc;
done;

cat $FILE_HOME/file1;

exit 0;

You can have more than one statement between while-do and until-do and if-then.
The last exit status counts.

Code:

dvc=""
FILE_HOME="."

until
    echo "Enter device"
    read dvc
    awk -v d="$dvc" '
        BEGIN { OFS=FS="," }
        $2==d {
            if ($3=="Free") { $3="Used" }
            else { fail=1 }
        }
        { print }
        END { exit (fail) }
    ' $FILE_HOME/file > $FILE_HOME/file1
do
    echo "Device in use"
done

cat $FILE_HOME/file1

exit 0