===== MySQL - autowrite in database =====

This script can be used to write the content of textfiles splittet into a database. The script will look automatically for 1 .txt file, reads it, write in database, deletes the file and looks for another one. A logfile will be written. 

<code bash mysqlwrite.sh>
#!/bin/bash

#endless loop 
while true
do

#Path to logfile
LOGPATH=/var/mysqlinsert.log

#Path to local textfiles
PATH=/var/textfiles/

#Path for textfiles after insert into mysql
READPATH=/var/textfiles/read/

#Username of MYSQL
MYSQLUSER=root

#Password of MYSQL
MYSQLPW=root

#Database.Table
DBTABLE=mysql.textfiles

#DATABASE - Columns of table
SCOL1=Column1
SCOL2=Column2
SCOL3=Column3
SCOL4=Column4
SCOL5=Column5
SCOL6=Column6

#get the name of the local textfile.
#every data with .txt ending will be read, because of head -1 only one file will be taken and the name will be saved in var DATA
DATA=`ls $PATH. | grep .txt | head -1`

#if data with ending .txt exists
if [ -n "$DATA"  ]
then

#init i
i=1

#this is needed for spaces, if not used, all tablerows will be in one row
IFS=$'\012'

#for every row in DATA do
for i in $(cat $PATH$DATA); 
do 
		# cut columns with -d "|" ("|" is the cut sign) IFS is used for spaces.
		COL1=$(echo $i | cut -d "|" -f1 | tr "$IFS" '\n')
		COL2=$(echo $i | cut -d "|" -f2 | tr "$IFS" '\n')
		COL3=$(echo $i | cut -d "|" -f3 | tr "$IFS" '\n')
		COL4=$(echo $i | cut -d "|" -f4 | tr "$IFS" '\n')
		COL5=$(echo $i | cut -d "|" -f5 | tr "$IFS" '\n')
		
#if COL1 is "hello" then set a 1 to COL6, else set 0
if [ $COL1==hello ]
then		

#Connects to MySQL with -u USERNAME and --password PASSWORD. if other hostname use -h HOSTNAME
#INSERT in DATABASE.TABLE, add columns and the columnvalues
mysql -u $MYSQLUSER --password=$MYSQLPW  << EOFMYSQL

INSERT INTO $DBTABLE ($SCOL1, $SCOL2, $SCOL3, $SCOL4, $SCOL5, $SCOL6) VALUES ('$COL1', '$COL2', '$COL3', '$COL4', '$COL5', 1);
EOFMYSQL

else
#Connects to MySQL with -u USERNAME and --password PASSWORD. if other hostname use -h HOSTNAME
#INSERT in DATABASE.TABLE, add columns and the columnvalues
mysql -u $MYSQLUSER --password=$MYSQLPW  << EOFMYSQL

INSERT INTO $DBTABLE ($SCOL1, $SCOL2, $SCOL3, $SCOL4, $SCOL5, $SCOL6) VALUES ('$COL1', '$COL2', '$COL3', '$COL4', '$COL5', 0);
EOFMYSQL
fi

done

#write the vars into the log
echo "----------------------------------------------" >> $LOGPATH; #line between files
echo "Data" $PATH$DATA >> $LOGPATH;
echo "Column1" $COL1 >> $LOGPATH;
echo "Column2" $COL2 >> $LOGPATH;
echo "Column3" $COL3 >> $LOGPATH;
echo "Column4" $COL4 >> $LOGPATH;
echo "Column5" $COL5 >> $LOGPATH;

#move the local textfile into the read dir, otherwise it will be read again over and over...
mv $PATH$DATA $READPATH$DATA
fi
#short wait time -> less cpu usage (effects = 1 File/sec, otherwise 2-3 Files/sec)
sleep 1
done
</code>
\\
\\
~~DISCUSSION:closed~~