Eikonal Blog

2012.02.07

Excel sortIP macro

Filed under: transformers — Tags: , , — sandokan65 @ 14:39

Found this somewhere on web several months ago. Very useful for long lists of machines that one want to order by IP addresses.

    Option Explicit
    Sub sortIP() 'sorts IP addresses
    Dim i As Long, j As Long, k As Long
    Dim IP
    Dim rg()
    Dim RangeToSort As Range
    Dim IPaddress As String
    Dim IPColumn As Long
    
    IPaddress = "#*.#*.#*.#*"
    
    Set RangeToSort = Selection
    
    'If just one cell selected, then expand to current region
    If RangeToSort.Count = 1 Then
    Set RangeToSort = RangeToSort.CurrentRegion
    End If
    
    'Check if row 1 contains an IP address. If not, it is a header row
    
    'first find column with IP addresses. Check row 2 since row 1 might be a Header
    IPColumn = 1
    Do Until RangeToSort.Cells(2, IPColumn).Text Like IPaddress
    If IPColumn > RangeToSort.Columns.Count Then
    MsgBox ("No valid IP address found in Row 1 or Row 2")
    Exit Sub
    End If
    IPColumn = IPColumn + 1
    Loop
    
    If Not RangeToSort(1, IPColumn).Text Like IPaddress Then
    Set RangeToSort = RangeToSort.Offset(1, 0). _
    Resize(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
    End If
    
    
    
    'one extra column for the IP sort order
    ReDim rg(RangeToSort.Rows.Count - 1, RangeToSort.Columns.Count)
    
    
    
    For i = 0 To UBound(rg)
    For k = 1 To UBound(rg, 2)
    rg(i, k) = RangeToSort.Cells(i + 1, k).Text
    Next k
    IP = Split(rg(i, IPColumn), ".")
    For j = 0 To 3
    rg(i, 0) = rg(i, 0) & Right("000" & IP(j), 3)
    Next j
    
    Next i
    
    rg = BubbleSort(rg, 0)
    
    For i = 0 To UBound(rg)
    For k = 1 To UBound(rg, 2)
    RangeToSort.Cells(i + 1, k) = rg(i, k)
    Next k
    Next i
    
    End Sub
    '-------------------------------------------
    Function BubbleSort(TempArray As Variant, d As Long) 'D is dimension to sort on
    Dim temp() As Variant
    Dim i As Integer, j As Integer, k As Integer
    Dim NoExchanges As Boolean
    
    k = UBound(TempArray, 2)
    ReDim temp(0, k)
    
    Do
    NoExchanges = True
    
    For i = 0 To UBound(TempArray) - 1
    If TempArray(i, d) > TempArray(i + 1, d) Then
    NoExchanges = False
    For j = 0 To k
    temp(0, j) = TempArray(i, j)
    TempArray(i, j) = TempArray(i + 1, j)
    TempArray(i + 1, j) = temp(0, j)
    Next j
    End If
    Next i
    Loop While Not NoExchanges
    
    BubbleSort = TempArray
    
    End Function
    


    Related here: Excel to text – https://eikonal.wordpress.com/2011/02/14/excel-to-text/ | Excel files processing – https://eikonal.wordpress.com/2011/02/25/excel-files-processing/ | IT tips pages – https://eikonal.wordpress.com/2010/02/08/it-tips-pages/

2011.08.17

eBooks and eBook Format Transformers

Sites


Articles


Devices and other readers

  • Amazon’s Kindle
  • barnes and Noble’s Nook
  • FBReader — e-book reader for Unix/Windows computers – http://www.fbreader.org/

eBook format transformers

Kindle blogs

PDF

2011.02.25

Excel files processing

Filed under: perl, scripting, transformers — Tags: — sandokan65 @ 23:55

Related here: Excel sortIP macro – https://eikonal.wordpress.com/2012/02/07/excel-sortip-macro/ | Excel to text – https://eikonal.wordpress.com/2011/02/14/excel-to-text/ | IT tips pages – https://eikonal.wordpress.com/2010/02/08/it-tips-pages/

2011.02.14

Excel to text

Filed under: perl, scripting, tools, transformers — Tags: , — sandokan65 @ 14:11
  • excel2txt (by Ken Youens-Clark) – http://search.cpan.org/~kclark/excel2txt/excel2txt [Perl] – convert Excel data to delimited text files
    • Example use:

      > excel2txt PasswdFiles.xls
      Processing PasswdFiles.xls
      Writing 'passwdfiles-fiapp1.txt'
      Writing 'passwdfiles-fiapp2.txt'
      Writing 'passwdfiles-fiapp3.txt'
      Writing 'passwdfiles-fiapp4.txt'
      Writing 'passwdfiles-fiapp6.txt'
      Writing 'passwdfiles-fiapp7.txt'
      Writing 'passwdfiles-fiapp8.txt'
      Writing 'passwdfiles-fiapp9.txt'
      Writing 'passwdfiles-fidb1.txt'
      Writing 'passwdfiles-fidb2.txt'
      Writing 'passwdfiles-fidb3.txt'
      Writing 'passwdfiles-fidb4.txt'
      Writing 'passwdfiles-fiweb1.txt'
      Writing 'passwdfiles-fiweb2.txt'
      Writing 'passwdfiles-fiweb3.txt'
      Writing 'passwdfiles-fiweb4.txt'
      Done, processed 1 Excel file, created 16 data files.
      

Related here: Excel sortIP macro – https://eikonal.wordpress.com/2012/02/07/excel-sortip-macro/ | Excel files processing – https://eikonal.wordpress.com/2011/02/25/excel-files-processing/ | IT tips pages – https://eikonal.wordpress.com/2010/02/08/it-tips-pages/

2010.10.05

sed tricks

Filed under: scripting, transformers — Tags: , , — sandokan65 @ 15:58

These one-liners are collected from various sites and articles on web – see the list of Sources at the bottom of this posting.

  • Deleting all empty lines from the input file:
    sed ‘/^$/d’ 
  • In-place replacement:
    sed –i ‘/^$/d’ INPUTFILE
  • In-place replacement with backup of original file:
    sed –ibak ‘/^$/d’ INPUTFILE
  • In-place deletion of all occurences of a string in a file:
    sed –i ‘/WORDTOBEDELETED/d’
  • How to replace the first occurrence only (of a string match) in a file, using sed
    sed '0,/THISSTRING/s//TOTHATSTRING/' INPUTFILE
  • Append environment variable PATH with sed:
    sed -e '/^PATH/s/"$/:\/usr\/lib\/myprog\/bin"/g' -i /etc/environment
  • Remove all whitespace from beinning of lines:
    sed 's/^[ \t]*//g' foo
  • Deleting the / from all html files contained in current folder:
    sed -i ‘s/src=”\//src=”/g’ *.html
  • Greedy matching:
    % echo "foobar" | sed 's///g'
    bar
    
  • Non greedy matching:
    % echo "foobar" | sed 's/]*>//g'
    foobar
    

Sources:

References


Related here: Command line based text replace – https://eikonal.wordpress.com/2010/07/13/command-line-based-text-replace/.

Related here: Scripting languages – https://eikonal.wordpress.com/2010/06/15/awk-sed/ | Unix tricks – https://eikonal.wordpress.com/2011/02/15/unix-tricks/ | SED tricks – https://eikonal.wordpress.com/2010/10/05/sed-tricks/ | Memory of things disappearing > nmap stuff > getports.awk – https://eikonal.wordpress.com/2010/06/23/memory-of-things-disappearing-nmap-stuff-getports-awk/ | AWK – https://eikonal.wordpress.com/2011/09/30/awk/

Transformers

Filed under: transformers — Tags: , , , , , — sandokan65 @ 14:49

DJVU to PDF

Mail processing tools

CSV to/from LDIF

Finding difference of two MS Word documents

2010.07.13

Command line based text replace

sed

  • sed 's/Mark Monre/Marc Monroe/' 1.txt > 2.txt
  • find ./* -type f -exec sed -i 's///g' {} \;

The “replace” command

  • Syntax:
    replace OLD-STRING NEW-STRING OUTPUT-FILE
  • Example:
    $ replace UNIX Linux  newfile
  • Example:
    $ cat /etc/passwd | replace : '|'
  • Partial support for regular expressions: \^ – matches start of line, and $ matches end of line.
  • Example: replace all IP address 192.168.1.2 start of line:
    $ replace \^192.168.1.2 192.168.5.10  newfile
  • a bash script, ‘fixer.sh’
    #!/bin/bash
    replace CHANGEFROM CHANGETO $1.tmp
    rm $1
    mv $1.tmp $1
    

    now run this command line:

    $ grep CHANGEFROM |cut -d':' -f1 |xargs -n 1 fixer.sh

    the results is that all files in the directory (or whatever you grep for) will be changed automagically.
    just make sure the grep doesn’t include the fixer script itself, or it will die half-way through changing when execute permissions are reset!


Perl


Sources:


Related: Regular expressions – https://eikonal.wordpress.com/2010/04/02/regular-expressions/ | Perl online – https://eikonal.wordpress.com/2010/02/15/perl-online/

Create a free website or blog at WordPress.com.