Bash cut and insert

Question

I am trying to use a bash script to input from a colon delimited file and insert these values into a mysql database. What I have so far is the ability to cut these appropriately and print them out, but I don't know how to manipulate data with each run of the cut.

ie.

cut -d: -f1 input.txt | \
while read firstVal;
do 
   echo firstVal;
done

From this, it looks like I would be able to place a mysql insert line in place of where I am echo'ing the name to achieve what I want to do. So I can do the same thing for the second value by replacing f1 with f2, but I don't know how to use multiple values at the same time and then insert f1, f2, f3 etc. Any ideas?

Answer 1

Why not use a more... robust scripting language? (php, python, etc)

echo "test1:test2:test3" | \
awk -F: '{print "INSERT INTO `tableName` (`column1`, `column2`, `column3`) \
VALUES('\''"$1"'\'', '\''"$2"'\'', '\''"$3"'\'' ) " }'

Produces:

INSERT INTO `tableName` (`column1`, `column2`, `column3`) \
VALUES('test1', 'test2', 'test3' )

Awk uses the -F flag to set a delimiter. the single quotes for value adds is achieved by encasing \' in a set of it's own single quotes. (magic you can read about here:https://stackoverflow.com/questions/9899001/how-to-escape-single-quote-in-awk-inside-printf) but ultimately resulting in: '\''

Pulling it Together

1. Read each line of the input file one line at a time and run the awk command over each.
2. Save the output of the command to a variable
3. echo "$variable; " | mysql -u username -pPASSWORDHERE databaseName

You will have to make the connection and run the query for each line. If that is an issue, you can conceivably ; delimit the insert command list, combin them all and run them all in one connection (IE: one call to the mysql command)

Answer 2

If available i'd suggest using phpmyadmin's import CSV feature, it includes the ability to change the delimiter character, among others.