Perl Guru Forums: Perl Programming Help: Beginner: Merge/Append CSV Files together

Home: Perl Programming Help: Beginner:
Merge/Append CSV Files together

wattsup
Novice

Feb 26, 2016, 8:30 AM

Post #1 of 12 (5612 views)

Merge/Append CSV Files together	Can't Post

I have been searching for the best way to merge 2 or more CSV files together.

I am very new to perl. Trying to rewrite some old winbatch scripts.

First CSV File

Code

BOL,SAP Shipment,Ship From,Ship To,P.O. Number,SAP Transfer,LPN,Material Code,Batch Code,Cases,Weight 
81171262,81171262,5026,115075,4500214105,,3000935271,2265536614,5026604900,36,2106.14 
81171262,81171262,5026,115075,4500214105,,3000935291,2265536614,5026604900,36,2116.94 
81171262,81171262,5026,115075,4500214105,,3000935293,2265536614,5026604900,36,2123.99 
81171262,81171262,5026,115075,4500214105,,3000935298,2265536614,5026604900,36,2118.89

Second and so on

Code

BOL,SAP Shipment,Ship From,Ship To,P.O. Number,SAP Transfer,LPN,Material Code,Batch Code,Cases,Weight 
81171704,81171704,5026,115076,4500214224,,3000935280,2.2655E+12,5026604900,40,1326.98 
81171704,81171704,5026,115076,4500214224,,3000938276,2.2655E+12,5026604800,40,1326.44 
81171704,81171704,5026,115076,4500214224,,3000938529,2.2655E+12,5026604800,40,1331.55

need them to be like this, one CSV file

Code

BOL,SAP Shipment,Ship From,Ship To,P.O. Number,SAP Transfer,LPN,Material Code,Batch Code,Cases,Weight 
81171262,81171262,5026,115075,4500214105,,3000935271,2265536614,5026604900,36,2106.14 
81171262,81171262,5026,115075,4500214105,,3000935291,2265536614,5026604900,36,2116.94 
81171262,81171262,5026,115075,4500214105,,3000935293,2265536614,5026604900,36,2123.99 
81171262,81171262,5026,115075,4500214105,,3000935298,2265536614,5026604900,36,2118.89 
BOL,SAP Shipment,Ship From,Ship To,P.O. Number,SAP Transfer,LPN,Material Code,Batch Code,Cases,Weight 
81171704,81171704,5026,115076,4500214224,,3000935280,2.2655E+12,5026604900,40,1326.98 
81171704,81171704,5026,115076,4500214224,,3000938276,2.2655E+12,5026604800,40,1326.44 
81171704,81171704,5026,115076,4500214224,,3000938529,2.2655E+12,5026604800,40,1331.55

FishMonger
Veteran / Moderator

Feb 26, 2016, 9:28 AM

Post #2 of 12 (5609 views)

Re: [wattsup] Merge/Append CSV Files together [In reply to]	Can't Post

You don't want to include the header line multiple times. It should be on the first line only.

Post your script so we can see what's wrong.

wattsup
Novice

Mar 1, 2016, 8:57 AM

Post #3 of 12 (5528 views)

Re: [FishMonger] Merge/Append CSV Files together [In reply to]	Can't Post

Sorry for the delay on getting back.

Here is sample of the code I am trying. This only creates a blank EM943_2580.CSV file. I know Its something simple I am missing.

Code

sub mergeFiles 
{ 
    logs("[MERGING FILES]");         
 
    my $dataIn = $bb_path . "exp_20160218162714_0081171045.csv"; 
    my $output = $bb_path . "EM943_2580.CSV"; 
 
    print "$dataIn"; 
    my $input = <STDIN>; 
 
     
    use Text::CSV; 
  
    my $csv = Text::CSV->new({ binary => 1 }) or die "Error creating CSV object: ".Text::CSV->error_diag (); 
  
    open my $fh, "<:encoding(utf8)", "$dataIn" or die "Error reading CSV file: $!"; 
    my $linea = <$fh>;  
    while ( my $record = $csv->getline( $fh ) ) { 
     
     
    } 
    $csv->eof or $csv->error_diag(); 
    close $fh; 
 
 
    $csv->eol ("\r\n"); 
  
    open $fh, ">:encoding(utf8)", "$output" or die " $output: $!"; 
    $csv->print ($fh, $_) for @records; 
    close $fh or die "$output: $!"; 
 
    #$merge_count = 0; 
    logs("[DONE MERGING FILES]\n");              
}

