Wednesday 16 April 2014

Excel - Colour duplicates

Doing data analysis, finding when you have repeats in a data-set is a really common task - particularly when you are exploring the data-set to get a sense for what might be there. This macro takes the record block you currently have your active cell in, and then does alternate colours across the block each time it finds a change of data in the current column. Here's an example to help you visualize. This list is sorted in the right-hand column and unsorted in the left.
As the Active Cell is in the left column, the macro looks for changes in groups there. On each change of type, the colour changes.
Now that wasn't much use - but if we sort the "type" column and apply the macro:
You can see that the groups are now visually defined by the colours.
Sub Colour_groups()
'Andrew Cave andrew.cave.blogging at gmail 2014
'**colors a spreadsheet row according to whether the PREVIOUS cell in the activecell column
'   matches it or not
'** if no match colors the cell to a different colour.
'**Gets width of colour block from rightmost cell in Row 1

Dim i As Long
Dim rg As Range
Dim c As Range, lastCell As Range
Dim rg2 As Range
Dim row As Long
Dim rowChange As Range
Dim groupCount As Long
Dim col As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim strActiveColumn As String
Dim intActiveColumn As Long
Dim darkColour As Long
Dim darkColour2 As Long
Dim lightColour As Long
Dim prevValue As Variant

Set wb = ActiveWorkbook
Set ws = wb.ActiveSheet

'51 = xlOpenXMLWorkbook (without macro's in 2007-2010, xlsx)
'52 = xlOpenXMLWorkbookMacroEnabled (with or without macro's in 2007-2010, xlsm)
'50 = xlExcel12 (Excel Binary Workbook in 2007-2010 with or without macro's, xlsb)
'56 = xlExcel8 (97-2003 format in Excel 2007-2010, xls)

col = ws.Range(IIf(wb.FileFormat = 56, "iv1", "xfd1")).End(xlToLeft).Column 'rightmost cell on the sheet - move left to find edge of data
intActiveColumn = ActiveCell.Column
'build the column letter names up

i = Int(Log(intActiveColumn) / Log(26)) 'get log base 26 (so we know how long the column name will be
'get the string name of the column (A,B...AB...IV etc)
Do
  If i > 0 Then
    strActiveColumn = strActiveColumn & Chr(intActiveColumn \ (26 ^ i) + 64)
  Else
    strActiveColumn = strActiveColumn & Chr(intActiveColumn + 64)
  End If
  intActiveColumn = intActiveColumn Mod (26 ^ i)
  i = i - 1
Loop While i >= 0
' reset values
i = 0
intActiveColumn = ActiveCell.Column
row = ws.Cells(IIf(wb.FileFormat = 56, 65536, 2 ^ 20), ActiveCell.Column).End(xlUp).row
row = ws.Cells(IIf(wb.FileFormat = 56, 65536, 2 ^ 20), ActiveCell.Column).End(xlUp).row

darkColour = 35
darkColour2 = 44
lightColour = 0

Set rg = ws.Range(strActiveColumn & "2:" & strActiveColumn & row)

Set rowChange = rg.Cells(1, 1)
For Each c In rg
    If c.Value = rowChange.Value Then 'are we in a group?
        groupCount = groupCount + 1 'count rows in group
    Else
        'and blank the current row
        If i Mod 3 = 0 Then
            Range(c.Offset(0 - groupCount, 1 - intActiveColumn), c.Offset(-1, col - intActiveColumn)).Interior.ColorIndex = darkColour
        ElseIf i Mod 3 = 1 Then
            Range(c.Offset(0 - groupCount, 1 - intActiveColumn), c.Offset(-1, col - intActiveColumn)).Interior.ColorIndex = darkColour2
        Else
            Range(c.Offset(0 - groupCount, 1 - intActiveColumn), c.Offset(-1, col - intActiveColumn)).Interior.ColorIndex = lightColour
        End If
        i = i + 1
        groupCount = 1
        Set rowChange = c ' keep a reference to the (potential) first row of the group
    End If
    Set lastCell = c
Next c
If groupCount > 1 Then 'handle the last row being part of a group
        If i Mod 3 = 0 Then
            Range(lastCell.Offset(1 - groupCount, 1 - intActiveColumn), lastCell.Offset(0, col - intActiveColumn)).Interior.ColorIndex = darkColour
        ElseIf i Mod 3 = 1 Then
            Range(lastCell.Offset(1 - groupCount, 1 - intActiveColumn), lastCell.Offset(0, col - intActiveColumn)).Interior.ColorIndex = darkColour2
        Else
            Range(lastCell.Offset(1 - groupCount, 1 - intActiveColumn), lastCell.Offset(0, col - intActiveColumn)).Interior.ColorIndex = lightColour
        End If
Else 'colour the last row if it is not in a group
    Range(Cells(lastCell.row, 1), Cells(lastCell.row, col)).Interior.ColorIndex = IIf(i Mod 3 = 0, darkColour, IIf(i Mod 3 = 1, darkColour2, lightColour))
    
End If

'handle the end of the lists -- ie if last one is a duplicate
Set ws = Nothing: Set wb = Nothing

End Sub

Wednesday 5 June 2013

Sybase - grant select to all tables in a schema

The problem is to grant a permission (select in this case) to a particular user, on all tables/views in a database.

select distinct 'grant select on ' + user_name + '.' + name + ' to username go' command from sysobjects inner join SYSUSER on SYSUSER.user_id = sysobjects.uid where type in ('U' ,'V') and user_name='database_name'

Just copy and paste the results into a command line session.

Wednesday 20 February 2013

Calculate supernets to /16 in Oracle SQL

This bit of SQL calculates all the supernet addresses (to /16) for a given IP address (and mask) in Oracle. Supernet addresses are the 'containing' IP Addresses based on powers of 2, so a /30 address covers 2(32-30) addresses (or 4 IPs). A /26 address covers 2(32-26) addresses (64 IPs). This works on Oracle 9, 10 and 11. If your version does not support regexp_replace there are some commented fields that utilise some instr calls.
select 
lvl supernet_mask, 
ip, 
regexp_substr(ip,'^\d+\.\d+\.') || resl2 || '.' || resl supernet
from
(
select 
  lvl, ip, mask, q3,q4,
  case when lvl > 24 then
    null
  else 
    0 
  end supernet
  , 
  floor(q4/(power(2,32-lvl))) * power(2,32-lvl) resl 
  , 
  CASE WHEN lvl <= 24 then 
    floor(q3/(power(2,24-lvl))) * power(2,24-lvl) 
  ELSE 
    Q3
  END resl2
from
(
select lvl, ip, mask
, to_number(regexp_substr(ip,'\d+',1)) q1
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\2')) q2
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\3')) q3
, to_number(regexp_replace(ip,'(\d{1,3})\.(\d{1,3})\.(\d{1,3})\.(\d{1,3})','\4')) q4
/* -- if your version does not support regexp_replace
, to_number(substr(ip,1,instr(ip,'.',1,1)-1)) q1
, to_number(substr(ip,instr(ip,'.',1,1)+1,instr(ip,'.',1,2 )-instr(ip,'.',1,1)-1 ))  q2
, to_number(substr(ip,instr(ip,'.',1,2)+1,instr(ip,'.',1,3 )-instr(ip,'.',1,2)-1 )) q3
, to_number(substr(ip,instr(ip,'.',1,3)+1)) q4
*/
from 
(
select 33 - level lvl,'203.63.104.108' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.12' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.124' ip,30 mask from dual connect by level <= 17 union
select 33 - level lvl,'203.63.104.144' ip,30 mask from dual connect by level <= 17 
) x
where mask >= lvl
) xx order by ip, lvl desc
) xxx

Sunday 3 February 2013

This Javascript function will filter a table according to a RegEx match with an input string. It does this by looping through each row of the table and hiding it if nothing matches. This is a pretty useful bit of code when you are presented report outputs as html tables, your user likes to chop and change conditions, and the query itself is a little slow.
//takes the tableid, and the filterstring and loops through the table
//doing a regex match using the filterString. if line matches, the <tr> is shown
// but if no match anywhere on the line, it is hidden.  
// Andrew Cave 04/02/2013
// andrew.cave.blogging@gmail.com
function filterTable(tableID,filterString){
t = document.getElementById(tableID);
if(typeof t=='undefined'){return;}//exit if the table hasn't been created yet
tbod = t.tBodies[0];
rows = tbod.rows;
rowct = rows.length;
filterString = trim(filterString);
filterString=(filterString.length==0)?'.*':filterString; //empty strings return all records
re = new RegExp(trim(filterString),"i");

for(i=0;i < rowct;i++){
cells=rows[i].cells;
cellct=cells.length;
hideme = 1; //start with hideme being true
for(j=0;j < cellct;j++){
	var td = cells[j].innerHTML;
	if(td.match(re)){ //checking for a match
		hideme=0;
		break;
	}
}
if(hideme){
	$(rows[i]).hide();
} else {
	$(rows[i]).show();
}
}
}

Thursday 10 January 2013

List of contrasting web-colors

