Take the 2-minute tour ×
Unix & Linux Stack Exchange is a question and answer site for users of Linux, FreeBSD and other Un*x-like operating systems.. It's 100% free, no registration required.

INPUT (unsorted, bad)

"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-44432"
,"XYZ-ZTE-43255"
"XYZ-ZTE-52775 serverB110 agreed",
"XYZ-ZTE-79213 - serverB688 agreed",
"XYZ-ZTE-77323 serverB617 agreed",
"XYZ-ZTE-81422 - serverB609 agreed","XYZ-ZTE-77323"
"XYZ-ZTE-32785 - serverA626 agreed","XYZ-ZTE-52775"
"XYZ-ZTE-43235 - serverA605 disagreed (asdfjlasdj yxvv il lkyeas sadfa)","XYZ-ZTE-43235"
"XYZ-ZTE-11591 serverB144 agreed",
,"XYZ-ZTE-11591"

OUTPUT (sorted, the needed thing, the good one!)

"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-43255"
,"XYZ-ZTE-44432"
"XYZ-ZTE-52775 serverB110 agreed","XYZ-ZTE-52775"
"XYZ-ZTE-79213 - serverB688 agreed",
"XYZ-ZTE-77323 serverB617 agreed","XYZ-ZTE-77323"
"XYZ-ZTE-81422 - serverB609 agreed",
"XYZ-ZTE-32785 - serverA626 agreed",
"XYZ-ZTE-43235 - serverA605 disagreed (asdfjlasdj yxvv il lkyeas sadfa)","XYZ-ZTE-43235"
"XYZ-ZTE-11591 serverB144 agreed","XYZ-ZTE-11591"

So in short: this is a snippet from an XLS with two columns. The two columns must be sorted together in a way that e.g.:

XYZ-ZTE-77323 in the left column matches the XYZ-ZTE-77323 on the right column

But there are things like:

,"XYZ-ZTE-43255"

which have to go with the line:

"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-43255"

but the line (in the INPUT) already contains a right column:

"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-44432"

and e.g. if:

"XYZ-ZTE-44432"

doesn't exists in the left column, then it must be in a new row in the OUTPUT

Does anybody has an idea how to do this?

share|improve this question
1  
awk. –  Seamus Jun 17 '11 at 17:54
    
