Results 1 to 11 of 11
  1. #1
    Join Date
    Jun 2004
    Posts
    21

    Unanswered: manipulation of two file

    I have two large file;
    File_1=member file
    File_2=Cost file

    Member file contain

    Batch number: 17 created from 01/04/2002 to 30/04/2006

    Client name Date Item TAX Total
    Ms. Jacky Ward 21/04/2006 699 $0.00 $113.00
    Ms. Shirley BANNISTER 18/04/2006 870 $11.3 $642.00
    Mrs. Rex chapman 24/04/2006 850 $7.41 $741.00
    Mr. JOHN PANOV 24/04/2006 800 $0.00 $99.00
    Mr. Joe Yu 9/02/2006 321 $0.00 $250

    Cost file contain
    Date PO # Invoice# Supplier Name Amount
    10/04/2006 Chapman IN110026 ABC PTY LTD $660.00
    04/05/2006 BATHGATE IN554446 EF Suplierx $880.00
    9/03/2006 burk IN556546 COLLEGE of foot $60.00
    21/02/2006 Yu 879786 Swmining Co $70

    I need script to search for by PO#(Cost file or file_2) in Member file(FILE_2)
    Note** PO# have the 8(maximum) first characters of Client name file (the Surname)
    If PO# found in Member file or match client surname
    Then the out put will be

    Client name Date Item TAX Total PO# Invoice# Suplier Amount Earn
    Rex chapman 24/04/2006 850 $7.41 $741.00 Chapman IN110026 ABC PTY LTD $660.00 $81
    Joe Yu 9/02/2006 321 $0.00 $250 Yu 879786 Swming Co $70 $180

    Please Note Earn = $Total - $Amount

    If Po# did NOT match client surname Print

    No member found for the following;

    Batch_No Client name Date Item TAX Total
    17 Jacky Ward 21/04/2006 699 $0.00 $113.00
    17 Shirley BANNISTER 18/04/2006 870 $11.3 $642.00
    17 JOHN PANOV 24/04/2006 800 $0.00 $99.00
    18 xxxx
    32 yyyy

    And If the first 8 characters of surname did not match #PO
    Please Note PO# may have minimum of 2 characters and maximum of 8 characters

    No Purchase found for the following:

    Date PO # Invoice# Supplier Name Amount
    04/05/2006 BATHGATE IN554446 EF Suplierx $880.00
    9/03/2006 burk IN556546 COLLEGE of foot $60.00
    Last edited by melindaj; 05-14-06 at 05:37.

  2. #2
    Join Date
    Feb 2006
    Posts
    56
    What have you tried so far? This is not a script writing service, this is a help forum. If you want a custom script written there are a number of sites where you can hire a programmer for a very reasonable price.

  3. #3
    Join Date
    Jun 2004
    Posts
    21
    Quote Originally Posted by KevinADC
    What have you tried so far? This is not a script writing service, this is a help forum. If you want a custom script written there are a number of sites where you can hire a programmer for a very reasonable price.
    I am not sure were to start!!, your help is much appreciated

  4. #4
    Join Date
    Feb 2006
    Posts
    56
    well, I'm not sure where to start to get you started. Do you know any perl?

  5. #5
    Join Date
    Jun 2004
    Posts
    21
    ok, I am just a beginner and this is what I have been able to do. it only grep for PO# from claim.txt file.


    #! /usr/bin/perl
    my $file_one = 'purchase.txt';
    my $file_two = 'claim.txt';

    my @ids;
    open(FILE1,"<$file_one") or die $!;
    while(<FILE1>){
    push(@ids,(split(' ',uc $_))[1]);
    }
    close FILE1;

    my @array;
    open(FILE2,"<$file_two") or die $!;
    foreach my $line(<FILE2>){
    my @fields = split(' ',uc $line);
    if(grep{$fields[2] =~ $_}@ids){
    $line = join("|",@fields);
    print "@fields\n";
    }
    push(@array,$line);
    }
    close FILE2;
    Last edited by melindaj; 06-09-06 at 13:16.

  6. #6
    Join Date
    Feb 2006
    Posts
    56
    based on the files and all the information you provided I came up with this:

    Code:
    removed
    the files used are exact copies of what you posted, the output is:

    Code:
    Client_name Date Item TAX Total PO# Invoice# Supplier Amount Earn
    "Rex chapman" 24/04/2006 850 7.41 741.00 Chapman IN110026 "ABC PTY LTD" $660.00 81
    "Joe Yu" 9/02/2006 321 0.00 250 Yu 879786 "Swmining Co" $70 180
    
    
    Batch_No Client name Date Item TAX Total
    17 "JOHN PANOV" 24/04/2006 800 0.00 99.00
    17 "Jacky Ward" 21/04/2006 699 0.00 113.00
    17 "Shirley BANNISTER" 18/04/2006 870 11.3 642.00
    I notice you have this question on Expert Exchange. I also post there and would appreciate if I post the code over there to answer the question that you could award me the points. My name on that forum is Perl_diver. Let me know and I will post the above code on that forum too.
    Last edited by KevinADC; 06-14-06 at 15:38.

  7. #7
    Join Date
    Jun 2004
    Posts
    21
    Thanks kevinADC that was nice piece of work.
    Base on your script I get bellow output, I am not sure how did you get your output!
    Yes I will give the point to Perl_diver but let us resolve the issues in this forum first.


    Client_name Date Item TAX Total PO# Invoice# Supplier Amount Earn


    Batch_No Client name Date Item TAX Total
    17 "JOHN PANOV" 24/04/2006 800 0.00 99.00
    17 "Joe Yu" 9/02/2006 321 0.00 250
    17 "Jacky Ward" 21/04/2006 699 0.00 113.00
    17 "Rex chapman" 24/04/2006 850 7.41 741.00
    17 "Shirley BANNISTER" 18/04/2006 870 11.3 642.00

  8. #8
    Join Date
    Feb 2006
    Posts
    56
    OK, I used this as file_1 (member.txt in my code):

    Code:
    Batch number: 17 created from 01/04/2002 to 30/04/2006
    
    Client name Date Item TAX Total
    Ms. Jacky Ward 21/04/2006 699 $0.00 $113.00
    Ms. Shirley BANNISTER 18/04/2006 870 $11.3 $642.00
    Mrs. Rex chapman 24/04/2006 850 $7.41 $741.00
    Mr. JOHN PANOV 24/04/2006 800 $0.00 $99.00
    Mr. Joe Yu 9/02/2006 321 $0.00 $250
    I used this as file_2 (cost.txt in my code):

    Code:
    Date PO # Invoice# Supplier Name Amount
    10/04/2006 Chapman IN110026 ABC PTY LTD $660.00
    04/05/2006 BATHGATE IN554446 EF Suplierx $880.00
    9/03/2006 burk IN556546 COLLEGE of foot $60.00
    21/02/2006 Yu 879786 Swmining Co $70
    I just copy and pasted them from your first post. And I used the examples of output you wanted to write the code I came up with (I put commas around fields with spaces in them to make it easier to distinguish them from adjacent fields.) The code I used I already posted. When I use the above files and run the code I get this output:

    Code:
    Client_name Date Item TAX Total PO# Invoice# Supplier Amount Earn
    "Rex chapman" 24/04/2006 850 7.41 741.00 Chapman IN110026 "ABC PTY LTD" $660.00 81
    "Joe Yu" 9/02/2006 321 0.00 250 Yu 879786 "Swmining Co" $70 180
    
    
    Batch_No Client name Date Item TAX Total
    17 "JOHN PANOV" 24/04/2006 800 0.00 99.00
    17 "Jacky Ward" 21/04/2006 699 0.00 113.00
    17 "Shirley BANNISTER" 18/04/2006 870 11.3 642.00
    The two blank lines in the middle are just visually seperate the found and not found parts from each other.

  9. #9
    Join Date
    Jun 2004
    Posts
    21
    Perfect yes you are quit right, it is working,
    “The posted information “files” was just for easy reading”

    A- But with actual data it does not, I have attached the two file and I will appreciate you if you can come up with a solution for this question with the actual data.


    B- the part 3 of the question is missing
    No Purchase found for the following:

    Date PO # Invoice# Supplier Name Amount
    04/05/2006 BATHGATE IN554446 EF Suplierx $880.00
    9/03/2006 burk IN556546 COLLEGE of foot $60.00
    Attached Files Attached Files

  10. #10
    Join Date
    Feb 2006
    Posts
    56
    The data you posted wasn't the real data? How did you expect to get the working solution to non-working data? The solution I provided took at least an hour to code and test and tweak. Now the real data is different?

  11. #11
    Join Date
    Jun 2004
    Posts
    21

    Exclamation

    Removed !! why?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •