How to read the output of a command line by line and pass it as a variable?

Hi,

I have some 2000 names in a table like below.

Java
Oracle/SQL
ANSI SQL
SQL,DWH,DB
DB&Java

And by using for loop in my code i am able to get a single word but if there is any special character or space then it is considering as a next line.

I have to execute the below queries in looping.

course=select course from table group by 1 order by 1;

create table table2 as
select * from table where course=$course;
Query3
Query4...

Above set of queries used to execute with different course each time.

I tried using while loop with no luck and unsure what exactly is missing in my code.

Thanks.

I would perhaps be able to tell you that, provided i could see it. As it is my crystal ball is currently in repair and i cannot see the code on your computer from here.

I can still tell you how to write a while-loop, though, because it should provide exactly what you want:

....
InFile="/path/to/your/inputfile"
line=""

while read line ; do
     echo "the line read: $line"
done < "$InFile"

This will read the content of the (text-)file "/path/to/your/inputfile" and output it line by line with a prefixed text of "the line read: ". The content of the line in question is in the variable "line" which you can use to base your own code on.

Notice that this will fail if the file is DOS-style formatted (that is, has CR/LF-line separators instead of the UNIX-style newline characters).

If the file contains things you want to filter out before processing it (like i.e. shell-style comments) you can use a filter to pipe into the while-loop like this (replace "<b>" and "<t>" with literal blank/tab characters, the sed-command removes leading/trailing whitespace, comments and empty lines):

....
InFile="/path/to/your/inputfile"
line=""

sed 's/#.*//;s/^[<b><t>]*//;s[<b><t>]*$//;/^$/d' "$InFile" |\
while read line ; do
     echo "the line read: $line"
done

I hope this helps.

bakunin

Thanks for the quick response!

I am using netezza sql query like below, and the table contains some 2k records in it.
I want to read the result record by record and pass it in the next set of sql steps.

course=select course from table group by 1 order by 1;
N=0
for course in `nzsql -host -d db -Atc "course=select course from table group by 1 order by 1;"` ; do
   
      test[$N]="$course"
      echo "$N = $course"     #to confirm the entry
       
  let "N= $N + 1"
create table table2 as
select * from table where course=$course;
One more query here..Query2
Query3

when i use the above for loop it is reading the string line by line but if i have space or special characters between the string then it is considering as next line.
table contains the data like below.

Java
Oracle/SQL
ANSI SQL
SQL,DWH,DB
DB&Java

Above piece of code is taking the first line Java but when coming to the next line, it is splitting and taking the first word in $course.

Your code sample seems to mix sql and shell code and thus doesn't make sense to me, but that may be my fault. Did you consider deploying bakunin's proposal, i.e. replacing your for loop with a while read loop? As the for construct splits arguments on spaces (although not on "/")

Thanks Rudic and Bakunin for your inputs!

Actually i am able to read it from file but unable to get it from the query result.

Did you consider piping the query results into a while read ... loop? Alternatively, recent shells (your version of which you fail to mention) offer "process substitution" which is another mechanism to feed a command's / process' output into the stdin of other commands.

Tried the below code but it couldn't worked fro me.

IFS=''
while read course
do
echo $course
done < <(course=`nzsql -host -d db -Atc "select course from table group by 1 order by 1;"`)

For now, i have created a a file from query result and reading from the file.

And i have 2k course names in the table/file, so i have to schedule it in batch wise like 100 courses at a time for the execution on my database.I never tried this before and would it be possible for you to suggest a way how to schedule this requirement as batch jobs

Please become accustomed to provide decent context info of your problem.

It is always helpful to carefully and detailedly phrase a request, and to support it with system info like OS and shell, related environment (variables, options), preferred tools, adequate (representative) sample input and desired output data and the logics connecting the two including your own attempts at a solution, and, if existent, system (error) messages verbatim, to avoid ambiguities and keep people from guessing.

man bash :

What do you think is the output of

course=`nzsql -host -d db -Atc "select course from table group by 1 order by 1;"`

?

I have no idea how you arrived at this, but if this would work, shell programming woud be a bad idea.

First: forget about backticks. They are just there for backwards compatibility with a shell which is not in use any more since about 25 years. Just forget they exist.

Second: even if you use backticks, x=`some -command` is a variable assignment. In fact it does: run the command some -command and assign its output to variable x. So, what you did amounts to:

while read line ; do
     command
done < x="blabla"

which is obviously not what you intended. You do it the same way i showed you with sed : by pipelining it like this:

command |\
while read line ; do
     other_command "$line"
done

The pipeline can have more than one part, so this would also be possible:

command1 |\
command2 |\
command3 |\
.... |\
while read line ; do
     other_command "$line"
done

This runs command1 , then pours its output into command2 , the output of command2 into command3 and so forth, until the output of the last command is poured into the loop, which reads it line by line and processes it.

I am sure you can now construct your own script with ease, no?

By the way: if you want to process lines like you do stay generally away from for -loops. While-loops are a lot more flexible when it comes to parsing input, even when word-splitting is involved.

Suppose you have a file (or process output, which is effectively the same) of this structure:

line1-word1 line1-word2 line1-word3
line2-word1 line2-word2 line2-word3
line3-word1 line3-word2 line3-word3
line4-word1 line4-word2 line4-word3

and you want to process the second word of every line ("line1-word2", "line2.word2", etc.) then this will do it:

InFile="/path/to/input"
secondword=""

while read junk secondword junk ; do
     echo "this is $word"
done <"$InFile"

Try it out. You could do that with some gymnastics and a for-loop too, but this is IMHO a lot easier to write and a lot easier to read and understand.

I hope this helps.

bakunin

Sure RudiC,will make a note of it.

---------- Post updated at 03:15 PM ---------- Previous update was at 03:13 PM ----------

Bakunin, Thanks a ton for your detailed explanation.I will try to construct my script now!

I am facing one more challenge here, for time being i have passed the variable from a file line by line and it is working fine but i have some 2k names in the file so i need to make checkpoint at 100 each time and then start executing the steps till end of the file. For example, my script should consider the first 100 names line by line,passing it as a parameter and execute the code. and the when i execute it next time it should skip the first 100 lines and pick it from 101 line till 200..

Thanks again!

There are umpteen possible approaches to your last question, e.g. using a counter in the loop, splitting the input file, using nested loops, mayhap a dedicated script.
Why don't you present your script and input data, and we'll discuss?