Visit Our UNIX and Linux User Community


How to extract one column from csv file in perl?


 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers How to extract one column from csv file in perl?
# 1  
Old 11-26-2010
How to extract one column from csv file in perl?

Hi everyone,
i am new to perl programming, i have a problem in extracting single column from csv file. the column is the 20th column,
please help me..

at present i use this code

Code:
 
#!C:/perl/bin
use warnings;
use strict;
my $file1 = $ARGV[0];
open FILE1, "<$file1"
    or die "Can't open $file1: $!\n";
    
    my #found;
    my $period;
    
    while ( <FILE1> ) {
        
            ### Remove the newline that acts as a record delimiter
            chop;
            
            ### Break up the record data into separate fields
          
            ( $period) = /^[.*,.*{19,}].*,/  
            #( $name, $location, $mapref, $type, $description ) =
}

# 2  
Old 11-26-2010
Please provide an example of your input as well as the output format you expect .
What is your field delimiter ?

Code:
awk -F<your_delimiter> '{print$20}' input

?
# 3  
Old 11-26-2010
Code:
perl -ne 'print $_ if ($.==20)' inputfile

# 4  
Old 11-26-2010
Without knowing more about your CSV file structure, there is little anybody can do to give you a definitive answer.

However here is a generic solution which uses the Text::CSV module.
Code:
use strict;
use Text::CSV;

my $file = $ARGV[0];
my $csv = Text::CSV->new();

open (CSV, "<", $file) or die "Can't open $file: $!\n";

while (<CSV>) {
   if ($csv->parse($_)) {
      my @column = $csv->fields();
      print "$column[19]\n";
   } else {
      my $err = $csv->error_input;
      print "ERROR: Failed to parse line: $err";
   }
}

close CSV;

# 5  
Old 11-29-2010
hi every one!! thanks a lot for your answers..

my file has \t(tab) as delimiter, and the input file looks like this

Code:
 
col1  col2 col3....col20 col21 col22
A       B     C ...... dy    0       xyz
C       R      S .......dx             abc 
E        T      E .......dc             res

The output file sholud look like this..

Code:
 
col1  col2 col3....col20 col21 col22
A       B     C ...... dy    0       xyz
C       R      S .......dx    0         abc 
E        T      E .......dc             res


Without changing any other column data, i need to update only column 20 and print the entire file... please help..as i dont know anything about perl..

Last edited by kvth; 11-29-2010 at 02:25 AM.. Reason: spelling mistake
# 6  
Old 11-29-2010
Code:
 
while(<CSV>)
{
my @line = split(/\t/, $_);
$line[19] = your_change;
foreach(@line)
{
print "$_\t";
}
print "\n";
}

If there are other whitespace characters seperating the columns, perl has a match any white space except newline escape character. I can't remember what it is off the top of my head.

Last edited by ilikecows; 11-29-2010 at 03:09 AM.. Reason: added code tags and corrected syntax error
# 7  
Old 11-29-2010
hey thanks Mr.ilikecows... your code is errors free.. but when i run it, i get a blank output file...please help
 

Previous Thread | Next Thread
Test Your Knowledge in Computers #423
Difficulty: Medium
C# is a general-purpose, multi-paradigm programming language encompassing strong typing, lexically scoped, imperative, declarative, functional, generic, object-oriented (class-based), and component-oriented programming disciplines.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

How do I extract specific column in multiple csv files?

file1: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,47 GETregistry,100,21592,0.00%,13 GEThomeLayout,100,30466,0.00%,17 file2: Name,Threshold,Curr Samples,Curr Error%,Curr ART GETHome,100,21601,0.00%,33 GETregistry,100,21592,0.00%,22... (6 Replies)
Discussion started by: Raghuram717
6 Replies

2. Shell Programming and Scripting

Compare 2 files of csv file and match column data and create a new csv file of them

Hi, I am newbie in shell script. I need your help to solve my problem. Firstly, I have 2 files of csv and i want to compare of the contents then the output will be written in a new csv file. File1: SourceFile,DateTimeOriginal /home/intannf/foto/IMG_0713.JPG,2015:02:17 11:14:07... (8 Replies)
Discussion started by: refrain
8 Replies

3. Shell Programming and Scripting

Perl - Extract first column from file

Hi, I want to extract first column from a file and redirect the output to another file in perl. I am able to do this from command line by executing below commands. perl -anle 'print $F' Input.dat > Output.dat perl -ne '@F = split("\t", $_); print "$F\n";' Input.dat > Output.dat perl -anE... (7 Replies)
Discussion started by: Neethu
7 Replies

4. Shell Programming and Scripting

Perl regexp to extract first and second column

Hi, I am trying with the below Perl one-liner using regular expression to extract the first and second column of a text file: perl -p -e "s/\s*(\w+).*/$1/" perl -p -e "s/\s*.+\s(.+)\s*/$1\n/" whereas the text file's data looks like: Error: terminated 2233 Warning: reboot 3434 Warning:... (3 Replies)
Discussion started by: royalibrahim
3 Replies

5. Shell Programming and Scripting

Perl code to grep a particular column in CSV format

Hi I want to grep a column 6 & column 7 from a CSV Format file & then i have to find the difference between these columns as these both columns contains date & time in 7/7/2012 9:20 this format . So kindly help me out ASAP. But please kindly dis xls has to be converted in csv format as may... (5 Replies)
Discussion started by: Prateek@123
5 Replies

6. Shell Programming and Scripting

3 column .csv --> correlation matrix; awk, perl?

Greetings, salutations. I have a 3 column csv file with ~13 million rows and I would like to generate a correlation matrix. Interestingly, you all previously provided a solution to the inverse of this problem. Thread title: "awk? adjacency matrix to adjacency list / correlation matrix to list"... (6 Replies)
Discussion started by: R3353
6 Replies

7. Shell Programming and Scripting

Extract first column from second line in perl

Hello Gurus I have a source file which has the first line as header and the rest are the records I need to extract the first column from the second line to extract a value I/P ... (7 Replies)
Discussion started by: Pratik4891
7 Replies

8. Shell Programming and Scripting

Perl script to extract second column from a xls

Can Anyone tell me how to extract the second column of a xls sheet And compare the content of each row of the column with a .h file. xls sheet is having only one spreadsheet. (2 Replies)
Discussion started by: suvenduperl
2 Replies

9. Shell Programming and Scripting

extract csv based on column value

Hi I have a csv file which is below A,5 B,6 C,10 D,7 I want the values who's second column is greater than 7 say C,10 D,7 Help me please... Thanks, Maruth (3 Replies)
Discussion started by: maruthavanan
3 Replies

10. Shell Programming and Scripting

extract values from column with Perl

Hi everybody I have some problems with PERL programming. I have a file with two columns, both with numeric values. I have to extract the values > 50 from the 2nd columns and sum them among them. The I have to sum the respective values in the first column on the same line and, at the end, I... (6 Replies)
Discussion started by: m_elena
6 Replies

Featured Tech Videos