169

If I have a csv file, is there a quick bash way to print out the contents of only any single column? It is safe to assume that each row has the same number of columns, but each column's content would have different length.

jww
  • 97,681
  • 90
  • 411
  • 885
user788171
  • 16,753
  • 40
  • 98
  • 125

17 Answers17

198

You could use awk for this. Change '$2' to the nth column you want.

awk -F "\"*,\"*" '{print $2}' textfile.csv
synthesizerpatel
  • 27,321
  • 5
  • 74
  • 91
  • 24
    `echo '1,"2,3,4,5",6' | awk -F "\"*,\"*" '{print $2}'` will print `2` instead of `2,3,4,5`. – Igor Mikushkin Mar 10 '15 at 15:06
  • If you are a lucky guy using GNU Tools in Windows, you can execute the same comand as @IgorMikushkin as follows: `gawk -F"|" "{print $13}" files*.csv` – Elidio Marquina May 25 '17 at 18:33
  • 19
    I think this fails when there are strings that contain a comma, i.e. `...,"string,string",...` – sodiumnitrate Jun 13 '17 at 19:18
  • 1
    I think for the 1st and last colume, this will have some flaw. The first column will start with `"` and last will end with `"` – BigTailWolf Oct 19 '18 at 00:15
  • 3
    Some programs return CSV files with different delimiters, so it may be required to change the regular expression accordingly. Example for a semicolon delimiter: `awk -F "\"*;\"*" '{print $2}' textfile.csv` – gekkedev May 30 '20 at 23:04
  • Doesn't work when there are escaped `"` in the data (encoded as `""`) – doug65536 Oct 05 '22 at 22:25
148

yes. cat mycsv.csv | cut -d ',' -f3 will print 3rd column.

madrag
  • 1,685
  • 1
  • 9
  • 4
  • 15
    Unless column two contains a comma in which case you'd get the second half of column two. Case in point ,"3,000",. My answer isn't much better with respect to that problem though. So don't be bummed out. – synthesizerpatel Oct 26 '13 at 02:40
  • @synthesizerpatel I agree better to use `awk` – MattSizzle Oct 26 '13 at 02:47
  • 2
    We are not sure that his CSV file contains doubles quotes to differentiate the differents values. It would be better that he provide an input file so that we can assess the most appropriate solution. – Idriss Neumann Oct 26 '13 at 14:10
  • 1
    That is the standard though. It would be an ill-formed csv if it didn't. – Andrew Jun 30 '21 at 19:54
  • 1
    fail with '1,"a,b,c",3,4' – Valerio Mar 21 '22 at 13:53
97

The simplest way I was able to get this done was to just use csvtool. I had other use cases as well to use csvtool and it can handle the quotes or delimiters appropriately if they appear within the column data itself.

csvtool format '%(2)\n' input.csv

Replacing 2 with the column number will effectively extract the column data you are looking for.

Samar
  • 1,865
  • 12
  • 13
  • 30
    This should be the accepted answer. This tool knows how to deal with CSV files, well beyond treating a comma as a field separator. To extract 2nd column, "csvtool col 2 input.csv" – Vladislavs Dovgalecs Oct 28 '16 at 02:52
  • 5
    Just a heads up... if you want to use csvtool with standard input (example csv comes from another command) it's something like this `cat input.csv | csvtool formath '%(2)\n' -` Note I know cat here is useless but sub it for any command that would normally export a csv. – General Redneck Mar 30 '18 at 21:53
  • 1
    It there are multiline fields, the `format '%(2)\n'` command could not tell where one field ends. (csvtool 1.4.2) – jarno Jan 26 '19 at 14:12
  • 1
    Newer versions of `csvtool` seem to require using `-` as the input filename to read from stdin. – Connor Clark Oct 09 '19 at 01:10
  • @GeneralRedneck why use cat? and it's format not formath `csvtool format '%(1),%(10)\n' - < in.csv > out.csv` – jg6 May 21 '20 at 16:09
  • 1
    Reiterate what I put in my comment. > Note I know cat here is useless but sub it for any command that would normally export a csv. – In other words i used it as an example for some other command that may output csv content (like drush). Unfortunately I can't edit my comment after 2 years so its forever typoed – General Redneck May 23 '20 at 01:49
  • 1
    As a note for those not on Ubuntu, you can install csvtool from pip as well – kgrimes2 Dec 15 '20 at 21:11
  • work with '1,"a,b,c",3,4' – Valerio Mar 21 '22 at 13:53
  • This gem of a tool is useful in not only selecting columns but permuting them as well: for e.g. `csvtool -c 3,8,7,5 input.csv > output.csv` – RT Denver Sep 12 '22 at 04:12
  • @RTDenver which version supports `-c`? I only see `col`. – Michel de Ruiter Nov 23 '22 at 09:48
  • Fedora 37: `sudo dnf install ocaml-csv` -- `csvtool` does not exist, `dnf install csv` is a perl-based solution. The Ubuntu csvtool is actually https://ocaml.org/p/csvtool/latest -- and that's what you get with `ocaml-csv` on Fedora. – Dr. Jan-Philip Gehrcke Apr 02 '23 at 11:08