a simple list of web-colours for backgrounds, with a contrasting foreground colour (based on RGB luminosity values):
background-colrcolor
#000000 #FFFFFF
#000080 #FFFFFF
#00008B #FFFFFF
#0000CD #FFFFFF
#0000FF #FFFFFF
#191970 #FFFFFF
#4B0082 #FFFFFF
#800000 #FFFFFF
#8B0000 #FFFFFF
#800080 #FFFFFF
#8B008B #FFFFFF
#006400 #FFFFFF
#9400D3 #FFFFFF
#2F4F4F #FFFFFF
#483D8B #FFFFFF
#008000 #FFFFFF
#B22222 #FFFFFF
#FF0000 #FFFFFF
#A52A2A #FFFFFF
#DC143C #FFFFFF
#8B4513 #FFFFFF
#C71585 #FFFFFF
#008080 #FFFFFF
#8A2BE2 #FFFFFF
#556B2F #FFFFFF
#228B22 #FFFFFF
#008B8B #FFFFFF
#9932CC #FFFFFF
#A0522D #FFFFFF
#FF1493 #FFFFFF
#FF00FF #FFFFFF
#2E8B57 #FFFFFF
#696969 #FFFFFF
#4169E1 #FFFFFF
#6A5ACD #FFFFFF
#808000 #FFFFFF
#FF4500 #FFFFFF
#4682B4 #FFFFFF
#6B8E23 #FFFFFF
#1E90FF #FFFFFF
#7B68EE #FFFFFF
#708090 #FFFFFF
#CD5C5C #FFFFFF
#808080 #FFFFFF
#D2691E #FFFFFF
#BA55D3 #FFFFFF
#20B2AA #FFFFFF
#778899 #FFFFFF
#9370D8 #FFFFFF
#B8860B #FFFFFF
#3CB371 #000000
#5F9EA0 #000000
#00BFFF #000000
#32CD32 #000000
#FF6347 #000000
#6495ED #000000
#00CED1 #000000
#D87093 #000000
#CD853F #000000
#00FF00 #000000
#DA70D6 #000000
#BC8F8F #000000
#FF69B4 #000000
#FF8C00 #000000
#FF7F50 #000000
#F08080 #000000
#FA8072 #000000
#00FA9A #000000
#00FF7F #000000
#DAA520 #000000
#48D1CC #000000
#A9A9A9 #000000
#8FBC8F #000000
#66CDAA #000000
#E9967A #000000
#9ACD32 #000000
#FFA500 #000000
#EE82EE #000000
#40E0D0 #000000
#BDB76B #000000
#00FFFF #000000
#F4A460 #000000
#FFA07A #000000
#DDA0DD #000000
#D2B48C #000000
#7CFC00 #000000
#87CEEB #000000
#7FFF00 #000000
#87CEFA #000000
#DEB887 #000000
#C0C0C0 #000000
#B0C4DE #000000
#90EE90 #000000
#D8BFD8 #000000
#FFD700 #000000
#ADD8E6 #000000
#FFB6C1 #000000
#ADFF2F #000000
#B0E0E6 #000000
#98FB98 #000000
#D3D3D3 #000000
#7FFFD4 #000000
#FFC0CB #000000
#AFEEEE #000000
#DCDCDC #000000
#F0E68C #000000
#F5DEB3 #000000
#FFDAB9 #000000
#FFDEAD #000000
#EEE8AA #000000
#FFFF00 #000000
#FFE4B5 #000000
#E6E6FA #000000
#FFE4C4 #000000
#FFE4E1 #000000
#FAEBD7 #000000
#FFEBCD #000000
#FFEFD5 #000000
#FAF0E6 #000000
#F5F5DC #000000
#FFF0F5 #000000
#F5F5F5 #000000
#FDF5E6 #000000
#E0FFFF #000000
#FAFAD2 #000000
#F0F8FF #000000
#FFF8DC #000000
#FFFACD #000000
#FFF5EE #000000
#F8F8FF #000000
#F0FFF0 #000000
#FFFAF0 #000000
#F0FFFF #000000
#F5FFFA #000000
#FFFFE0 #000000
#FFFAFA #000000
#FFFFF0 #000000
#FFFFFF #000000

Wednesday 29 August 2012

array of file handles in Perl

I used the following code today which creates, prints to and closes an array of file-handles using the IO::File core module. I had over 100K of files in the same directory and had to process them (and send them into their own directory too, but that is trivial). This works on Perl 5.14...
#==========================
# Andrew Cave
# 29 aug 2012
# 
#==========================

use IO::File;
#create a blank hash of day values
my %month=( 
'20120712' => '','20120713' => '','20120714' => '',
'20120715' => '','20120716' => '','20120717' => '',
'20120718' => '','20120719' => '','20120720' => '',
'20120721' => '','20120722' => '','20120723' => '',
'20120724' => '','20120725' => '','20120726' => '',
'20120727' => '','20120728' => '','20120729' => '',
'20120730' => '','20120731' => '','20120801' => '',
'20120802' => '','20120803' => '','20120804' => '',
'20120805' => '','20120806' => '','20120807' => '',
'20120808' => '','20120809' => '','20120810' => '',
'20120811' => '','20120812' => '','20120813' => '',
'20120814' => '','20120815' => '','20120816' => '',
'20120817' => '','20120818' => '','20120819' => '',
'20120820' => '','20120821' => '','20120822' => '',
'20120823' => '','20120824' => '','20120825' => '',
'20120826' => '','20120827' => '','20120828' => '' 
);
my ($key,$val) ;
#open a filename based on the array values and
#put the file-handle reference in the hash, using the dayvalue 
#as the key