FishMonger
Veteran / Moderator

Mar 1, 2016, 9:17 AM

Post #4 of 12 (5525 views)

Re: [wattsup] Merge/Append CSV Files together [In reply to]	Can't Post

The body of your while loop is empty i.e., isn't doing anything, so where is @records declared and assigned values?

Take the use Text::CSV; statement out of the subroutine. It should be at the beginning of your script with the other use statements.

wattsup
Novice

Mar 1, 2016, 2:02 PM

Post #5 of 12 (5512 views)

Re: [FishMonger] Merge/Append CSV Files together [In reply to]	Can't Post

dang.. that was something simple! LOL

okay Now I am missing the first line (header), then another question is how would I make this more dynamic? I want to read a directory of csv files and combined them to one file.

Code

   logs("[MERGING FILES]");         
 
    my $dataIn = $bb_path . "exp_20160218162714_0081171045.csv"; 
    my $output = $bb_path . "EM943_2580.CSV"; 
 
    print "$dataIn"; 
    my $input = <STDIN>; 
 
     
  
    my $csv = Text::CSV->new({ binary => 1 }) or die "Error creating CSV object: ".Text::CSV->error_diag (); 
  
    open my $fh, "<:encoding(utf8)", "$dataIn" or die "Error reading CSV file: $!"; 
    my $linea = <$fh>;  
    while ( my $record = $csv->getline( $fh ) ) { 
     
         push @records, $record; 
    } 
     
    $csv->eof or $csv->error_diag(); 
    close $fh; 
 
    $csv->eol ("\r\n"); 
  
    open $fh, ">:encoding(utf8)", "$output" or die " $output: $!"; 
    $csv->print ($fh, $_) for @records; 
    close $fh or die "$output: $!"; 
 
    #$merge_count = 0; 
    logs("[DONE MERGING FILES]\n");

FishMonger
Veteran / Moderator

Mar 1, 2016, 2:30 PM

Post #6 of 12 (5509 views)

Re: [wattsup] Merge/Append CSV Files together [In reply to]	Can't Post

Quote

Code

    while ( my $record = $csv->getline( $fh ) ) {  
      
         push @records, $record;  
    }

Why are you pushing the data onto an array? It would be more efficient to output it directly to the csv file.

If all of the files are in the same format and it's proper csv formant, then it would be more efficient to concatenate them without using the Text::CSV module. However, if you need to make sure it gets outputted in proper csv format, then use the module. I prefer the Text::CSV_XS module because it uses XS code which is faster than the Text::CSV module.

Since the $bb_path var is declared outside of the sub, you should pass it to the sub as a reference instead of accessing it directly.

If you want it to handle an unknown number of files within a directory, you can either compile the list of files and pass it to the sub, or you can have the sub do the compiling of the list. Once you have that list, then loop over it processing each file one at a time. My preference would be to compile the list and pass it to the sub.

wattsup
Novice

Mar 1, 2016, 3:27 PM

Post #7 of 12 (5505 views)

Re: [FishMonger] Merge/Append CSV Files together [In reply to]	Can't Post

In Reply To

Quote

Code

    while ( my $record = $csv->getline( $fh ) ) {  
      
         push @records, $record;  
    }

Quote

Why are you pushing the data onto an array? It would be more efficient to output it directly to the csv file.

I am very new to perl. wasn�t sure what is the best way

Quote

If all of the files are in the same format and it's proper csv formant, then it would be more efficient to concatenate them without using the Text::CSV module.

do you have a snip of what your talking about?

Quote

However, if you need to make sure it gets outputted in proper csv format, then use the module. I prefer the Text::CSV_XS module because it uses XS code which is faster than the Text::CSV module.

Yes, it needs to be in proper csv format. Okay have not seen CSV_XS.

Quote

