Cut command

To get any n characters of each line from a file, use cut.

Code to get first 3 characters of file

cut -c1-3 filename
217
207
284
238
216
219

Other examples
1) Get just specific character
echo "your text here" | cut -c2
> o

2) Get everything from a character
echo "your text here" | cut -c2-
> our text here

3) argument -f can be used to get specific fields from the file. Default delimiter is tab. It can be overridden by -d.
e.g.
cat rhyme
>
jack , and jill, went
up the,hill
to fetch,a pail
of water.

cut -f1 -d, rhyme
>
jack
up the
to fetch
of water.

4) argument -s along with -f is used to suppress lines that do not contain the field delimiter.

cut -f1 -d, -s rhyme
>
jack
up the
to fetch

5) Get a range of fields
cut -f2-3 -d, -s rhyme
>
and jill, went
hill
a pail

Cut option -b can be used to obtain same results as -c.

Function that returns value in VBA

To code a function that returns value to the calling sub, use FunctionName as the VariableName in the function.

e.g.
function findrownbr()
findrownbr=10
end function

sub readCellData ()
cell_nbr = findrownbr()
msgbox Range("A" &cell_nbr)
end sub

Newline Character in VBA

Chr(13) inserts a new line character in vb.

Output
there is
newline between this and the line above.

Code
"there is a" & Chr(13) & "newline between this and the line above."

Note
Chr(13) should not be in quotes.

find a file based on date timestamp

find . -type f -newer guidedog | xargs -i mv {} going2dogs

With the above command, we are
1) looking for files (type -f) with date and timestamp newer than file guidedog.
2) then moving those files to directory going2dogs

guidedog is a dummy file which has the reference time we need. It can be created as -
touch -t 200911251340 guidedog

So all the files that were created after 1:40 PM on 11/25/2009 will move to directory doing2dogs.

Also, between two timestamps will work as

find . -type f -newer guidedog -a ! -newer anotherguidedog | xargs -i mv {} going2dogs

Update : Above command will move anotherguidedog also. To avoid that additional condition should be added before passing output to xargs:
-a ! -name anotherguidedog

changing case within vi

1)
If you have to change one or two letters, use ~ over the letters while in command mode. vi toggles the cases with ~

2) Between a range, say marks a and b

Change to lower case
'b,'a,s/[aA-zZ]/\L&/g

Change to upper case
'b,'a,s/[aA-zZ]/\U&/g

3) Change to upper case thru out the file
%s/[aA-zZ]/\U&/g

Running shell commands from within vi

Without leaving vi, we can run shell commands from within vi.

e.g. we need to insert contents of a file within our current file opened in vi.

1) Insert will begin immediately after the cursor. So we place cursor where we need the insert.
2) enter vi command mode
3) :r! cat filename

:r! can be used to insert output of a unix command also.
e.g. :r! ls
the above command inserts list of the files in the current directory (pwd)

:sh can be used to enter shell from within vi.
to return to vi, use exit.

CVS DIFF

When you need to find differences between two versions of any file in CVS, pass the current and previous version numbers of the file along with file name to CVS diff.

Example below

cvs diff -r 1.4 -r 1.5 filename.sh
Index: filename.sh
===================================================================
RCS file: /cvs/ruchi/bin/filename.sh,v
retrieving revision 1.4
retrieving revision 1.5
diff -r1.4 -r1.5
216c216
< values="2 6 7"
---
> values="1 2 3 4 5 11 15"
-----------------------------------

Note
> indicates : Modified/ added line in new version
< indicates : Code in previous version

ansi sql : between syntax

select emp_id from emptable where emp_id BETWEEN 5 and 10;

This will list
5
6
7
8
9
10

because the range on BETWEEN clause is inclusive of the test values on databases using ANSI SQL.

Error SP2-0734 - unknown command beginning

while running a query that ran fine on toad but failed on the server/sqlplus due to above error.

