LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This Linux forum is for members that are new to Linux.
Just starting out and have a question? If it is not in the man pages or the how-to's this is the place!

Notices

Reply
 
Search this Thread
Old 07-14-2009, 09:40 AM   #1
briana.paige
LQ Newbie
 
Registered: May 2009
Location: Canada
Posts: 14

Rep: Reputation: 0
Batch manipulating CSV columns and files in Perl script


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 1Column H:

1
2
3

FILE 2Column H:

4
5
6

% NEW FILEColumn 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
 
Old 07-14-2009, 11:02 AM   #2
slugmax
Member
 
Registered: Nov 2008
Location: Northern CT
Distribution: Slackware, Debian, Ubuntu
Posts: 69

Rep: Reputation: 24
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

Last edited by slugmax; 07-14-2009 at 02:01 PM.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
CSV null columns with gawk pcock Programming 1 03-30-2009 07:35 PM
Manipulating comma delimited CSV - newbie help with sed etc jonnymorris Programming 16 09-19-2008 06:14 AM
addings columns in perl script activeq Programming 5 09-03-2008 01:17 AM
looking for a perl script to convert html table data into a csv file swiftguy121 Linux - Software 2 04-25-2007 07:28 PM
bash script on manipulating files fiomba Linux - Software 8 10-30-2004 08:31 PM


All times are GMT -5. The time now is 11:33 AM.

Main Menu
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
identi.ca: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration