Archive

Posts Tagged ‘bash’

awk – Printing row totals

April 18, 2012 Leave a comment

Problem:

Had various files with totals that had to be combined/totaled into one file.

From

cars,30,cars,15,cars,5
bikes,0,bikes,0,bikes,6
trains,10,trains,30,trains,15
buses,0,buses,0,buses,0

To

cars,50
bikes,6
trains,55
buses,0

Solution:

I went with awk.


paste -d, file1.txt file2.txt file3.txt  | \
cut -d, -f1,2,4,6  | \
awk -v FS="," 'total=$2+$3+$4+1 {print $1,total-1}'

NB:

Using

awk -v FS="," 'total=$2+$3+$4 {print $1,total}'

Will not return any results that have a zero. So add one to the total then remove it when printing so that you can get the zero counts.

awk -v FS="," 'total=$2+$3+$4+1 {print $1,total-1}'
Advertisements
Categories: awk Tags:

bash – Trimming spaces from variables.

March 15, 2012 Leave a comment

Problem:
I was reading “settings” from a tab separated text file. The values I was getting had extra white spaces that were not needed. I needed to strip them out. Here is the sample of the text file I was using.

cat sample_file
this | is a sample |   file with |    unwanted spaces   | 

Solution:
After a quick search on google I got a few solutions. Here is what worked for me.

1. sed

cut -d"|" -f4 sample_file | sed 's/^ \+\| \+$//g'

2. awk

cut -d"|" -f4 sample_file | awk '{gsub(/^ +| +$/,"")}1'

Source:
http://stackoverflow.com/questions/369758/how-to-trim-whitespace-from-bash-variable

Categories: awk, bash, sed Tags: ,

Interesting sites

February 3, 2012 Leave a comment
Categories: Interesting Tags: ,

tsql – Query MSSQL from the command line

April 22, 2011 Leave a comment

Problem:
Run the same query on a MSSQL database several times.

Solution:
tsql

Background.
I had to get data for various months from a table on MSSQL. Rather than sit down in front of the computer and manually change each value and re-run the query using SQL Query Tool(?) I opted to use the command line. So I moved to a linux box that could:
1. Access the Windows box that had MSSQL server installed.
2. Had tsql installed.

First I wrote the query.
$cat query

use cars 
go
SELECT LEFT(RecordTime, 11) AS Date, COUNT(CarType) FROM sales WHERE datepart(yyyy, RecordTime) = '2010' AND datepart(mm, RecordTime) = '$month' GROUP BY LEFT(RecordTime, 11) ORDER BY LEFT(RecordTime, 11)
go

$month is the variable that is going to change

On the linux box.
Check that I could connect remotely

shell>tsql -S [server-ip-address] -U [username] -P [password]
locale is “C”
locale charset is “ANSI_X3.4-1968”
1> exit
shell>

Once I knew I could connect I just needed to run the queries. So on the command line I did

shell>for month in 01 05 10 11 12; do sed “s/\$month/$month/” query | tsql -S [server-ip-address] -U [username] -P [password] > results_$month.csv; done

Thats it.

Categories: MSSQL Tags: ,

Useless use of cat

March 26, 2011 Leave a comment

http://partmaps.org/era/unix/award.html

Above link looks dead.

But a search of “Useless Use of cat” gave the following results:

http://www.smallo.ruhr.de/award.html
https://en.wikipedia.org/wiki/Cat_%28Unix%29#Useless_use_of_cat

Categories: Interesting Tags: , ,

Insufficiently known POSIX shell features

March 25, 2011 Leave a comment
Categories: Interesting Tags: ,