The solution is simple, just remove all blank lines from your script.

To delete blank lines in vi:
%g/^$/d
(i.e. global change, nothing between beginning of line to end of line, delete)

REGEXP in FIND and REPLACE in Open Office

On More Option in Find, regular expressions can be used.

e.g. to quickly replace period with period and new line.

Macros in Documents on Open Office Aqua

It's easy to record and replay macros on Open Office. I'm currently formatting a document and it involved mundane tasks like setting font format to heading3, converting a paragraph to bulleted list, etc.

Customized macros work well for these.

STEPS
1. Begin to record a macro
Tools>Macros>Record Macro
2. Perform the tasks you want recorded.
3. Click on Stop Recording. "Stop recording" looks inactive on mac but it does work.
4. #3 will open a window asking you to save the macro. Give a name.
5. Macro is now saved. Run it and see everything works as needed.
Tools>Macros>Run Macro
6. Assign this macro to a keyboard shortcut for ease in running. Tools>Macros>Organize Macros>Openoffice Basic
7. Select the macro and click edit.
8. #7 will open the macro in a new window.
9. Tools > Customize
10. Select the shortcut key you want to assign to this macro e.g. Command+J
11. From Category, select your macro's location. It will be OpenOffice org Macros > user
12. From Functions, select the macro. Click Modify.
The shortcut should appear in the Keys window.

Reduce Font in excel with a shortcut key

In MS Word one can use
ctrl + [ to reduce font size
ctrl + ] to increase font size

These do not work in MS Excel.

Following macro reduces any given font size by 2 units. Assign it to a shortcut key e.g. ctrl + p and you're set.

Sub reduceFont()
With Selection.Font
.Size = .Size - 2
End With
End Sub

Count Distinct on Multiple Columns

Some days back I figured following is a valid query. It can be used to get count of distinct occurrences of a column.

select count(distinct colname) from tablename;

But it would not take more than one column. To look up distinct records based on more than one column we can use Oracle's concatenate function - "||". Thus all the columns will be treated as a single entity.

select count(distinct colname1 || colname2 || colname3) from tablename;

A good example where we would want to use above query is to get distinct customer orders from a transaction table that contains customer-name,order-date and order-status.

Duel with Dual

Dual is a default table that comes with all Oracle db installations. It is tiny table with just one column and has just one row. See desc below.

desc dual;
DUMMY

select * from dual;
DUMMY
X

It's owner is SYS but all users have access to it. This table always returns one row. So that's something that comes in handy.

Usages:
1. mostly to select pseudo columns from tables or views
2. test any of the Oracle functions

e.g.
select to_date(sysdate) as system_date from dual;
SYSTEM_DATE
6/25/2009

select 'ruchi' as name, 'crazy' as fame from dual;
NAME FAME
ruchi crazy

select upper('ruchi') as ucase from dual;
UCASE
RUCHI

Oracle functions INSTR, SUBSTR, LENGTH, UPPER, LOWER

Usage:
length('snake')
= 5
upper('body')
= BODY
lower('roLLeR')
=roller

subset of a string is substr
substr('thisplace', 5)
= place
substr('thisplace',2,3)
= his

find in a string is instr
instr('thisplace','his')
=2
instr gives this starting position of the searched string in source string.

Toad Shortcuts

F2 - Hide data grid and show full screen editor
F4 - Run desc on the object under the cursor
F6 - Switch between editor and data grid
F7 - Clean editor
F8 - Show SQL statement history
Alt + downArrow - Display last executed SQL statement
Alt + upArrow - Brings previous recalled SQL statement

Ctrl + T or Ctrl + . - to expand Table names

Cmd + Up Arrow: Shortcut to home folder on mac os

Cmd + Down Arrow = Cmd + Enter : Open

Database User Access information

To look up what role/access you have in an Oracle database we can use this query -

select * from dba_role_privs where grantee = 'userid';

Table structure of dba_role_privs:

