Keep only certain columns in a csv file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Keep only certain columns in a csv file
# 1  
Old 08-05-2011
Keep only certain columns in a csv file

I have a file that has 500+ columns but only need 15 of those and all the other ones can be removed.

What is the best way to do this? Can I get some examples please?
# 2  
Old 08-05-2011
With awk you can do something like:
Code:
awk -F, '{print $1,$2,$4,$18,$20}'

the numbers being the column # of course.
# 3  
Old 08-05-2011
Hi,

The solution of 'neutronscott' must work and it's easy to program. I did one in 'perl' and althought perhaps you don't need it, could be useful for another one.
Code:
$ cat script.pl
use warnings;
use strict;

@ARGV >= 3 or die <<USAGE;
Usage: perl $0 file_separator #column #column #column ... file
Example: perl $0 "," 7 12 41 myfile.csv
USAGE

## Get arguments:
## First one ($file_separator) -> Separator of csv file.
## Last one ($file) -> The file to process.
## Rest of arguments (@columns) -> The numbers of the columns to print.
my $file_separator = shift;
my $file = pop @ARGV;
my @columns = grep { /^\d+$/ } @ARGV;

open my $fh, "<", $file or die "Cannot open file $file: $!\n";

## Index of columns begin in 0.
@columns = map { --$_ } @columns;

while ( <$fh> ) {

        ## Omit blank lines.
        next unless /\S/;

        ## Remove end of line character.
        chomp;

        ## Once in the processing of the file, count number of fields and remove
        ## from the array of columns those number bigger or incorrect (not
        ## positives).
        if ( 1 .. 1 ) {
                my $line_fields = eval "tr/$file_separator/$file_separator/";
                ++$line_fields;
                @columns = grep { $_ < $line_fields && $_ >= 0  } @columns; 
        }

        ## Split columns and join only those pointed out in parameters.
        my @line_columns = split /$file_separator/;
        my $outline = join "$file_separator", @line_columns[ @columns ];
        printf "%s\n", $outline;
}

close $fh or warn "Cannot close file $file: $!\n";
$ perl script.pl
Usage: perl script.pl file_separator #column #column #column ... file
Example: perl script.pl "," 7 12 41 myfile.csv
$ perl script.pl "," 1 4 7 36 infile
... ((Output)) ...

Regards,
Birei
# 4  
Old 08-05-2011
I used the one neutronscott put out. But I would be interested in what you have done birei. I will try that way just to see if I can get it to work for me.

Thanks to both!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Randomize columns in CSV file

Hi there, friends! Writing exams again! This time my wish would be to randomize certain columns in a csv file. Given a file containing records consisting of 3 columns tab-separated: A B C A B C A B C I would love to get the columns of each record in random order...separated by a tab as... (12 Replies)
Discussion started by: eldeingles
12 Replies

2. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

3. Shell Programming and Scripting

Deleting all the fields(columns) from a .csv file if all rows in that columns are blanks

Hi Friends, I have come across some files where some of the columns don not have data. Key, Data1,Data2,Data3,Data4,Data5 A,5,6,,10,, A,3,4,,3,, B,1,,4,5,, B,2,,3,4,, If we see the above data on Data5 column do not have any row got filled. So remove only that column(Here Data5) and... (4 Replies)
Discussion started by: ks_reddy
4 Replies

4. Shell Programming and Scripting

Checking the CSV File Columns

Hi All, i have a CSV file like below, col1 col2 col3 col4 col5 col6 col7 a1 a2 a3 a4 a5 a6 a7 b1 b2 b3 b4 b5 b6 b7 c1 c2 c3 c4 c5 c6 c7 d1 d2 d3 d4 d5 d6 d7 Col1,col2.. are the column names and... (1 Reply)
Discussion started by: dileep_d10
1 Replies

5. UNIX for Advanced & Expert Users

Help in Deleting columns and Renaming Mutliple columns in a .Csv File

Hi All, i have a .Csv file in the below format startTime, endTime, delta, gName, rName, rNumber, m2239max, m2239min, m2239avg, m100016509avg, m100019240max, metric3min, m100019240avg, propValues 11-Mar-2012 00:00:00, 11-Mar-2012 00:05:00, 300.0, vma3550a, a-1_CPU Index<1>, 200237463, 0.0,... (9 Replies)
Discussion started by: mahi_mayu069
9 Replies

6. Shell Programming and Scripting

Add columns in csv file

Hi everybody, I am new here and I need a reel help please A have a csv file and I want to add new in the end of the file Devisce Model,VMGuest Name,Host OS Name, Memory Size Value1,Value2,Value3,Value4 Value5,Value6,Value7,Value8 Value9,Value10,Value11,Value12 And I want to add to new... (3 Replies)
Discussion started by: villebonnais
3 Replies

7. UNIX for Advanced & Expert Users

Unix Bash: substitute columns in .csv using other .csv columns

Hi All, I have two .csv's input.csv having values as (7 columns) ABC,A19907103,ABC DEV YUNG,2.17,1000,2157,07/07/2006 XYZ,H00213850,MM TRUP HILL,38.38,580,23308,31/08/2010 output.csv having (25 columns) A,B,C,D,E,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U,V,W,X,Y... (4 Replies)
Discussion started by: abhivyas
4 Replies

8. Shell Programming and Scripting

Deleting columns from CSV file

Hi All, Am working on perl script which should delete columns in existing CSV file. If my file is : AA,BB,CC,DD 00,11,22,33 00,55,66,77 00,99,88,21 AA,BB... are all my headers can come in any order (e.g AA,CC,BB...) and rest are values. I want to delete column CC... Can anybody help... (2 Replies)
Discussion started by: darshakraut
2 Replies

9. Shell Programming and Scripting

no of columns in csv file

Hi, I would like to know how can I find the number of columns in each line in a csv file. I have a csv file wiht comma separated an dhas 10000 line in it. I want to verify the number of columns in each line. is there any way to find it out using awk or any other commands? Thanks.... (2 Replies)
Discussion started by: McLan
2 Replies

10. Shell Programming and Scripting

Help with comparing columns from a csv file

Hi there, I have an csv file. I want to compare the 16th and 18th columns. They contain alpha numeric characters. Some are same and some are different. We have to pick the ones which are different. But with certain rules. 16th col. 18th col. ---------- ... (1 Reply)
Discussion started by: sickboy
1 Replies
Login or Register to Ask a Question