Differences

This shows you the differences between two versions of the page.

@@ Line 1: / Line 1: @@
+===== 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~~

Lunetikk's IT Wiki

User Tools

Site Tools

Differences

Page Tools