Column Name    ID    Data Type
GRANTEE         1    VARCHAR2 (30 Byte)  
GRANTED_ROLE    2    VARCHAR2 (30 Byte) -- Roles defined specifically for the db
ADMIN_OPTION    3    VARCHAR2 (3 Byte)  
DEFAULT_ROLE    4    VARCHAR2 (3 Byte)  

If there's no entry for the user-id in this table that means user doesn't have access to the database.

Yesterday I learned to created some basic favicons using GIMP. It was frustrating to begin with but then I figured it out -
1. start with 16x16 px
2. zoom it to upto 800%
3. reduce the brush size to minuscule ...but still it didn't work quite well. However using text works just fine.

I use CVS at work for version control. It's very easy to learn and setup. There are few stumbling blocks though for a new user.

Removing a file permananently from CVS:
rm badfile
cvs remove badfile
* if you decide not to remove the file, just do : cvs update filename cvs commit badfile

Committing without opening editor
cvs commit -m 'interesting msg' filename

The above message format can be used with other cvs commands as well that require a modification message.
e.g
cvs import -m

This tutorial is very helpful and amusing.

An excerpt
1. Two developers, A and B, check out working copies of a project at the same time. The project is at its starting point - no changes have been committed by anyone yet, so all the files are in their original, pristine state.
2. Developer A gets right to work and soon commits her first batch of changes.
3. Meanwhile, developer B watches television.
4. Developer A, hacking away like there's no tomorrow, commits her second batch of changes. Now, the repository's history contains the original files, followed by A's first batch of changes, followed by this set of changes.
5. Meanwhile, developer B plays video games.
6. Suddenly, developer C joins the project and checks out a working copy from the repository. Developer C's copy reflects A's first two sets of changes, because they were already in the repository when C checked out her copy.
7. Developer A, continuing to code as one possessed by spirits, completes and commits her third batch of changes.
8. Finally, blissfully unaware of the recent frenzy of activity, developer B decides it's time to start work. He doesn't bother to update his copy; he just commences editing files, some of which may be files that A has worked in. Shortly thereafter, developer B commits his first changes.

Using translate function on Unix in a shell script


echo "TRansLate" | tr [:lower:] [:upper:]

echo "Ok to continue? y or n"
read respons
if [ `echo ${respons} | tr [:lower:] [:upper:]` = "Y" ] ||
[ `echo ${respons} | tr [:lower:] [:upper:]` = "YES" ] ; then
echo "Processing..."
else
echo "Exiting"
exit 1
fi


On executing the script

/home/ruchi> ./test
TRANSLATE
Ok to continue? y or n
yes
Processing...

DELETE

delete till end of file
dG

some other delete commands

added:
:g/^$/d
delete all (global) lines that have nothing from start (^) to end ($) i.e. blank or empty lines

:.,$d
delete lines from current line (.) to end of file ($)
dG will do the same. It's not an editor command so should be done directly on the line.

between marks
:'p,'qd
delete lines starting from mark p to mark q, including both p and q

delete current line and lines below
4dd
delete current line and 3 below it

delete current line and lines above it
3k4dd
3k moves the cursor 3 lines up and 4 dd deletes the required 4 lines

Filter rows based on Color

Sometimes we work with excel sheets that have color coded rows and we often want to look at only a particular color at a time.

Following vba code does just that. Select any one cell of the color that you want to see. And run the macro filter_on_color. Make sure that column EZ is blank.

Sub filter_on_color()
' Select any color based on which to filter the sheet
' make sure EZ is empty

colindex = Selection.Cells.Interior.ColorIndex
col = ColumnLetter(Selection.Column)
lastrowcnt = Cells(Cells.Rows.Count, "A").End(xlUp).Row

MsgBox "Filtering on cell " & (col & Selection.Row)

For i = 1 To lastrowcnt
If Range(col & i).Cells.Interior.ColorIndex = colindex _ 
Then
Range("EZ" & i) = "Filter on color"
Else
Range("EZ" & i) = ""
End If
Next i

