CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

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

 



wattsup
Novice

Feb 26, 2016, 8:30 AM

Post #1 of 12 (3690 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 (3687 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 (3606 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 (3603 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 (3590 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 (3587 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 (3583 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 (3578 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 (3566 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 (3535 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 (3532 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 (3487 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) Powered by Gossamer Forum v.1.2.0

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