Jokes apart, you first need to establish if your valid characters and your possible delimiters are disjointed sets. If one file can use ";" as a separator, while it's a perfectly valid char inside the fields of another... it's something that I would not automate.

Otherwise, if these sets do not intersect (e.g. you only have digits as values inside fields, and possible separators are ";,|:"), you can simply transform each separator character into a comma:

   while (<>) {
      tr/;:|/,/;
      print;
   }
[download]

Update: removed spurious line from example.

BERTIE: What should we call this one, Jeeves?
JEEVES: C, sir?
BERTIE: Well, I suppose "Caeser" is as good a name as any.

If anyone can come up with a solution to this, I'd like to shake that person's hand ... or rather, at least know who did it and what their reasoning was.

(BTW, I apologize for the injection of P.G. Wodehouse into the middle of this, but that is a weakness of mine. Please forgive me.)

Try parsing the file 256 times — once using each ASCII character as the field delimiter — and collect statistics on the number of columns that result. The delimiter character that yields the most uniform number of fields per line is the likely one. (Yes, I've actually done this. Works pretty well sometimes.)

The best place to start is your manager. If the person giving you the task has not provided the information you need to complete said task, they need to be made aware of it ASAP.

I totally agree with you, but in this business that request will go unanswered. I was handed a project with a 48 hour TAT and the hard drive of the customers data. The hard drive consisted of 251 GB of data files. Some of these files are considered metadata files which are the delimited files I was describing earlier. With this much data to work there is no way to go through it manually to figure out the delimiters. This is actually somewhat humorous, I said to my manager that this project is nearly void of any meaningful details. His reponse was " You have to make your own details" Nice!

Your solution

---

• In general, if you think something isn't in Perl, try it out, because it usually is. :-)
• "What is the sound of Perl? Is it not the sound of a wall that people have stopped banging their heads against?"

my @delim = (",", ";", ":", "\t");
my $line = "This;is;delimited;with;a;semicolon,;and;there;is;a;comma";

for ( @delim )
{
    print "possibly delimited with $_\n"
        if $line =~ /([^$_]+$_){2,}/;
        
}
# prints "possibly delimited with ;"
[download]

Without knowing more about the structure of your files and/or the range of possible delimiters, this question is impossible to answer in any meaningful way.

The files are flat text files with anywhere from 3 to 50 or fields. The range of delimiters that I have seen thus far include - _ * & ^ % $ # @ ! ~ ` < > . : ; € œ þ

If you can assume the same number of fields in each line, you can you can try counting each possible delimiter for the first 5 lines or so, and seeing which returns a reasonable result.

This is a little rough, and could use better error checking, but something along these lines.

Run this in the directory containg the csv files. It assumes file extentions of .csv and saves the "corrected" files as filename.csv.new. Modify to suit.

Update: edited script slightly to remove useless use of array.

###############################################
use warnings;
use strict;

my @delimiters = ('_', '*', '&', '^', '%', '$', '#', '@', '!', '~', '`
+', '<', '>', '.', ':', ';', '€', 'œ', 'þ', ',');

my @files = glob('*.csv'); # or whatever

my %likely;

for my $file(@files){
    open my $fh, '<', $file or warn "Couldn't open $file. $!";
    my %delim_count;
    for my $count (1..5){
        my $line = <$fh>;
        for (@delimiters){
            my $testline = $line;
            $delim_count{$_}{total} +=  $testline =~ s/\Q$_\E//g;
        }
    }
    for (@delimiters){
        if (defined $delim_count{$_}{total} and ($delim_count{$_}{tota
+l}) > 2 and ($delim_count{$_}{total}/5 == int($delim_count{$_}{total}
+/5))){
            no warnings 'uninitialized';
            $likely{$file} = $_ if ($delim_count{$_}{total} > $delim_c
+ount{$likely{$file}}{total});
        }
    }
    print "Most likely delimiter for $file is $likely{$file}\n"
}

for my $file (keys %likely){
    if (defined $likely{$file}){
        print "Updating $file....\n";
        next if ($likely{$file} eq ',');
        my ($csv,$output);
        unless (open $csv, '<', $file){
            warn "Couldn't open $file. $!";
            next;
        }
        unless (open $output, '>', "$file.new"){
            warn "Couldn't open $file.new for writing. $!";
            next;
        }
        while (<$csv>){
            s/\Q$likely{$file}\E/,/g;
            print $output $_;
        }
    }else{
        print "Ambiguous delimiter for file $file\n";
    }
}
[download]

You can try and split the first 5 lines of a given file on each of the different known deliminators. For each deliminator tested, if the 5 lines you test return the same length array (greater than 1, as that is the default case if there was nothing to split on), then that deliminator is likely to be the correct one.

While this isn't 100% guaranteeed, many of the files should process correctly this way, and then you may only have a few files to work by hand after verifying yourself what the true deliminator is.

I hope you're not saying that the delimiter can change from record to record within a file. That would make it an even dodgier proposition.

Making a few assumptions about the nature of your "delimited" files:

#! perl -slw
use strict;

my %charsByLine;
my %freqCharsPerLine;
my %chars;
while( <> ) {
    chomp;
    for( split '', $_ ) {
        $chars{ $_ } = 1;
        push @{ $charsByLine{ $_ } }, $.;
        $freqCharsPerLine{ $. }{ $_ }++ ;
    }
}
my $last = $.;

## Eliminate chars that do not appear in every line
@{ $charsByLine{ $_ } } != $last and delete $chars{ $_ } for keys %cha
+rs;

## Eliminate chars where they appear a different number of time per li
+ne
for my $char ( keys %chars ) {
    my $previousCount = $freqCharsPerLine{ 1 }{ $char };
    for my $line ( 2 .. $last ) {
        if( $freqCharsPerLine{ $line }{ $char } != $previousCount ) {
            delete $chars{ $char };
            last;
        }
    }
}

if( keys %chars == 1 ) {
    print "The delimiter for this file is: ", keys %chars;
}
elsif( keys %chars ) {
    print "Candidate delimiters for this file are: ", keys %chars;
}
else {
    print "Unable to determine a likely candidate for this file!";
}
[download]

Here is a possible idea that could work (I'll leave it up to you to implement it)

Assumption: one record is stored on each line (or you know exactly how many lines define one record).

Idea: Create a hash which represents the charachters and the numbers of lines they appear on. (Which means: read the file record per record, and add 1 to the correct hash-element for each unique charachter).

Now look for the charachters that occurs on every line. (meaning loop the hash and look for the elements where the value equals the total number of lines (after excluding alphanumeric charachters etc))

Now when you are done with that, then you might or might not be there yet. As in, what if there are two charachters that appear on every line? A solution for this could be that you try each charachter as a delimiter, and count how many fields you get... if there is a big difference in the number of fields then there is a big chance you used the wrong delmiter (except ofcourse when the data is really strange)