6

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
  • 2
    What's wrong with having no headers? – countermode Oct 19 '16 at 6:32
  • 1
    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
  • Almost the same question: unix.stackexchange.com/questions/34646/… – Alexander Pozdneev Jul 29 at 7:23
21

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
| improve this answer | |
  • This is incorrect! CSV files may have multiline cells and those would be broken by this command. – adius Jan 17 at 10:54
  • @adius yes, the first command is pretty simple and can be broken by newlines, commas in quotes, etc. The second command handles a much wider range of CSV quirks, including multi-line cells. – James Jan 18 at 11:25
4

I suggest using miller (AKA mlr). Miller is a small (< 1 MB) program written in C (without dependencies) for processing tabular data in a variety of formats (e.g., TSV, CSV, tabular JSON, key-value pairs) in a streaming fashion. It is available from most Linux distros' repositories (usually under the name miller). Its CSV parser handles multi-line cells (i.e., cells with embedded newline characters). Although it assumes CSV files with headers, it has options (--implicit-csv-header and --headerless-csv-output) that handle header-less CSV files. For your use-case, assuming you want to remove the fourth column (column indexing begins with 1), you could do something like

mlr --csv --implicit-csv-header --headerless-csv-output cut -x -f 4 input.csv > output.csv

If you want to remove the last column without having to count them, you could reverse each CSV line prior to processing the data, and reverse them again afterwards, like so:

< input.csv rev |
mlr --csv --implicit-csv-header --headerless-csv-output cut -x -f 1 |
rev > output.csv

Hope that helps.

| improve this answer | |
3

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,
| improve this answer | |
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.

| improve this answer | |
2

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,.*//'
| improve this answer | |
1

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/,,/,/"
| improve this answer | |
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.

The --complement option indicates the complement of the selection. (This option enables the cut command to display all the bytes/characters or fields except the selected bytes)

In this scenario, the complement of the cut operation is all the columns delimited by , except the 4th column.

| improve this answer | |
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

| improve this answer | |
  • 1
    this will break the csv if there's any FALSE values. – Boris Apr 26 at 14:38

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.