Eikonal Blog


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/


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/


Command line based text replace


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

The “replace” command

  • Syntax:
  • 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 start of line:
    $ replace \^  newfile
  • a bash script, ‘fixer.sh’
    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!



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


Perl online




Control structures


Rename files

Alex Batko says (at http://www.cs.mcgill.ca/~abatko/computers/programming/perl/):

Here is a brilliant program for renaming one or more files according to a specified Perl expression. I found it on page 706 of Programming Perl (3rd edition).

$op = shift;
for( @ARGV ) {
    $was = $_;
    eval $op;
    die if $@;
    rename( $was, $_ ) unless $was eq $_;

In the code above, the second last line calls the built-in function “rename”, not the program itself (which is named “rename.pl”). Below are a few examples of use.

% rename.pl 's/\.htm/\.html/' *.htm         # append an 'l'
% rename.pl '$_ .= ".old"' *.html           # append '.old'
% rename.pl 'tr/A-Z/a-z/' *.HTML            # lowercase
% rename.pl 'y/A-Z/a-z/ unless /^Make/' *   # lowercase

Printing hashes

Starting with an input file with data in two columns separated by coma (,):

#/bin/perl -t

my %TempHash = ();
my $InputFile = shift;
print "Input file = ",$InputFile,"\n";

my ($line,$column1,$column2,);

#reading input file to generate hash
open (INPUTSTREAM, '<',  $InputFile) || die ("Could not open $InputFile");
while ( $line =  ) {
        #print $line;
	($column1, $column2) = split ',', $line;
        #print $column1," ==> ",$TempHash{$column1};

## printing hash - way #1
print "The following are in the DB: ",join(', ',values %TempHash),"\n";

## printing hash - way #2
while (($key, $value) = each %TempHash)
     print "$key ==> $value";

## printing hash - way #3
foreach $key (sort keys %TempHash){
   print "$key ==> $TempHash{$key}";

Removing white spaces


# Declare the subroutines
sub trim($);
sub ltrim($);
sub rtrim($);

# Perl trim function to remove whitespace from the start and end of the string
sub trim($)
	my $string = shift;
	$string =~ s/^\s+//;
	$string =~ s/\s+$//;
	return $string;
# Left trim function to remove leading whitespace
sub ltrim($)
	my $string = shift;
	$string =~ s/^\s+//;
	return $string;
# Right trim function to remove trailing whitespace
sub rtrim($)
	my $string = shift;
	$string =~ s/\s+$//;
	return $string;

# Here is how to output the trimmed text "Hello world!"
print trim($string)."\n";
print ltrim($string)."\n";
print rtrim($string)."\n";

Related: Regular Expressions – https://eikonal.wordpress.com/2010/04/02/regular-expressions/ | Command line based text replace – https://eikonal.wordpress.com/2010/07/13/command-line-based-text-replace/

Blog at WordPress.com.