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 #636
Difficulty: Easy
Apple reported that as of September 2016, there have been over 190 billion app downloads from their App Store.
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