Filtering CSV file based on string name

Question

I'm trying to get specific columns of a csv file (that Header contains "SOF" in case). Is a large file and i need to copy this columns to another csv file using Shell.

I've tried something like this:

#!/bin/bash
awk ' {
i=1
j=1

while ( NR==1 )
  if ( "$i" ~ /SOF/ )
  then
   array[j] = $i
   $j += 1
  fi
$i += 1

for ( k in array ) 
 print array[k]

}' fil1.csv > result.csv

In this case i've tried to save the column numbers that contains "SOF" in the header in an array. After that copy the columns using this numbers.

note that looping through arrays in awk is done like this: for (item in array). Your are currently using a Basy syntax instead. — fedorqui
– fedorqui, Commented Jan 7, 2017 at 15:50
You completely misunderstand awk syntax and semantics. Awk is not shell, it is a completely different tool with it's own language. Get the book Effective Awk Programming, 4th Edition, by Arnold Robbins and start working through the first few examples. — Ed Morton
– Ed Morton, Commented Jan 7, 2017 at 16:07
Thanks @fedorqui , but still does not working :( , i think my logic is right but the result csv is empty, and the script is running in a loop... — Denys Wylliam
– Denys Wylliam, Commented Jan 7, 2017 at 16:10
As @EdMorton suggested, start with a small example, since you are mixing many basic concepts here. — fedorqui
– fedorqui, Commented Jan 7, 2017 at 16:14
The keywords contains, then and fi are not keywords in awk. You probably need a regex and ~ for contains; the rest just needs converting from shell-ish to Awk. Also, $i in Awk is very different from $i in shell. — Jonathan Leffler
– Jonathan Leffler, Commented Jan 7, 2017 at 16:24

xhienne · Accepted Answer · 2017-01-08 03:52:28Z

1

Preliminary note: contrary to what one may infer from the code included in the OP, the values in the CSV are delimited with a semicolon.

Here is a solution with two separate commands:

the first parses the first line of your CSV file and identifies which fields must be exported. I use awk for this.
the second only prints the fields. I use cut for this (simpler syntax and quicker than awk, especially if your file is large)

The idea is that the first command yields a list of field numbers, separated with ",", suited to be passed as parameter to cut:

# Command #1: identify fields
fields=$(awk -F";" '
    {
        for (i = 1; i <= NF; i++)
            if ($i ~ /SOF/) {
                fields = fields sep i
                sep = ","
            }
        print fields
        exit
     }' fil1.csv
)

# Command #2: export fields
{ [ -n "$fields" ] && cut -d";" -f "$fields" fil1.csv; } > result.csv

edited Jan 8, 2017 at 3:52

answered Jan 7, 2017 at 18:44

xhienne

6,2141 gold badge18 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

11 Comments

Denys Wylliam Over a year ago

The result.csv file have the same content of fil1.csv when i compile the script

xhienne Over a year ago

@LorD Can you echo "$fields" please?

Denys Wylliam Over a year ago

Using echo to show the content of the variable fields, just return 1.

Denys Wylliam Over a year ago

The first column of the file don't contains SOF

xhienne Over a year ago

@LorD I tested my solution with gawk with and without POSIX mode (--posix) and it worked. What is your awk?

|

karakfa · Accepted Answer · 2017-01-07 17:39:15Z

0

try something like this...

$ awk 'BEGIN {FS=OFS=","} 
       NR==1 {for(i=1;i<=NF;i++) if($i~/SOF/) {col=i; break}} 
             {print $col}' file

there is no handling if the sought out header doesn't exist so should print the whole line.

answered Jan 7, 2017 at 17:39

karakfa

67.8k8 gold badges45 silver badges59 bronze badges

1 Comment

xhienne Over a year ago

Apparently, OP wants to select all the fields named "...SOF...", not just one

Shubham_K · Accepted Answer · 2017-01-07 19:15:28Z

0

This link might be helpful for you :

One of the useful commands you probably need is "cut"

cut -d , -f 2 input.csv

Here number 2 is the column number you want to cut from your csv file.

answered Jan 7, 2017 at 19:15

Shubham_K

985 bronze badges

Comments

Vicky · Accepted Answer · 2017-01-08 07:30:10Z

0

try this one out :

awk '{for(i=1;i<=NF;i++)a[i]=a[i]" "$i}END{for (i in a ){ print a[i] } }'  filename  | grep SOF | awk '{for(i=1;i<=NF;i++)a[i]=a[i]" "$i}END{for (i in a ){ print a[i] } }'

answered Jan 8, 2017 at 7:30

Vicky

1,3581 gold badge17 silver badges38 bronze badges

1 Comment

Vicky Over a year ago

the grep SOF does not look for SOF in the first occurrence so youmay want to replace it with awk '/$1 ~ "SOF"/{print $0}' and the full commands becomes like this : awk '{for(i=1;i<=NF;i++)a[i]=a[i]" "$i}END{for (i in a ){ print a[i] } }' SOF.txt | awk '/$1 ~ "SOF"/{print $0}' | awk '{for(i=1;i<=NF;i++)a[i]=a[i]" "$i}END{for (i in a ){ print a[i] } }'

Collectives™ on Stack Overflow

Filtering CSV file based on string name

4 Answers 4

11 Comments

1 Comment

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

11 Comments

1 Comment

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related