Read XML Sitemap to a CSV File

You may need to process a sitemap for redirects or get a list of all your pages for some testing or monitoring process.  Here is a simple script you can run to convert your sitemap to a CSV file.  It even supports multi-file sitemaps.

Read more »

Connecting OTRS into Active Directory.

I was testing out OTRS for work and got it to connect to and integrate with out Active Directory server.  All the Customers and Agents are pulled in from AD, bassed on group membership, along with their contact information.  This took quite a bit of trial and error but here is the config file I ended up with.  Note that this isn't the full file but rather goes into the load method where indicated.

Read more »

Find Duplicate Files in the Terminal

I posted an Automator Service last week for finding duplicate photo's in an iPhoto Library.  Here is a slightly modified version of the internal script it uses. You can save this script and run it in a terminal to find duplicate file of any kind in any directory tree of your choice.  This can also be included in Automater actions itself with the Shell Script action.

findDuplicates.pl
#!/usr/bin/perl

# ##################################### #
# Filename:      findDuplicates.pl
# Author:        Jeremy Pyne
# Licence:       CC:BY/NC/SA  http://creativecommons.org/licenses/by-nc-sa/3.0/
# Last Update:   02/10/2010
# Version:       1.5
# Requires:      perl
# Description:
#   This script will look through a directory of files and find and duplicates.  It will then
#   return a list of any such duplicates it finds.  This is done by calculating the md5 checksum
#   of each file and recording it along with the filename.  Then the list is sorted by the checksum
#   and read in line by line.  Any time multiple records in a row share a checksum the file names
#   are written out to stdout.  As a result all empty files will be flagged as duplicates as well.
# ##################################### #

# Get the path from the command line.  Thos could be expanded to provide more granular control.
$dir = shift;

# Set up the location of the temp files.
$file = "/tmp/pictures.txt";
$sort = "/tmp/sorted.txt";

# Find all files in the selected directory and calculate their md5sum.  This is by far the longest step.
`find "$dir" -type file -print0 | xargs -0 md5 -r > $file`;
# Sort the resulting file by the md5sum's.
`sort $file > $sort`;

open FILE, "<$sort" or die $!;

my $newmd5;
my $newfile;
my $lastmd5;
my $lastfile;
my $lastprint = 0;

# Read each line fromt he file.
while() {
        # Extract the md5sum and the filename.
        $_ =~ /([^ ]+) (.+)/;

        $newmd5 = $1;
        $newfile = $2;

        # If this is the same checksum as the last file then flag it.
        if($1 =~ $lastmd5)
        {
                # If this is the first duplicate for this checksup then print the first file's name.
                if(!$lastprint)
                {
                        print("$lastfile\n");
                        $lastprint = 1;
                }
                # Print the conflicting file's name/
                print("$newfile\n");
        }
        else
        {
                $lastprint = 0;
        }

        # Record the last filename and checksup for future testing.
        $lastmd5 = $newmd5;
        $lastfile = $newfile;
}

close(FILE);

# Remove the temp files.
unlink($file);
unlink($sort);

Reading CSV file in Regex

Csv filws come in many shapes and forms, and its trivial to read most a csv files. But then again it can be quite headache when the csv file has a mixture of formats and control characters.

The simplest way to parse a csv file it to use a simple regexp. One might start with something as simple as this:

split(/,/, $line);

But of course this is to simple. It will split on every coma and thus not work for a line like this:

123,asd,"123, asd"

Similarly the following will work:

split(/","/, $line);
"123","asd","123, asd"

But what if we don't have control over the quoter and a mixture of both is generated? And furthermore what if the fields can contain quotes and comas themselves?


In my case I have to parse a file that is quoted as such:

005101,"LITERATURE-P/S, WARRANTY","4,345,211.0000",0.0292"70P"

To phase this the regexp is a bit more complicated:

@line = split(/,(?!(?:[^",]|[^"],[^"])+")/, $line);
for $item (@line)
{
  $item[$c] =~ s/^"(.*)"$/$1/;
}

In this case we first split the string on any coma but look ahead each time to check and see if the current coma is part of a quoted literal. If that is the case then the coma is skipped over. Next we look through each item and remove the quotation's if they are present.

This previous example is almost perfect but there is one more problem. Try the next item and it will parse wrong.

005101,"LITERATURE-P/S WARRANTY,","4,345,211.0000",0.0292,"70P"

Note the extra coma at the very end of the literals. This trips up the regexp logic. The simplest solution I found to this problem was to just add a extra space in this case and then trim it at the end.

$line =~ s/,",/, ",/g;
@line = split(/,(?!(?:[^",]|[^"],[^"])+")/, $line);
for $item (@line)
{
  $line[$c] =~ s/, $/,/;
  $item[$c] =~ s/^"(.*)"$/$1/;
}

The final script can be found below. The script included does a bit more then just phase a csv, it writes it back out as a fixed width file. This can be easily changed to just store the data in an array.

csvtofixed.pl
#!/usr/bin/perl

# Input File.
$in = shift;
# Output File.
$out = shift;
# Fixed width padding.
@size = split(/,/, shift);

if(!$in || !$out || $#size == -1)
{
  print "usage: csvtofixed input output columns\n";
  print "\tinput: Filename to read in.\n";
  print "\toutput: Filename to write out to, will overwrite.\n";
  print "\tcoumns: Field widths to pad input fields to. Example: 20,15,3,10\n";
  exit;
}

if($in =~ $out)
{
print "Aborted: Can't use same input and output file.  Please use a temparay $
exit;
}

# Open Files.
open(IN, $in) or die "Can't open input file $in";
open(OUT, ">$out") or die "Can't create output file $out";

# While there is input.
while(&th;in>)
{
# Read the next line.
$line = $_;

# Trim off the end.
$line =~ s/\r\n//;
# Fix for coma as the last char in a quote bug.
$line =~ s/,",/, ",/g;

# Split the line into its parts.
@line = split(/,(?!(?:[^",]|[^"],[^"])+")/, $line);

# For each column.
for($c=0;$c<=$#size;$c++)
{
 # Trim and quoted fields.
 $line[$c] =~ s/^"(.*)"$/$1/;
 # Remove the extra space for the coma fix.
 $line[$c] =~ s/, $/,/;

 # Print out the field.
 printf OUT "%*.*s|", $size[$c], $size[$c], $line[$c];
}

# Finish the line.
print OUT "\r\n";
}

# Close the files.
close(IN);
close(OUT);

exit;