2

I have a CSV file from which I need to remove one column from it.The problem is I have exported the CSV file without headers.So how can I remove the column from the CSV file.For example if I have the example.csv I want to remove the last column from it which is a boolean data and have the file as input.csv.

input.csv

1,"data",100.00,TRUE
2,"code",91.8,TRUE
3,"analytics",100.00,TRUE

output.csv

1,"data",100.00
2,"code",91.8
3,"analytics",100.00
  • Post an example chunk and your desired output from that. – heemayl Oct 19 '16 at 5:49
  • 2
    What's wrong with having no headers? – countermode Oct 19 '16 at 6:32
  • You should clearly indicate that your complete CSV is this simplistic, i.e. no fields spanning multiple lines. Either give an example matching csv or use csv-simple. CSV is much more complex format than most assume, and in only a extremely small subset of CSV files can be handled with "standard" text processing tools (sed, awk), the vast majority require parsing e.g. with Python/Perl/Ruby libraries. – Anthon Oct 19 '16 at 6:34
14

To remove the fourth column,

$ cut -d, -f4 --complement example.csv > input.csv

Adjust the -f option to match the column number.

If the CSV file is more complicated, you could use some perl and the Text::CSV package,

$ perl -MText::CSV -E '$csv = Text::CSV->new({binary=>1}); 
  while ($row = $csv->getline(STDIN)) 
  {
    print "$row->[0],$row->[1],$row->[2]\n"
  }' < example.csv > input.csv
3

Use the csv module in Python:

python -c 'import sys,csv
w = csv.writer(sys.stdout)
for row in csv.reader(sys.stdin):
    w.writerow(row[0:-1])' < input.csv > output.csv

If you want the output to contain say columns 2, 3, and 1, in that order, use

python -c 'import sys,csv
w = csv.writer(sys.stdout)
for row in csv.reader(sys.stdin):
    w.writerow([row[1], row[2], row[0]])' < input.csv > output.csv

Python array indexes start at 0, so the column numbers are counted starting at zero too, above; that's why they are 1, 2, 0 for columns 2, 3, 1, respectively.

2

sed:

sed 's/,[^,]\+$//' file.csv
  • ,[^,]\+ matches , followed by any number of characters except ,, and the match is replaced with empty string

awk:

awk -F, '{for(i=1; i<NF; i++) printf("%s%s", $i, FS); print ""}' file.csv
  • -F, sets field delimiter as ,

  • for(i=1; i<NF; i++) printf("%s", $i); print "" iterates over the fields except the last one, and print the output in desired format

Example:

% cat file.txt
1,"data",100.00,TRUE
2,"code",91.8,TRUE
3,"analytics",100.00,TRUE

% sed 's/,[^,]\+$//' file.txt                                     
1,"data",100.00
2,"code",91.8
3,"analytics",100.00

% awk -F, '{for(i=1; i<NF; i++) printf("%s%s", $i, FS); print ""}' file.txt
1,"data",100.00,
2,"code",91.8,
3,"analytics",100.00,
1

CSV format is sometimes tricky: CSV-parsers is the way to go.

Taking the simplistic approach, removing last field:

1) remove last field with Awk

awk 'BEGIN{FS=OFS=","}{NF--;print}'

2) regex with perl

perl -pe 's/.*\K,.*//'
1
cut -d "," -f 4 --complement input.csv > output.csv

Here, -d is followed by the delimiter i.e. semi-colon(;) within double quotes, and -f is followed by the number of the column to be removed i.e. 4. The column numbers are starting from 1 and proceed in this context.

0

just change the column number in this awk command. Below command removes the 3rd column. ( $3 )

awk -F, '{$3=""}1' OFS=, test.txt  | sed "s/^,//;s/,$//;s/,,/,/"
0

Very generic solution to remove number of last characters:

awk '{print substr($0, 1, length($0)-5)}' list1.txt
1,"data",100.00
2,"code",91.8
3,"analytics",100.00

-5 is the number of character will be removed

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.