You say OUTPUT (sorted.., but you show it as effectively the same order as the input data... I wrote a python script to sort it :( .. oh well, I should have checked more carefully; I guess you meant (colloquially) "sorted-out"... (fun doing it, though :) –  Peter.O Jun 29 '11 at 1:02
    
I've modified the python script and have now posted it as an answer... –  Peter.O Jun 29 '11 at 4:32
add comment

3 Answers 3

I tried this program, not the best one (it parses twice one file and has some duplicated code) but feel free to adapt it to your needs. I think it does the work.

$ cat script.pl
use warnings;
use strict;
use Text::CSV_XS;

my (%col1, %col2);

my $csv = Text::CSV_XS->new(
        { empty_is_undef => 1 }
) or die "Error: " . Text::CSV_XS->error_diag();

chomp( my @data = <STDIN> );

## Read file and save first column in %col1 hash and second
## column in %col2 hash.
foreach my $line ( @data ) {
    die "Error in parse of CSV file\n" unless $csv->parse( $line );
    my @columns = $csv->fields();
    $col1{ $columns[0] }++ if defined $columns[0];
    $col2{ $columns[1] }++ if defined $columns[1];
}

LINE:
foreach my $line ( @data ) {
        die "Error in parse of CSV file\n" unless $csv->parse( $line );
        my @columns = $csv->fields();

        ## Discard line if both columns are undefined.
        next if !defined $columns[0] && !defined $columns[1];

        ## 1.- Undefined first column: Save second column in hash.
        do { $col2{ $columns[1] } = 1; next } unless defined $columns[0];

        ## 2.- Both columns are defined: Sort them.
        if ( defined $columns[0] && defined $columns[1] ) {
                if ( index( $columns[0], $columns[1] ) > -1 ) {
                        # Line is sorted, print it.
                        print quote($columns[0]), ",", quote($columns[1]), "\n";
                        delete $col2{ $columns[1] };
                } else {
                        # Line unsorted, search its equivalent in hash of second column
                        # and print.
                        my $key = $1 if $columns[0] =~ /^(\S*)/;
                        print quote($columns[0]), ",", ( exists $col2{ $key } ? quote($key) : "" ), "\n";
                        delete $col2{ $key } if exists $col2{ $key };
                        # Here, the second unsorted column, search its equivalent in first
                        # column. If not found print it now, else it will be printed later.
                        for my $str ( keys %col1 ) {
                                next LINE if index( $str, $columns[1] ) > -1;
                        }
                        print ",", quote($columns[1]), "\n";
                }
                next;
        }

        ## 3.- Undefined second column: Check if second column is saved in
        ## hash and join it with first column.
        unless ( defined $columns[1] ) {
                my $key = $1 if $columns[0] =~ /^(\S*)/;
                print quote($columns[0]), ",", ( exists $col2{ $key } ? quote($key) : "" ), "\n";
                delete $col2{ $key } if exists $col2{ $key };
        }
}

sub quote {
        my ($str) = $_[0];
        $str =~ s/^(.*)$/"$1"/;
        return $str;
}

Your data file:

"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-44432"
,"XYZ-ZTE-43255"
"XYZ-ZTE-52775 serverB110 agreed",
"XYZ-ZTE-79213 - serverB688 agreed",
"XYZ-ZTE-77323 serverB617 agreed",
"XYZ-ZTE-81422 - serverB609 agreed","XYZ-ZTE-77323"
"XYZ-ZTE-32785 - serverA626 agreed","XYZ-ZTE-52775"
"XYZ-ZTE-43235 - serverA605 disagreed (asdfjlasdj yxvv il lkyeas sadfa)","XYZ-ZTE-43235"
"XYZ-ZTE-11591 serverB144 agreed",
,"XYZ-ZTE-11591"

The the results:

$ perl script.pl <yourdatafile
"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-43255"
,"XYZ-ZTE-44432"
"XYZ-ZTE-52775 serverB110 agreed","XYZ-ZTE-52775"
"XYZ-ZTE-79213 - serverB688 agreed",
"XYZ-ZTE-77323 serverB617 agreed","XYZ-ZTE-77323"
"XYZ-ZTE-81422 - serverB609 agreed",
"XYZ-ZTE-32785 - serverA626 agreed",
"XYZ-ZTE-43235 - serverA605 disagreed (asdfjlasdj yxvv il lkyeas sadfa)","XYZ-ZTE-43235"
"XYZ-ZTE-11591 serverB144 agreed","XYZ-ZTE-11591"
share|improve this answer
add comment

Stuff your data into a database, then use joins to retrieve it based on any criteria you like. SQLite is a good light weight system with no server component that is great for manipulating small data sets in this way.

share|improve this answer
add comment

This produces your expected output, using python. It is a bit hackish because I don't know much Pyton, but it works :)

#!/bin/bash

python -c '
import sys, csv;
reader = csv.reader(sys.stdin)
nbli=0 # line number (to maintin input order)
nbwi=6 # width of line number.. (zero padded) 
left=[] 
rght=[] 
for row in reader:
    nbli+=1
    fnum=format(nbli, "0"+str(nbwi)+"d")
    if row[0] != "": left.append(row[0]+fnum) 
    if row[1] != "": rght.append(row[1]+fnum)
left.sort()
rght.sort()
coll = []
l ,r = 0, 0
while l < len(left) or r < len(rght):
    #
    if l >= len(left):
        numr = rght[r][-nbwi:]
        datr = rght[r][:len(rght[r])-nbwi]
        coll.append(numr + " " + ",\"" + datr + "\"")
        r+=1
    elif r >= len(rght):
        numl = left[l][-nbwi:]
        datl = left[l][:len(left[l])-nbwi]
        coll.append(numl + " " + "\"" + datl + "\",")
        l+=1
    else:
        numl = left[l][-nbwi:]
        numr = rght[r][-nbwi:]
        datl = left[l][:len(left[l])-nbwi]
        datr = rght[r][:len(rght[r])-nbwi]
        #
        if datl.startswith(datr+" "):
            coll.append(numl + " " + "\"" + datl + "\",\"" + datr + "\"")
            l+=1
            r+=1
        elif datl < datr + " ":
            coll.append(numl + " " + "\"" + datl + "\",")
            l+=1
        else:
            coll.append(numr + " " + ",\"" + datr + "\"")
            r+=1

coll.sort()
c = 0
while c < len(coll):
    print coll[c][nbwi+1:]
    c+=1
' \
<<-'STDIN'
"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-44432"
,"XYZ-ZTE-43255"
"XYZ-ZTE-52775 serverB110 agreed",
"XYZ-ZTE-79213 - serverB688 agreed",
"XYZ-ZTE-77323 serverB617 agreed",
"XYZ-ZTE-81422 - serverB609 agreed","XYZ-ZTE-77323"
"XYZ-ZTE-32785 - serverA626 agreed","XYZ-ZTE-52775"
"XYZ-ZTE-43235 - serverA605 disagreed (asdfjlasdj yxvv il lkyeas sadfa)","XYZ-ZTE-43235"
"XYZ-ZTE-11591 serverB144 agreed",
,"XYZ-ZTE-11591"
STDIN

Output:

"XYZ-ZTE-43255 serverB618 agreed","XYZ-ZTE-43255"
,"XYZ-ZTE-44432"
"XYZ-ZTE-52775 serverB110 agreed","XYZ-ZTE-52775"
"XYZ-ZTE-79213 - serverB688 agreed",
"XYZ-ZTE-77323 serverB617 agreed","XYZ-ZTE-77323"
"XYZ-ZTE-81422 - serverB609 agreed",
"XYZ-ZTE-32785 - serverA626 agreed",
"XYZ-ZTE-43235 - serverA605 disagreed (asdfjlasdj yxvv il lkyeas sadfa)","XYZ-ZTE-43235"
"XYZ-ZTE-11591 serverB144 agreed","XYZ-ZTE-11591"
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.