Data Cleaning with  SED 

sed 's/\x0D//g'   inputfile      # deletes CTRL-M character 

wget usage

To download multiple files from  web ;

 wget -r -nd --no-parent "http://foo.com" -A "*.jpg"

Print a character while command is executing in Linux

function printChar

{

    local pid=$!

    local delay=0.50

    local spinstr='...'

    echo "Loading "

    while [ "$(ps a | awk '{print $1}' | grep $pid)" ]; do

        local temp=${spinstr#?}

        printf "%s  " "$spinstr"

        local spinstr=$temp${spinstr%"$temp"}

        sleep $delay

        printf "\b\b\b"

    done

    printf "    \b\b\b\b"

echo

}

####

sqlplus -S $ouser/$opass@$oserver @$tmpPth/qry.sql & printChar

Deleting crlf and concat lines in Linux

 for dosya in 2023*/*.tem ; do echo $dosya ; cat $dosya|sed ':a;N;$!ba;s/\n//g' >> test ; done

Finding files that consist empty lines and concat

grep  -l ^$ filename | xargs -i cp {} tmp1/        # find files if file consist empty line(s)

for fname in tmp1/aero* ; do echo $fname ; purefile=`echo $fname|awk -F "/" '{print $2}'`; cat $fname |awk '{print}' ORS=' ' > tmp2/$purefile ; done        # deletes empty line and copies to another directory

Find Disk Write Performance; 

dd if=/dev/zero of=/tmp/output bs=16k count=10k  >> /root/diskwrite.txt  (runs in a crontab)

After a while;

cat diskwrite.txt |grep bytes|awk -F "," '{print $4}'|awk '{print $1}'|awk '{top=top+$NF; print top/NR}'

 Oracle Turkish Character Support;

echo "

set echo off

set verify off

set termout off

set trimout on

set heading off

set feedback off

set colsep ';'

set pagesize 0

set trimspool on

set headsep off

set linesize 5000

set serveroutput off

spool $tmpdir/${fname1}.txt

alter session set nls_language='TURKISH';

SELECT AIRPORT, a.ISTNO,${perbas}||'-'||${person},sum(VALUE) from TABLE1 a, TABLE2 where a.ICAO=b.ICAO and b.SENSOR_NAME IN('RAININST','PR_INS','RAIN_TDZ2','RAIN_TDZ1') and SENSOR_TIME between to_date('$perbas','YYYYMMDDHH24Mi') and to_date('$person','YYYYMMDDHH24Mi') group by AIRPORT,a.ISTNO order by 2 ;

spool off

exit " > $sql

sqlplus -S $ouser/$opass@$oserver @$sql

Change Spesific Columns Value According to a condition

This changes 4th columns value to "none" if it is zero.

awk 'BEGIN{FS=OFS=";"} $4 == "0" ;{$4 = "none"; } {print $0}' $filename 

Data Select From  SAP-IQ And Insert into Windows MS-SQL

#######################################################

function iq_select

{

echo "SELECT * ISTNO, YIL, AY, GUN, SAAT, DAKIKA, KAYIT_TURU, ORT_RUZGAR_YONU, ORT_RUZGAR_HIZI, MAKS_RUZGAR_YONU, MAKS_RUZGAR_HIZI, TOPLAM_YAGIS, SICAKLIK, NEM, convert(varchar,TARIH,20) FROM AA WHERE YIL=2022 and ay>1 and ISTNO between 17100 and 17102 " > $sql

 echo "go" >>$sql

/usr/local/bin/bsqldb -U user -S dbserver -P password -i $sql -o aa.txt -t "|" -q

sed -i 's/NULL//g' aa.txt

}

#######################################################

function sql_load 

{

freebcp table1 in aa.txt -S Mssqlsrv -U user -P password -c -b 1000 -t '|'

}

#######################################################

iq_select

sql_load

Split a row between pattern1 and pattern2

cat file |sed 's/^M//g' | sed -n "/pattern1/,/pattern2/p" > newfile

Türkçe diline ayarlanmamış Oracle veritabanına "Load Data" ile komut satırından veri yüklerken Türkçe karakterleri girmek için önce bunları İngilizce karşılığına çevirmek gerekiyor. Bunun için;

sed -e 's/Ş/s/g' -e 's/ş/s/g' -e 's/ğ/g/g' -e 's/Ğ/g/g' -e 's/ı/i/g' -e 's/İ/I/g' -e 's/ü/u/g' -e 's/Ü/U/g' -e 's/ö/o/g' -e 's/Ö/O/g' -e 's/ç/c/g' -e 's/Ç/c/g' f.txt > ff2.txt

kullanılabilir.

Sonrasında;

datafile=ff2.txt 

errors=`wc -l $datafile|awk '{print $1}'`

echo "Satir Sayisi: $errors"

echo "load data

infile '$datafile'

append

into table "AAA".TABLE1  

fields terminated by \"|\"

TRAILING NULLCOLS

(C1,C2,C3,C4)

" > table1.ctl

sqlldr user/pass@orcl1 control=table1.ctl bad=table1.bad log=table1.log errors=$errors date_cache=$errors SILENT=feedback

ile veri yüklenebilir. 

 Merge Two Files According To Common Columns

FileA:

17015;202101060930;2021;1;6;9;30;3;0.5;5;0.6;0.00;12.3;81.0;8.8;11.4;1021.7;1022.9

17015;202101060940;2021;1;6;9;40;0;0.7;1;0.8;0.00;12.3;82.0;9.0;11.5;1021.6;1022.8

17015;202101060950;2021;1;6;9;50;345;0.5;353;0.6;0.00;12.7;82.0;9.0;11.5;1021.5;1022.7

17015;202101061000;2021;1;6;10;0;319;0.4;8;0.6;0.00;12.7;83.0;9.2;11.6;1021.4;1022.6

17015;202101061010;2021;1;6;10;10;0;0.0;10;0.0;0.00;12.6;80.0;8.7;11.2;1021.3;1022.5

FileB:

17015;AKÇAKOCA;41.0895;31.1374;10

17018;DEVREK;41.1813;31.7985;1112

Command;

join <(sed 's/;/ /g' omgi_ondakikalik.txt|sort) <(sed 's/;/ /g' istbilgi.csv|sort) |sed 's/ /;/g' > omgi_10dk.txt

Result:

17015;202101060930;2021;1;6;9;30;3;0.5;5;0.6;0.00;12.3;81.0;8.8;11.4;1021.7;1022.9;AKÇAKOCA;41.0895;31.1374;10

17015;202101060940;2021;1;6;9;40;0;0.7;1;0.8;0.00;12.3;82.0;9.0;11.5;1021.6;1022.8;AKÇAKOCA;41.0895;31.1374;10

17015;202101060950;2021;1;6;9;50;345;0.5;353;0.6;0.00;12.7;82.0;9.0;11.5;1021.5;1022.7;AKÇAKOCA;41.0895;31.1374;10

17015;202101061000;2021;1;6;10;0;319;0.4;8;0.6;0.00;12.7;83.0;9.2;11.6;1021.4;1022.6;AKÇAKOCA;41.0895;31.1374;10

17015;202101061010;2021;1;6;10;10;0;0.0;10;0.0;0.00;12.6;80.0;8.7;11.2;1021.3;1022.5;AKÇAKOCA;41.0895;31.1374;10

 Another Date Loop

#start="2020-01-01 00:00"

for i in $(seq 0 1 670000); do  

   date -d "2020-01-01 0000 +$i minutes"  +%Y%m%d%H%M; 

done

Date Loop in Shell Script

start=1980-01-01

end=1981-12-31

start=$(date -d $start +%Y%m%d)

end=$(date -d $end +%Y%m%d)

while [[ $start -le $end ]]

do

        echo $start

        start=$(date -d"$start + 1 day" +"%Y%m%d")

done

#########################################

firstday=2022-02-06

lastday=2022-03-15

while [ "$firstday" != $lastday ]; do

  echo $firstday

  firstday=$(date -I -d "$firstday + 1 day")

done

Find Yesterday's or Tomorrow's date

date --date="1 days ago"
date --date="-1 day"
date --date='tomorrow'
date +%Y%m%d --date="-1 day"
date --date='1 year ago'
date --date='1 month ago'

tar from a file list

Sadece istenen dosyaları tarlamak için;

ls *.txt > a.txt
tar -czvf a.tar.gz --files-from a.txt

Split a word with awk

 sed 's/_t/ /g' file | awk '{print substr($0,1,4),substr($0,5,2),substr($0,7,2),$2,$3,$4,$5}'

file:
20191007_t15   1.9    64
20191007_t16   6.1    86
20191007_t17   6.5    80
20191007_t18   5.8    91
20191007_t19   5.4    93
20191007_t20   4.9    87
20191007_t21   5.0    90
20191007_t22   4.3    94

result:

2019 10 07 15 1.9 64
2019 10 07 16 6.1 86
2019 10 07 17 6.5 80
2019 10 07 18 5.8 91
2019 10 07 19 5.4 93
2019 10 07 20 4.9 87
2019 10 07 21 5.0 90
2019 10 07 22 4.3 94

grep a pattern with previous and next line

grep -A 1 : bir satır sonrasıyla birlikte görüntüler.
grep -B 1 : bir satır öncesiyle görüntüler.
grep -C 1 : bir satır öncesi ve sonrasıyla görüntüler.

ilk 3 kolon verisi aynı olan dosyanın aynı satırlarını silmek

awk '!a[$1,$2,$3]++' abc.txt

input:

6290 17 15 41.09 31.14 10 10 0
6290 17 18 41.18 31.80 1112 1112 2
6290 17 20 41.62 32.36 33 33 0
6290 17 20 41.63 32.33 33 30 0
6290 17 22 41.45 31.78 135 135 0
6290 17 22 41.45 31.80 137 137 0
6290 17 23 41.51 32.09 13 13 0
6290 17 24 41.98 33.76 64 64 0
6290 17 24 41.98 33.78 64 64 0


output:
6290 17 15 41.09 31.14 10 10 0
6290 17 18 41.18 31.80 1112 1112 2
6290 17 20 41.62 32.36 33 33 0
6290 17 22 41.45 31.78 135 135 0
6290 17 23 41.51 32.09 13 13 0
6290 17 24 41.98 33.76 64 64 0

linux sistem bilgisi görüntüleme

Linux komut satırından sunucu bilgilerini görüntülemek için;
dmidecode | grep -A5 '^System Information'