ActiveSheet.Select
Cells.Select
Selection.AutoFilter
Selection.AutoFilter Field:=156,  _ 
Criteria1:="Filter on color"
Range("A1").Select

End Sub

---------------------------------------------------------------------------------------

Function ColumnLetter(ColumnNumber As Integer) As String
' This function is taken from 
' http://www.freevbcode.com/ShowCode.asp?ID=4303

If ColumnNumber > 26 Then

' 1st character: Subtract 1 to map the characters to 0-25,
' but you don't have to remap back to 1-26
' after the 'Int' operation since columns
' 1-26 have no prefix letter

' 2nd character: Subtract 1 to map the characters to 0-25,
' but then must remap back to 1-26 after
' the 'Mod' operation by adding 1 back in
' (included in the '65')

ColumnLetter = Chr(Int((ColumnNumber - 1) / 26) + 64) & _
Chr(((ColumnNumber - 1) Mod 26) + 65)
Else
' Columns A-Z
ColumnLetter = Chr(ColumnNumber + 64)
End If
End Function

change cases

:%s/[a-z]/\U&/g

:%s/[A-Z]/\L&/g

sql function : NVL can be used to provide a default value when a field is null.

if salary = Null
then salary = 0
else
salary = emp_salary from emp_table
end if

This can be reduced to
select nvl(emp_salary,0) from emp_table;

---------------------------------------------------------------------------

sql function : DECODE can be used instead of a nested if condition or a case statement.

DECODE(expression or column,if_value,then_return_this_value,[ if_value,then_return_this_value,...],default_return_value)

e.g.
Following sql will assign state codes for Washington or Ohio and default to Florida if the state is neither of the two.
select emp_id, decode(emp_state,'Washington','WA','Ohio','OH','FL')
from emp_table;

Following sql will assign tax percentage as 30 if salary >=60000 else 20.
select emp_id, decode(emp_tax_pct,emp_salary >= 60000,30,20)
from emp_table;

awk world

awk executes the instructions for each line of input

$ cat testfile
1
2
3
4

$ awk '{print $1}' testfile
1
2
3
4

$ awk '{print "Hello, World"}' testfile
Hello, World
Hello, World
Hello, World
Hello, World

awk will wait for the input file name and will consider 'ENTER or RETURN' as valid input.

prefixing BEGIN keyword to any script lets awk execute the instructions without waiting for input file.

$ awk 'BEGIN {print "Hello, World"}' <== No input file
Hello, World

This instruction would be executed only once at the beginining.

$ awk 'BEGIN {print "Hello, World"}' testfile
Hello, World

$ awk 'BEGIN {print "Hello, World"} {print $1}' testfile
Hello, World
1
2
3
4


END keyword can be used to add a closing instruction.

$ awk 'BEGIN {print "Hello, World"} {print $1} END {print "Good bye"} ' testfile
Hello, World
1
2
3
4
Good bye

#

display only desired number (n) of rows in sql result

 

select * from table_name where rownum < n;

 

 

#

Available packages/ ports on MacPorts :
http://www.macports.org/ports.php?by=category&substr=archivers

To install a package using MacPorts :
sudo port install <package name>

sudo port uninstall <package name>

Caret M or ^M creeps into files imported to unix from windows/dos. It is a bad line break or carriage return. It can be seen in vim.

Caret M is a single character that can be created by pressing control+v and control+m. Writing Shift+6M would not work.

To remove caret M from the files give "%s/^M//g" on vi in escape mode.

IE6 bugs - float:left and png transparency

My html code in IE6 was not working right while Firefox worked fine. There were two issues -

 

png files were not displayed with transparent background instead a solid color.

Fixed it by adding javascript code in head. Credit to tech_support @ http://www.dynamicdrive.com/forums/showthread.php?t=22176

 