19

Landed here looking to extract from a tab separated file. Thought I would add.

cat textfile.tsv | cut -f2 -s

Where -f2 extracts the 2, non-zero indexed column, or the second column.

cevaris
  • 5,671
  • 2
  • 49
  • 34
15

I think the easiest is using csvkit:

Gets the 2nd column: csvcut -c 2 file.csv

However, there's also csvtool, and probably a number of other csv bash tools out there:

sudo apt-get install csvtool (for Debian-based systems)

This would return a column with the first row having 'ID' in it. csvtool namedcol ID csv_file.csv

This would return the fourth row: csvtool col 4 csv_file.csv

If you want to drop the header row:

csvtool col 4 csv_file.csv | sed '1d'

wordsforthewise
  • 13,746
  • 5
  • 87
  • 117
  • 1
    The advantage of this method over a simple `awk` CLI is that it correctly parses the CSV file and handles `"` escaping and newlines for you: https://stackoverflow.com/questions/36287982/bash-parse-csv-with-quotes-commas-and-newlines/76604236#76604236 – Ciro Santilli OurBigBook.com Jul 03 '23 at 11:20
13

Here is a csv file example with 2 columns

myTooth.csv

Date,Tooth
2017-01-25,wisdom
2017-02-19,canine
2017-02-24,canine
2017-02-28,wisdom

To get the first column, use:

cut -d, -f1 myTooth.csv

f stands for Field and d stands for delimiter

Running the above command will produce the following output.

Output

Date
2017-01-25
2017-02-19
2017-02-24
2017-02-28

To get the 2nd column only:

cut -d, -f2 myTooth.csv

And here is the output Output

Tooth
wisdom
canine
canine
wisdom
incisor

Another use case:

Your csv input file contains 10 columns and you want columns 2 through 5 and columns 8, using comma as the separator".

cut uses -f (meaning "fields") to specify columns and -d (meaning "delimiter") to specify the separator. You need to specify the latter because some files may use spaces, tabs, or colons to separate columns.

cut -f 2-5,8 -d , myvalues.csv

cut is a command utility and here is some more examples:

SYNOPSIS
     cut -b list [-n] [file ...]
     cut -c list [file ...]
     cut -f list [-d delim] [-s] [file ...]
Stryker
  • 5,732
  • 1
  • 57
  • 70
9

First we'll create a basic CSV

[dumb@one pts]$ cat > file 
a,b,c,d,e,f,g,h,i,k  
1,2,3,4,5,6,7,8,9,10  
a,b,c,d,e,f,g,h,i,k  
1,2,3,4,5,6,7,8,9,10

Then we get the 1st column

[dumb@one pts]$  awk -F , '{print $1}' file  
a  
1  
a  
1
Jonathan
  • 10,792
  • 5
  • 65
  • 85
7

Many answers for this questions are great and some have even looked into the corner cases. I would like to add a simple answer that can be of daily use... where you mostly get into those corner cases (like having escaped commas or commas in quotes etc.,).

FS (Field Separator) is the variable whose value is dafaulted to space. So awk by default splits at space for any line.

So using BEGIN (Execute before taking input) we can set this field to anything we want...

awk 'BEGIN {FS = ","}; {print $3}'

The above code will print the 3rd column in a csv file.

router
  • 582
  • 5
  • 16
6

The other answers work well, but since you asked for a solution using just the bash shell, you can do this:

AirBoxOmega:~ d$ cat > file #First we'll create a basic CSV
a,b,c,d,e,f,g,h,i,k
1,2,3,4,5,6,7,8,9,10
a,b,c,d,e,f,g,h,i,k
1,2,3,4,5,6,7,8,9,10
a,b,c,d,e,f,g,h,i,k
1,2,3,4,5,6,7,8,9,10
a,b,c,d,e,f,g,h,i,k
1,2,3,4,5,6,7,8,9,10
a,b,c,d,e,f,g,h,i,k
1,2,3,4,5,6,7,8,9,10
a,b,c,d,e,f,g,h,i,k
1,2,3,4,5,6,7,8,9,10

And then you can pull out columns (the first in this example) like so:

AirBoxOmega:~ d$ while IFS=, read -a csv_line;do echo "${csv_line[0]}";done < file
a
1
a
1
a
1
a
1
a
1
a
1

So there's a couple of things going on here:

  • while IFS=, - this is saying to use a comma as the IFS (Internal Field Separator), which is what the shell uses to know what separates fields (blocks of text). So saying IFS=, is like saying "a,b" is the same as "a b" would be if the IFS=" " (which is what it is by default.)

  • read -a csv_line; - this is saying read in each line, one at a time and create an array where each element is called "csv_line" and send that to the "do" section of our while loop

  • do echo "${csv_line[0]}";done < file - now we're in the "do" phase, and we're saying echo the 0th element of the array "csv_line". This action is repeated on every line of the file. The < file part is just telling the while loop where to read from. NOTE: remember, in bash, arrays are 0 indexed, so the first column is the 0th element.

So there you have it, pulling out a column from a CSV in the shell. The other solutions are probably more practical, but this one is pure bash.

drldcsta
  • 413
  • 3
  • 8
6

I needed proper CSV parsing, not cut / awk and prayer. I'm trying this on a mac without csvtool, but macs do come with ruby, so you can do:

echo "require 'csv'; CSV.read('new.csv').each {|data| puts data[34]}" | ruby
Darth Egregious
  • 18,184
  • 3
  • 32
  • 54
5

You could use GNU Awk, see this article of the user guide. As an improvement to the solution presented in the article (in June 2015), the following gawk command allows double quotes inside double quoted fields; a double quote is marked by two consecutive double quotes ("") there. Furthermore, this allows empty fields, but even this can not handle multiline fields. The following example prints the 3rd column (via c=3) of textfile.csv:

#!/bin/bash
gawk -- '
BEGIN{
    FPAT="([^,\"]*)|(\"((\"\")*[^\"]*)*\")"
}
{
    if (substr($c, 1, 1) == "\"") {
        $c = substr($c, 2, length($c) - 2) # Get the text within the two quotes
        gsub("\"\"", "\"", $c)  # Normalize double quotes
    }
    print $c
}
' c=3 < <(dos2unix <textfile.csv)

Note the use of dos2unix to convert possible DOS style line breaks (CRLF i.e. "\r\n") and UTF-16 encoding (with byte order mark) to "\n" and UTF-8 (without byte order mark), respectively. Standard CSV files use CRLF as line break, see Wikipedia.

If the input may contain multiline fields, you can use the following script. Note the use of special string for separating records in output (since the default separator newline could occur within a record). Again, the following example prints the 3rd column (via c=3) of textfile.csv:

#!/bin/bash
gawk -- '
BEGIN{
    RS="\0" # Read the whole input file as one record;
    # assume there is no null character in input.
    FS="" # Suppose this setting eases internal splitting work.
    ORS="\n####\n" # Use a special output separator to show borders of a record.
}
{
    nof=patsplit($0, a, /([^,"\n]*)|("(("")*[^"]*)*")/, seps)
    field=0;
    for (i=1; i<=nof; i++){
        field++
        if (field==c) {
            if (substr(a[i], 1, 1) == "\"") {
                a[i] = substr(a[i], 2, length(a[i]) - 2) # Get the text within 
                # the two quotes.
                gsub(/""/, "\"", a[i])  # Normalize double quotes.
            }
            print a[i]
        }
        if (seps[i]!=",") field=0
    }
}
' c=3 < <(dos2unix <textfile.csv)

There is another approach to the problem. csvquote can output contents of a CSV file modified so that special characters within field are transformed so that usual Unix text processing tools can be used to select certain column. For example the following code outputs the third column:

csvquote textfile.csv | cut -d ',' -f 3 | csvquote -u

csvquote can be used to process arbitrary large files.

jarno
  • 787
  • 10
  • 21
5

I wonder why none of the answers so far have mentioned csvkit.

csvkit is a suite of command-line tools for converting to and working with CSV

csvkit documentation

I use it exclusively for csv data management and so far I have not found a problem that I could not solve using cvskit.

To extract one or more columns from a cvs file you can use the csvcut utility that is part of the toolbox. To extract the second column use this command:

csvcut -c 2 filename_in.csv > filename_out.csv 

csvcut reference page

If the strings in the csv are quoted, add the quote character with the q option:

csvcut -q '"' -c 2 filename_in.csv > filename_out.csv 

Install with pip install csvkit or sudo apt install csvkit.

Suzana
  • 4,251
  • 2
  • 28
  • 52
Soundbytes
  • 1,149
  • 9
  • 17
4
csvtool col 2 file.csv 

where 2 is the column you are interested in

you can also do

csvtool col 1,2 file.csv 

to do multiple columns

exussum
  • 18,275
  • 8
  • 32
  • 65
3

Simple solution using awk. Instead of "colNum" put the number of column you need to print.

cat fileName.csv | awk -F ";" '{ print $colNum }'
Sav K0
  • 39
  • 2
1

If you know your data will not be quoted, then any solution that splits on , will work well (I tend to reach for cut -d, -f1 | sed 1d), as will any of the CSV manipulation tools.

If you want to produce another CSV file, then xsv, csvkit, csvtool, or other CSV manipulation tools are appropriate.

If you want to extract the contents of one single column of a CSV file, unquoting them so that they can be processed by subsequent commands, this Python 1-liner does the trick for CSV files with headers:

python -c 'import csv,sys'$'\n''for row in csv.DictReader(sys.stdin): print(row["message"])'

The "message" inside of the print function selects the column.

If the CSV file doesn't have headers:

python -c 'import csv,sys'$'\n''for row in csv.reader(sys.stdin): print(row[1])'

Python's CSV library supports all kinds of CSV dialects, so if your CSV file uses different conventions, it's possible to support them with relatively little change to the code.

j3h
  • 692
  • 5
  • 10
0

Been using this code for a while, it is not "quick" unless you count "cutting and pasting from stackoverflow".

It uses ${##} and ${%%} operators in a loop instead of IFS. It calls 'err' and 'die', and supports only comma, dash, and pipe as SEP chars (that's all I needed).

err()  { echo "${0##*/}: Error:" "$@" >&2; }
die()  { err "$@"; exit 1; }

# Return Nth field in a csv string, fields numbered starting with 1
csv_fldN() { fldN , "$1" "$2"; }

# Return Nth field in string of fields separated
# by SEP, fields numbered starting with 1
fldN() {
        local me="fldN: "
        local sep="$1"
        local fldnum="$2"
        local vals="$3"
        case "$sep" in
                -|,|\|) ;;
                *) die "$me: arg1 sep: unsupported separator '$sep'" ;;
        esac
        case "$fldnum" in
                [0-9]*) [ "$fldnum" -gt 0 ] || { err "$me: arg2 fldnum=$fldnum must be number greater or equal to 0."; return 1; } ;;
                *) { err "$me: arg2 fldnum=$fldnum must be number"; return 1;} ;;
        esac
        [ -z "$vals" ] && err "$me: missing arg2 vals: list of '$sep' separated values" && return 1
        fldnum=$(($fldnum - 1))
        while [ $fldnum -gt 0 ] ; do
                vals="${vals#*$sep}"
                fldnum=$(($fldnum - 1))
        done
        echo ${vals%%$sep*}
}

Example:

$ CSVLINE="example,fields with whitespace,field3"
$ $ for fno in $(seq 3); do echo field$fno: $(csv_fldN $fno "$CSVLINE");  done
field1: example
field2: fields with whitespace
field3: field3
qneill
  • 1,643
  • 14
  • 18
0

You can also use while loop

IFS=,
while read name val; do
        echo "............................"

        echo Name: "$name"
done<itemlst.csv
K.Sopheak
  • 22,904
  • 4
  • 33
  • 78
  • This code produces a Shellcheck warning: [SC2034](https://github.com/koalaman/shellcheck/wiki/SC2034). Search returns this question as the first result when looking for ways to sidestep the warning. – jww Dec 08 '19 at 00:20