Stack Overflow Asked on January 1, 2022
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
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";
Answered by Léa Gris on January 1, 2022
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 ...
Answered by Barmar on January 1, 2022
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP