CODEWITHSUNDEEP
mysqlbashsedawk
user1197252
user1197252

Reputation:

Populate MySQL table using txt file and bash script

I am trying to write a bash script that will take a text file and use its content to populate a MySQL table.

The text file contains numerous entries that I will need to add to the table.

Example of text file contents:

Firstname=bob
Surname=ross
Age=9
Firstname=gary
Surname=graeme
Age=19
Firstname=henry
Surname=harry
Age=23

The text file is structured like the example above, the same 3 variables are constantly assigned new values for each unique entry that would go into the mysql table.

I have used sed to remove all empty lines and lines beginning with "#" in the text file that I would not want to process.

My question is how do I use bash to make an sql insert every three lines using the contents in textfile to generate the mysql query. I know I need to loop through the file 3 lines at a time until the end of the text file is reached and somehow parse each value after "=" and assign it to a variable in the loop iteration to insert the mysql data but I am not sure how to do this.

I know that I will need to have a new table entry every 3 lines.

echo "INSERT INTO $table (Firstname, Surname, Age) VALUES ('$Firstname', '$Surname', '$age');" | mysql $db

Thanks

Upvotes: 1

Views: 4638

Answers (2)

tzelleke
tzelleke

Reputation: 15345

I split each line in the input on = and use the second part.
Then I pipe this to the while-loop where I use read fname sname age to populate the variables inside the loop. I use NR % 3==0 {print("")} to introduce a linebreak in the output every three lines.

awk -F"=" '{printf("%s ", $2)}; NR % 3==0 {print("")}' input_file | \
while read fname sname age; do
    echo "INSERT INTO $table (Firstname, Surname, Age)
      VALUES ('$fname', '$sname', '$age');" | mysql $db
done

UPDATED:
input:

Firstname=bob
Surname=ross
Age=9
Activity=1 2 3 4 5
Firstname=gary
Surname=graeme
Age=19
Activity=1 2 3 4 5
Firstname=henry
Surname=harry
Age=23
Activity=1 2 3 4 5

with this code:

awk -F"=" '{printf("%s ", $2)}; NR % 4==0 {print("")}' input | \
while read fname sname age act; do
    echo "'$fname', '$sname', '$age', '$act');" 
done

gives:

'bob', 'ross', '9', '1 2 3 4 5');
'gary', 'graeme', '19', '1 2 3 4 5');
'henry', 'harry', '23', '1 2 3 4 5');

Upvotes: 1

Iwo Kucharski
Iwo Kucharski

Reputation: 3825

If it's possible to change the structure of file, just try to use LOAD DATA INFILE ... instruction. Detailed specification is here. It's a lot faster than you would write it by yourself :)

If not, try this.

Upvotes: 0

Related Questions