AnswerBun.com

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

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 

2 Answers

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

Add your own answers!

Related Questions

First negative number row in Oracle

3  Asked on November 24, 2021 by user10916118

   

how to compare hashmap in Java?

2  Asked on November 24, 2021

   

Java Functional Interfaces and Lambda Expressions

2  Asked on November 24, 2021 by vilitaria

     

How to fill array in C++?

4  Asked on November 24, 2021 by kuz

 

Using while to exit for loop

3  Asked on November 24, 2021 by lojcs

   

How to register SFML Vector2 operators with AngelScript?

2  Asked on November 24, 2021 by veridis_quo_t

     

How to make a new line in Wrap Widget

2  Asked on November 24, 2021 by hng-trnh

   

AlarmManager and BroadcastReceiver in Android Oreo+

3  Asked on November 24, 2021 by kelton-holanda-fontenele

         

HOW? Controller return nothing/current view

5  Asked on November 24, 2021 by dent-argue

 

PHP get array child

2  Asked on November 24, 2021 by tgezginis

     

How to enable/disable table column text on click

1  Asked on November 24, 2021 by asif-zaidi

       

C# Assembly.Load() method loads the wrong dll?

1  Asked on November 24, 2021 by suns-flower

         

Ask a Question

Get help from others!

© 2023 AnswerBun.com. All rights reserved. Sites we Love: PCI Database, MenuIva, UKBizDB, Menu Kuliner, Sharing RPP, SolveDir