Assigning a value from a text file to variable in shell in loop

Question

I have text file, which will contain 1 record and more. I need to assign each row values of a column to a variable during each loop for example :

test.txt:

1234,12,IN,John,[email protected]
1111,10,SA,John,[email protected]
2222,11,EU,John,[email protected]
3333,13,CN,John,[email protected]
4444,14,US,John,[email protected]

Delete statement :

delete from table1.test where id=$var1 and code=$var2 and country='$var3'

loop 1 - delete query will use the below variable values

$var1=1234, $var2=12, $var3=IN 
delete statement

loop 2 - delete query will use the below variable values

$var1=1111, $var2=10, $var3=SA 
delete statement

loop 3 - delete query will use the below variable values

$var1=2222, $var2=11, $var3=EU
delete statement

loop 4 - delete query will use the below variable values

$var1=3333, $var2=13, $var3=CN 
delete statement

Please make the question clear if you want to know the delete statement or a way to get the values into the variable. — LogicIO
– LogicIO, Commented Jul 24, 2020 at 17:52
You'd better have absolute control over your input file: bobby-tables.com — glenn jackman
– glenn jackman, Commented Jul 24, 2020 at 20:19

Barmar · Accepted Answer · 2020-07-24 20:32:16Z

2

Use a while read loop, setting $IFS to a comma to split the lines.

while IFS=, read -r var1 var2 var3 rest
do
    echo "delete from table1.test where id=$var1 and code=$var2 and country='$var3'"
done < test.txt | mysql ...

edited Jul 24, 2020 at 20:32

answered Jul 24, 2020 at 17:54

Barmar

789k57 gold badges554 silver badges669 bronze badges

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

3 Comments

Gordon Davisson Over a year ago

Note that if the file is being modified in the loop (e.g. if the lines are actually being deleted, rather than "delete ..." just being printed), then this will have trouble. Reading from a file as it's being changed is almost never safe.

Barmar Over a year ago

@GordonDavisson I don't think the lines are being deleted, I believe that he's using the file to submit delete queries to a database.

Gordon Davisson Over a year ago

I think you're right, but I'm not entirely clear how it's being used so I thought it safer to add a caveat

Léa Gris · Accepted Answer · 2020-07-24 21:32:05Z

Another approach rater than looping with bash:

<input.csv cut -d',' --output-delimiter=' ' -f1,2,3 |
  xargs -l printf 'delete from table1.test where id="%s" and code="%s" and country="%s";\n' >deletes.sql

Explanations:

<input.csv cut -d',' --output-delimiter=' ' -f1,2,3 |: extract first 3 comma delimited fields from input.csv, and turn them into space delimited arguments.
xargs -l printf >deletes.sql: xargs feeds lines of arguments to printf to be formatted into SQL statements and writes the output to the deletes.sql file.

Or use awk to do it all at once:

awk -F, '{ printf("delete from table1.test where id=\"%s\" and code=\"%s\" and country=\"%s\";\n", $1, $2, $3) }' input.csv >deletes.sql

Or with a stand-alone csv2sql awk script:

#!/usr/bin/env -S awk -f

BEGIN {
  FS=","
}

{
  printf( "delete from table1.test where id=\"%s\" and code=\"%s\" and country=\"%s\";\n", $1, $2, $3)
}

Usage:

# Make csv2sql executable
chmod +x csv2sql

# Run it
./csv2sql input.csv >output.sql

Output generated from sample:

delete from table1.test where id="1234" and code="12" and country="IN";
delete from table1.test where id="1111" and code="10" and country="SA";
delete from table1.test where id="2222" and code="11" and country="EU";
delete from table1.test where id="3333" and code="13" and country="CN";
delete from table1.test where id="4444" and code="14" and country="US";

Collectives™ on Stack Overflow

Assigning a value from a text file to variable in shell in loop

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related