float:left with the image was not working. The image continued to be above the text rather than on left of it.

Fixed it by

1. Using "display:inline" with the text; and

2. Setting height for the div since the same image on Firefox slopped after doing #1.

 

 

#

 

while editing a file in vim, to do any of the following, get into vim command mode.

1. invoke shell using :sh. Return to vim using exit in shell.
2. see output of a vim command using :!cmd
3. insert the output of a vim command using :!rcmd
this last command is highly useful if we have to insert data from a different file into the current. it saves copy/paste efforts.

ftp to a server

ftp
open servername
username
password
cd dirname
put filepath/filename . (to copy file to server)
get anotherfilename (to get file from server)
close
! (to quit)

#

Keyboard shortcuts for Mac OSX

Control-F2 : Move focus to the menu bar

Shift-Command-H : Go Menu Home

http://support.apple.com/kb/HT1343

http://www.danrodney.com/mac/index.html

http://creativebits.org/keyboard_shortcuts_in_os_x

#

few vi commands:

Deleting from cursor to end of line
D


Selecting text for further copy/paste/delete etc

• enter visual mode per character using v or per line using V;
• use motion commands like h, j, k, l to select text;
• use the selected text to yank/delete etc;

Move to the beginning next "word delimited by space"
W (w is for any next word)


Move to the beginning of previous "word delimited by space"
B (b is for any previous word)

to search for a particular table in Oracle database:
select * from dba_objects where object_type = 'TABLE' and object_name like '%THAT_TABLE%';

Add owner name to limit query to a schema name.


to find a particular column by name:
select * from all_tab_columns where column_name like '%COLUMN_NAME%' ;

Index Sheet in Workbook

This vba code creates a new first sheet called index. Index sheet contains serial number and names with hyperlinks of the worksheets in the workbook.

Sub create_index()

' Creates a new sheet called index and 
' makes it the first sheet.
' This macro counts the number of sheet and 
' creates a hyperlink to the sheet and 
' places in index sheet

flg = 1

For Each varsheet In Worksheets
If varsheet.Name = "Index" Then
flg = 0
Exit For
End If
Next varsheet

If flg = 0 Then
MsgBox "Index Exists"
Else
MsgBox "Adding Index"
Worksheets.Add.Name = "Index"
'updated on 05/12
'Worksheets.Move before:=Worksheets(1) 
Worksheets("Index").Move before:=Worksheets(1)
sheetcnt = ActiveWorkbook.Sheets.Count

For i = 2 To sheetcnt
Sheets("Index").Select
j = i + 3
Range("E" & j) = i - 1
Range("F" & j) = Sheets(i).Name
Range("F" & j).Select
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:="'" & _
Range("F" & j).Value & "'!A1"
Next i
End If

End Sub

Fieldset

Note - If you want to test this html, replace all "< " with "<".

< html>
< body>
< fieldset>

< legend style="font-family:verdana ;font-size:30px;color:blue;align=center" >
Menu
< /legend>
< form action="">
< /br>
Bread < input type="checkbox" />
< /br>
Gravy < input type="checkbox" />
< /br>
Wine < input type="checkbox" />
< /br>
Dessert < input type="checkbox" />
< /br>
< /form>
< /fieldset>
< /body>
< /html>

Output

Link and Image

<html>
<body>
<h1>
This is heading one
</h1>
<h2>
This is heading two
</h2>
<h3>
This is heading three
</h3>
<p>
This is a paragraph
</p>
<p>
<a href="http://google.com/" >googly linky</a>
</p>
<p>
<a href="page2.htm" >Link to Page2</a>
</p>
<img src="http://www.iacuc.arizona.edu/training/cats/images/Tabby1-DomesticCat-Closeup.jpg" alt="Arizona Cat" width="100" height="100"/>
<p> This is Arizonian cat img
</p>
</body>
</html

alt in img - if the image does not load alt lets user know what is missing on the page.