Dear All,

I am writing a bash script which can read data from file. File contains output of "last" command with some awk tricks.
Following is my file data format.

kamran pts/0 10.111.11.51 Thu-Oct-30 22:33 logged
kamran pts/0 10.111.11.51 Thu-Oct-30 21:06 22:33
kamran pts/0 10.111.11.51 Wed-Oct-29 21:29 05:00
kamran pts/1 10.111.11.51 Tue-Oct-28 22:48 04:33
kamran pts/0 10.111.11.51 Tue-Oct-28 21:21 00:21

Now my bash script is following to put data in mysql database.
Issue I am facing is that the below script inserted data from file with picking up the 1st row of my data file i.e all rows in mysql table has same records matching with row1 of data file.

#/!bin/bash
#Defining Variables
ssh_sessions_file="/home/kamran/ssh_logins.txt"

#Begin Code
# extrcting the Information of ssh logins from file ssh_logins.txt
#

NumberOfLines=$(cat $ssh_sessions_file | wc -l)
j=1
while [ $NumberOfLines -gt 0 ]
do
Login_Name=$(cat $ssh_sessions_file | awk '{print $1}')
Login_Terminal=$(cat $ssh_sessions_file | awk '{print $2}')
Login_IP=$(cat $ssh_sessions_file | awk '{print $3}')
Login_Date=$(cat $ssh_sessions_file | awk '{print $4}')
Login_Start_Time=$(cat $ssh_sessions_file | awk '{print $5}')
Login_End_Time=$(cat $ssh_sessions_file | awk '{print $6}')


echo $Login_Name
echo $Login_Terminal
echo $Login_IP
echo $Login_Date
echo $Login_Start_Time
echo $Login_End_Time
let "j +=1"
let "NumberOfLines -=1"
mysql -u root -ppassword ssh_logins <<- _END_
INSERT INTO ssh_logins (Login_Name, Login_Terminal, Login_IP, Login_Date, Login_Start_Time, Login_End_Time) VALUES ('$Login_Name','$Login_Terminal','$Login_IP','$Login_Date','$Login_Start_Time','$Login_End_Time');
_END_
done

##################

Please anybody can help why this script duplicating 1st row in all my mysql table rows. Why not inserting correct row records from file with help of loop in script.

regards,
kamran

Looks complicated. Instead of the bash script, why not use SQL?

http://dev.mysql.com/doc/refman/5.1/en/load-data.html

1 members found this post helpful.

Your script says, database name is ssh_logins as well as table name is ssh_logins. Is it confirmed?

You should not run queries like this from a bash script

Try this code

1 members found this post helpful.

Similar to your other thread:
http://www.linuxquestions.org/questi...db-4175523722/

There are THOUSANDS of very easily found bash scripting tutorials...even one in my posting signature, and many others online that have examples on how to do MySQL inserts from files. Have you looked at ANY of them?

And I'll again suggest, as both Guttorm and eklavya have, that you just use the standard MySQL commands to load a file.

Dear eklavya,

Yes I have set DB name and table name ssh_logins. I have tried your provided query.

But result is same that it is inserting 1st row record of my data file in all rows of my mysql table.

Any other suggestion please.

Regards,
Kamran

You got other suggestions; did you read and understand them??

AGAIN: you need to use the native MySQL commands both correctly, and use all of their capabilities. You were already provided links that tell you how to import a CSV file using nothing by MySQL...did you read that? Try it?

And again, it was suggested that you look at any of the bash scripting tutorials..did you? Because if you're only getting the first row of data, it would seem VERY obvious that you aren't looping through the file correctly.

Dear All,

Thanks for your replies and suggestions.

Issue resolved by reading output file in csv format. Following is now a simple bash script code for doing all.

#/!bin/bash

IFS=,
while read Login_Name Login_Terminal Login_IP Login_Date Login_Start_Time Login_End_Time
do
echo "INSERT INTO ssh_logins (Login_Name,Login_Terminal,Login_IP,Login_Date,Login_Start_Time,Login_End_Time) VALUES ('$Login_Name','$Login_Terminal','$Login_IP','$Login_Date','$Login_Start_Time','$Login_End_Time');"

done < /home/kamran/output.csv | mysql -u root -p asterisk ssh_logins;

#################

Regards,
Kamran

1 members found this post helpful.

Glad it's working, but:

• You were given advice SEVERAL TIMES about how you are doing that insert. You were told it was a bad idea, yet you did it anyway.
• You were given advice about how to use the built-in MySQL tools that already exist to do nothing BUT import CSV files, but didn't acknowledge or use them.
• You were asked to use CODE tags around your code, and didn't

If you're going to ask for advice and suggestions, you might want to consider at least ACKNOWLEDGING them. If you're going to ignore what people tell you, there's little point in posting.