Csv notes

From raju

list lines where column values equal something

Use csvsql from the python3-csvkit package.

Consider for example:

    % cat foo.csv 
    Itemname,Value,Description,Component
    1,2,3,4
    5,6,7,8
    9,10,11,12
    13,14,15,16
    17,18,19,20
    21,22,23,24
    25,26,27,28
    29,30,31,32
    33,34,35,abc
    37,38,39,40
    41,42,43,44
    45,46,47,48
    49,50,51,52
    53,54,55,56
    57,58,59,60
    61,62,63,64
    65,66,67,68
    69,70,71,72
    73,74,75,76
    77,78,79,80
    81,82,83,84
    85,86,87,88
    89,90,91,92
    93,94,95,96
    97,98,99,100
    

sample commands:

    % csvsql --query "select * from foo where component='abc'" foo.csv
    Itemname,Value,Description,Component
    33,34,35,abc
    
    % csvsql --query "select * from foo where component between 12 and 16" foo.csv
    Itemname,Value,Description,Component
    9,10,11,12
    13,14,15,16
    
    % csvsql --query "select * from foo where component > 40 and component < 50" foo.csv
    Itemname,Value,Description,Component
    41,42,43,44
    45,46,47,48
    

System Information:

    % which csvsql
    /usr/bin/csvsql
    
    % dpkg -S /usr/bin/csvsql
    python3-csvkit: /usr/bin/csvsql
    
    % dpkg -l python3-csvkit | grep ^ii | cut -c1-200
    ii  python3-csvkit 0.9.1-3      all          library of utilities for working with CSV (Python 3)
    

Ref:- https://unix.stackexchange.com/questions/80471/selecting-rows-in-a-csv-file-based-on-column-value

loop through files

    for fullname in /path/to/*.csv
    do
    fname="${fullname##*/}"
    fhead="${fname%.*}"
    echo $fullname
    cat $fullname | grep -v '^#'  | csvsql --query "select foo,bar from stdin"
    done
    

demonstrates | reading from standard input

change the order of columns

Reordering columns is not possible with cut. Instead use awk.

Consider for example

    $ cat foo.csv
    Itemname,Value,Description,Component
    1,2,3,4
    5,6,7,8
    9,10,11,12
    13,14,15,16
    17,18,19,20
    

We get same output for both "cut -f 1,2" or "cut -f 2,1"

    $ cut -f 1,2 -d ',' foo.csv
    Itemname,Value
    1,2
    5,6
    9,10
    13,14
    17,18
    
    $ cut -f 2,1 -d ',' foo.csv
    Itemname,Value
    1,2
    5,6
    9,10
    13,14
    17,18
    

Use awk to control the column order.

    $ awk -F "," '{print $2 "," $1}' foo.csv
    Value,Itemname
    2,1
    6,5
    10,9
    14,13
    18,17