1

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

Improve this question

2 Answers 2

Reset to default
1

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');
Improve this answer
Sign up to request clarification or add additional context in comments.

9 Comments

This is great. Thanks. I have introduced a few more variables there are a total of six now - this isn't a problem I just changed the modulus condition. The last one is a string though. E.g. (Activity=I walked the dog). With this awk I am only capturing the first word of the string to be stored in this field. Any ideas?
actually it should still work.. because read a b c will read a complete line, split on whitespace (default) and then put the first token in a, the second in b,... but if there are more token than variables provided it puts all the remaining tokens into the last variable. see: read a b c <<< "1 2 3 4"; echo $c; gives 3 4
Note that this only works if there's one variable that has multiple words, and it has to be the last one. Also, the string can't contain '='.
@Barmar why did you revoke your solution?
@Mixiul I have updated my answer...If its still not working for you please provide an example where it fails..
|
0

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.