while (  ($key,$val) = each(%month)) {
 my $filename = "output_$key.dat";
 my $fh = IO::File->new("> $filename") 
      or die "failed to create $filename $@\n$!\n";
 $month{$key} = $fh;
};

open IN , '<', 'input.dat';
while(){
 chomp;
 my $line = $_;
 if(/\d{8}/){# if the line has an eight digit number
   my $day = $1; 
   my $fh = $month{$day}; # get the correct filehandle
   print {$fh} "$line\n" ; #print to the filehandle - note the braces
                #move the file into its own directory
                #and add '.done' to the name
  if(! -d "./$day/"){
   system("mkdir ./$day/");
  }
  #print "$file|./$day/\n"; # the mv statements (if we want a record)
  system("mv $file ./$day/$file.done");

 }

}
close IN;

#close the array of files we created
# using IO::File
while (($key,$val)=each(%month)){
 my $close_fh =  $month{$key};
 $close_fh->close();
}

Wednesday 28 March 2012

Create a Calendar listing in pure Oracle SQL

Just fiddling around...create a calendar for the next 1000 days starting with the 1st day of the current year. The calendar is the standard format putting Sundays in the left-most column a la :

2012 APR SUN MON TUE WED THU FRI SAT
2012 APR 1 2 3 4 5 6 7
2012 APR 8 9 10 11 12 13 14
2012 APR 15 16 17 18 19 20 21
2012 APR 22 23 24 25 26 27 28
2012 APR 29 30
2012 AUG SUN MON TUE WED THU FRI SAT
2012 AUG 1 2 3 4
2012 AUG 5 6 7 8 9 10 11
2012 AUG 12 13 14 15 16 17 18
2012 AUG 19 20 21 22 23 24 25
2012 AUG 26 27 28 29 30 31
2012 DEC SUN MON TUE WED THU FRI SAT
2012 DEC 1
2012 DEC 2 3 4 5 6 7 8
2012 DEC 9 10 11 12 13 14 15
2012 DEC 16 17 18 19 20 21 22
2012 DEC 23 24 25 26 27 28 29
2012 DEC 30 31




/*generate a calendar in Oracle using pure SQL
-- Andrew Cave andrew.cave.blogging at gmail-- 29/3/2012*/
with x as (select -- generate the days
  trunc(trunc(sysdate,'YEAR')+level-1) date_ -- add days to 1st day of current year
  from dual connect by level <= 1000 -- number of days to go forward
) 
select yr,monthname,SUN,MON,TUE,WED,THU,FRI,SAT from 
(
select yr
,monthname,MON_NUM,row_
,SUN,MON,TUE,WED,THU,FRI,SAT
from (
select -- display the dates
yr
,monthname,MON_NUM,row_
, to_char(max(decode(dayname,'SUN',dom,null))) SUN
, to_char(max(decode(dayname,'MON',dom,null))) MON
, to_char(max(decode(dayname,'TUE',dom,null))) TUE
, to_char(max(decode(dayname,'WED',dom,null))) WED
, to_char(max(decode(dayname,'THU',dom,null))) THU
, to_char(max(decode(dayname,'FRI',dom,null))) FRI
, to_char(max(decode(dayname,'SAT',dom,null))) SAT
from (
select 
yr
,mon monthname
,dayname
,dom
,MON_NUM
,trunc((dom+ fdom-1)/7)-trunc(fdom/7)  row_ -- what week of the month
,fdom 
from 
  (
  select date_ -- get date values for calculations and display
    , to_char(date_,'DY') dayname
    , to_char(date_,'YYYY') yr  
    , to_char(date_,'MON') MON
    , to_char(date_,'MM') MON_NUM
    , to_number(to_char(date_,'DD')) dom
    , to_number(to_char(trunc(date_,'MM'),'D')) fdom -- day value of 1st day of month
  from x
  ) x1
) x2 group by yr,monthname,row_,MON_NUM
) x3 
union
select distinct -- this select does the day name column headers for the calendar
  to_char(date_,'YYYY') yr
  , to_char(date_,'MON') MONthname
  , to_char(date_,'MM') MON_NUM
  , -1 row_
  ,'SUN','MON','TUE','WED','THU','FRI','SAT'
from x
) x4  order by yr, MON_NUM, row_