Visit Our UNIX and Linux User Community


Filtering out lines in a .csv file


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Filtering out lines in a .csv file
# 1  
Old 02-27-2013
Filtering out lines in a .csv file

Hi Guys,

Would need your expert help with the following situation..

I have a comma seperated .csv file, with a header row and data as follows

Code:
H1,H2,H3,H4,H5.....    (header row)
0,0,0,0,0,1,2....         (data rows follow)
0,0,0,0,0,0,1
.........
.........

i need a code that would, trigger a set of instructions(if-then condition) .. based on the situation like .. if any of the rows in H5,H4,H3 .. but not H1,H2 has a value greater than 1.

thanks,
dev
# 2  
Old 02-27-2013
awk has builtin column names $1, $2, ... $n

What exactly are you trying to do? - building a pile of if-then-else constructs is possible in awk in a very simple way. Huge blocks of if-then-else are prone to errors as well

Please give us sample input -> expected output.
# 3  
Old 02-27-2013
You could use sed to delete heading line and then read columns into an Array. Remember bash arrays are indexed from zero, so H[0] is column 1, H[5] is column 6:

Code:
#!/bin/bash
IFS=,
sed '1d' infile | while read -a H
do
   if [ ${H[0]} -le 1 -a ${H[1]} -le 1 ] &&
      [ ${H[4]} -gt 1 -o ${H[3]} -gt 1 -o ${H[2]} -gt 1 ]
   then
      echo "set of instructions"
   fi
done
IFS=$' \t\n'

# 4  
Old 03-02-2013
Hi Jim,

the sample file looks like this .. there are 18 columns in total, out of which last 3 are amount columns(D1 D2 D3), having certain values.. nothing has to be changed in the file .. the requirement is to read the last 3 columns of the file and in case any row has a value greater than '1' .. in that case 'a set of instructions' have to be executed..

Code:
H1 H2 H3 H4 H5 H6 H7 H8 H9 H10 H11 H12 H13 H14 H15 D1 D2 D3
                                                  0   0   0
                                                  0   0   1
                                                  0   0   0
                                                  1   0   1
 
.....................
..............


thanks,
Dev
# 5  
Old 03-02-2013
Code:
awk '
{
        if ( NR == 1)
        {
                print
                next
        }
        if ( $16 > 1 || $17 > 1 || $18 > 1 )
        {
                # Put your set of instructions here
        }
} ' file

Note: Set field separator to comma if fields in your file are separated by comma:
Code:
awk -F, '

# 6  
Old 03-09-2013
thanks bipin, seems to be working but can this code be made a oneliner ?
# 7  
Old 03-09-2013
Quote:
Originally Posted by dev.devil.1983
thanks bipin, seems to be working but can this code be made a oneliner ?
Just put it in one line Smilie
Code:
awk -F, 'NR==1{print;next}$16>1||$17>1||$18>1{#Put your set of instructions here}' file


Previous Thread | Next Thread
Test Your Knowledge in Computers #920
Difficulty: Medium
The Unix time number is zero at the Unix epoch, and increases by exactly 86400 per day since the epoch.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Convert a horizontal lines to vertical lines in a csv file

Hi.. I need some help in converting the below horizontal lines to vertical lines format. can anyone help me on this. input file Hour,1,2,3,4,5 90RT,106,111,111,112,111 output file Hour,90RT 1,106 2,111 3,111 4,112 5,111 (3 Replies)
Discussion started by: Raghuram717
3 Replies

2. UNIX for Beginners Questions & Answers

Filtering records of a csv file based on a value of a column

Hi, I tried filtering the records in a csv file using "awk" command listed below. awk -F"~" '$4 ~ /Active/{print }' inputfile > outputfile The output always has all the entries. The same command worked for different users from one of the forum links. content of file I was... (3 Replies)
Discussion started by: sunilmudikonda
3 Replies

3. Shell Programming and Scripting

Need help with csv filtering

Hello everyone, i am stuck with a task i was meant to do so i came here. So i have a .csv file which structure is : year;temperature;precipitation 2012;32;483 2006;28;517 ... I want to note that it is in fact ";" not a space, which a new file named <old-name>-new.txt, the first line must ... (2 Replies)
Discussion started by: Needhelp123
2 Replies

4. Shell Programming and Scripting

Filtering log file with lines older than 10 days.

Hi, I am trying to compare epoch time in a huge log file (2 million lines) with todays date. I have to create two files one which has lines older than 10 days and another file with less than 10 days. I am using while do but it takes forever to complete the script. It would be helpful if you can... (12 Replies)
Discussion started by: shunya
12 Replies

5. Shell Programming and Scripting

Filtering data from text to csv

Hello, Is there a way to filerter data from a text file as shown below to a Column e.g. hostname nfsmount as two separate column. Currently I could get hostname and the mount is appearing below.. using this script #! /bin/bash for i in `cat fqdn.txt` do echo "$i ............ " >>... (3 Replies)
Discussion started by: Cy Pqa
3 Replies

6. Shell Programming and Scripting

Reading 2 CSV files and filtering data based on group

I have two CSV files in the following format: First file: GroupID, PID:TID, IP, Port Sample data: 0,1000:11,127.0.0.1,445 0,-1:-1,127.0.0.1,800 1,1000:11,127.0.0.1,445 1,-1:-1,127.0.0.1,900 2,1000:11,127.0.0.1,445 2,-1:-1,180.0.0.3,900 Second file: IP,Port,PID Sample data... (6 Replies)
Discussion started by: rakesh_arxmind
6 Replies

7. Shell Programming and Scripting

Removing lines of a .csv file

Hello, Does anyone have a one-liner to remove lines of a csv file if the value in a specific column is zero? For example, I have this file, 12345,COM,5,0,N,29.95,Y 12345,MOM,1,0,N,29.95,Y 12345,COM,4,0,N,9.99,Y 12345,MOM,0,2,N,9.99,Y 12345,REN,0,1,N,9.99,Y and I want to remove lines... (4 Replies)
Discussion started by: palex
4 Replies

8. Shell Programming and Scripting

Help with pulling / filtering data from a .csv

Good day Gurus, I have a csv file that contains an inventory of active servers. This csv file contains a well over a hundred systems (IBM, SUN, HP). It also contains those systems details. See below for an example hostA,invver,1.02,20100430 hostA,date,08/30/2010,06:18 hostA,use,"Unknown... (4 Replies)
Discussion started by: LuffyDMonkey
4 Replies

9. Homework & Coursework Questions

Displaying specific lines from a CSV file

1. The problem statement, all variables and given/known data: Display from a csv file, birthdays that occur today. If there are no birthdays today, the next one in the year. 2. Relevant commands, code, scripts, algorithms: The csv file is ordered from older to younger (ie. the most recent... (8 Replies)
Discussion started by: Adzi
8 Replies

10. Shell Programming and Scripting

Deleting Lines from .csv file

Hello All, I have a .csv file and I have to delete the selcted records stored in a vairable e.g echo $lname 7 88 91 94 97 100 103 106 I dont know how to pass the variable name to "sed" for deleting the $lname from a file can any one help as this is very urgent. $lname is changing the... (3 Replies)
Discussion started by: 009satya
3 Replies

Featured Tech Videos