Since the $bb_path var is declared outside of the sub, you should pass it to the sub as a reference instead of accessing it directly.

Not sure what your talking about.

Quote

If you want it to handle an unknown number of files within a directory, you can either compile the list of files and pass it to the sub, or you can have the sub do the compiling of the list. Once you have that list, then loop over it processing each file one at a time. My preference would be to compile the list and pass it to the sub.

I do have a sub for gathering the list of files in an array.

FishMonger
Veteran / Moderator

Mar 1, 2016, 4:17 PM

Post #8 of 12 (5500 views)

Re: [wattsup] Merge/Append CSV Files together [In reply to]	Can't Post

I'm tied up other projects right now but will post an example later tonight or tomorrow when I have more time.

Laurent_R
Veteran / Moderator

Mar 1, 2016, 11:14 PM

Post #9 of 12 (5488 views)

Re: [wattsup] Merge/Append CSV Files together [In reply to]	Can't Post

In Reply To

Yes, it needs to be in proper csv format.

Yeah, I can understand that you want proper output, but the real question as this: do you need to validate the CSV (and perhaps reject some files if they don't pass your validation) or can you just take the input files as they are. These are two very different processes. If you can trust the input, then the code will be simpler and the processing much faster, because you can just copy the input lines as they are. And you don't need a CSV module for that.

wattsup
Novice

Mar 2, 2016, 10:48 AM

Post #10 of 12 (5457 views)

Re: [Laurent_R] Merge/Append CSV Files together [In reply to]	Can't Post

good question... after thinking about it, I don't need to validate the CSV.

FishMonger
Veteran / Moderator

Mar 2, 2016, 11:41 AM

Post #11 of 12 (5454 views)

Re: [wattsup] Merge/Append CSV Files together [In reply to]	Can't Post

Here's a short example tested against the sample data you supplied.

Code

#!/usr/bin/perl 
 
use warnings; 
use strict; 
use autodie; 
 
my $bb_path = "C:/test/wattsup"; 
opendir(my $dh, $bb_path); 
my @csv_files = grep { /\.csv$/ } readdir($dh); 
closedir $dh; 
 
merge_files($bb_path, \@csv_files); 
 
sub merge_files { 
    my ($path, $csv_files) = @_; 
    my $printed; 
     
    open(my $csv_out, '>', "$path/EM943_2580.CSV"); 
 
    foreach my $csv_file (@{ $csv_files }) { 
        open(my $fh, '<', "$path/$csv_file"); 
        my $header = <$fh>; 
        print {$csv_out} $header unless $printed++; 
        print {$csv_out} <$fh>; 
        close $fh; 
    } 
    close $csv_out; 
}

c:\test\wattsup>type EM943_2580.CSV

Quote

BOL,SAP Shipment,Ship From,Ship To,P.O. Number,SAP Transfer,LPN,Material Code,Batch Code,Cases,Weight
81171262,81171262,5026,115075,4500214105,,3000935271,2265536614,5026604900,36,2106.14
81171262,81171262,5026,115075,4500214105,,3000935291,2265536614,5026604900,36,2116.94
81171262,81171262,5026,115075,4500214105,,3000935293,2265536614,5026604900,36,2123.99
81171262,81171262,5026,115075,4500214105,,3000935298,2265536614,5026604900,36,2118.89
81171704,81171704,5026,115076,4500214224,,3000935280,2.2655E+12,5026604900,40,1326.98
81171704,81171704,5026,115076,4500214224,,3000938276,2.2655E+12,5026604800,40,1326.44
81171704,81171704,5026,115076,4500214224,,3000938529,2.2655E+12,5026604800,40,1331.55

(This post was edited by FishMonger on Mar 2, 2016, 11:43 AM)

wattsup
Novice

Mar 3, 2016, 10:04 AM

Post #12 of 12 (5409 views)

Re: [FishMonger] Merge/Append CSV Files together [In reply to]	Can't Post

Thank You FishMonger !! your snip worked a lot faster and better!! Thanks for you help again!!

Search for (options)

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives


	MAIN INDEX	SEARCH POSTS	WHO'S ONLINE	LOG IN