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 - 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