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.
#!/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
Discussion