Batch manipulating CSV columns and files in Perl script

briana.paige · 07-14-2009, 10:40 AM

Hi,

I have a lot of CSV files that I want to be able to:

1. Extract specific columns
2. Append specific columns out in ONE new CSV (with each column being put in its own column)

I know there are modules that already exist for CSV and Spreadsheets etc... but I'm not familiar with any of them. If there is a specific one that does the job, please let me know and please explain how to incorporate it in my code.

PHP Code:


			
% FILE 1, Column H:

1
2
3

% FILE 2, Column H:

4
5
6

% NEW FILE, Column A & Column B:

1  4
2  5
3  6

My code is below in its entirety. It extracts the columns I want from each FILE and write them to the NEW FILE, but they are all appended to the New file in Column A and this is not what I want.

Code:

#!/usr/bin/perl
use File::Find;
use Cwd;

#1. Get listings of current folder.
$dir = getcwd();

find(sub{
	# move on to next file if:
	#(1) file is a directory
	return if -d;
	#(2) file is not readable
	return unless -r;

if ($File::Find::name =~ m/.+\.[Cc][Ss][Vv]$/)
	  {
	    #Open CSV file.
	    open (CSV,$File::Find::name ) or die "could not open '$InputFile'  $!\n";
	    print "Processing $File::Find::name\n";
		open (OUT,">>PVpower\_Ottawa\_NRC_2002.csv");

			    #Set First line flag.
	    my $FirstLine = 1;
	    $OUTPUT = "" ;

	    	    #Loop through each line record.
	    while (<CSV>)
	      {
			if ($FirstLine == 1)
			  {
			    my$colh = (split(/,/))[7];
 				print OUT "$colh \n"; #printout the 8th column of the CSV results file
				}
			}
		close OUT;
		close CSV;
		}
	}
	, $dir
)

Thanks in advance,
Briana

slugmax · 07-14-2009, 12:02 PM

I'm not sure if this works for you, but the GNU textutils were made
for dealing with such data. A shell solution that works with any
number of CSV files is this:

Code:

paste -d, <(cut -d, -f8 file1.csv) <(cut -d, -f8 file2.csv) > output.csv

This pulls the eighth column from each file and pastes them into a new
file the way you would expect. You can specify as many files as you
need. One caveat is that this (and using split in Perl) will only work
on simple CSV data - meaning there are no embedded commas in the
fields themselves.

For properly parsing CSV data in Perl, there is a builtin module
Text::Parsewords that will do the right thing, even if the CSV
fields themselves contain commas or are quoted. Docs are here:

http://perldoc.perl.org/Text/ParseWords.html

The function to use is quoteword.

Continuing in Perl, you will need to build up a 2D array of columns
from each file first, then post-process the array, printing the
elements in the right order to a new CSV file. This might help for
ideas:

http://www.perlmonks.org/?node_id=46529

Doug