Login or Register to Ask a Question and Join Our Community


UNIX for Dummies Questions & Answers

Extract a certain field from a CSV?

 
Thread Tools Search this Thread
Top Forums UNIX for Dummies Questions & Answers Extract a certain field from a CSV?
# 1  
Old 02-16-2011
Extract a certain field from a CSV
EDIT: This problem has been solved thanks to the help of scottn.

Okay, so I have a CSV. Let's say it has the following entries in it:

Jackie Chan,1954,M
Chuck Norris,1930,M
Bruce Lee,1940,M


How would I, for example, extract the gender out of a certain person, maybe based on the year of birth?

I'm new to unix but I want to use "awk -F," but I don't know what to do next.
Last edited by chickeneaterguy; 02-17-2011 at 05:35 AM..
# 2  
Old 02-16-2011
Quote:
How would I, for example, extract the gender out of a certain person
Probably with a painful operation Smilie


Quote:
maybe based on the year of birth
You want to "extract" the gender based on the person or the year of birth, or both?

Using , as a field separator, based on your input, the name would be in $1, the year of birth in $2, etc.

Code:
$2 == 1940 { print $3 }
$2 == 1940 { print $1, $3 }
$1 == "Bruce Lee" { print $3 }
$1 == "Bruce Lee" && $2 == 1940 { print $3 }

Not such a painful operation Smilie
This User Gave Thanks to Scott For This Post:
chickeneaterguy
# 3  
Old 02-16-2011
Thanks, is there a way I could do it with writing:
Code:
awk -F, '{(if $2 == 1940) print $3}' > gender.txt

note: I'd be writing this into a file/script then running it as a parameter. Say the above code is titled genderExtract.sh, could I do this:

Code:
sh genderExtract.sh listofpeeps.csv

EDIT: I'm just doing this to get the gist of using -F and I don't want to do it through command line. I don't need to make a program to extract genders...but I could see that being useful for surveying purposes.
# 4  
Old 02-16-2011
Sounds like you want genderExtract.sh to be
Code:
#!/usr/bin/awk -F, -f
{ print $3 }

Then you would run it as:

Code:
$ ./genderExtract.sh input_file

The .sh extension is superfluous, and don't run it as sh genderExtract.sh input_file - just make genderExtract.sh executable, and run it as shown (./genderExtract.sh, not sh genderExtract.sh).

Otherwise, just write a plain old script as

Code:
#!/usr/bin/sh
awk -F, '......'  "$1"

and run it in the same way:
Code:
$ ./genderExtract.sh input_file

This User Gave Thanks to Scott For This Post:
chickeneaterguy
# 5  
Old 02-16-2011
Quote:
Originally Posted by scottn
Otherwise, just write a plain old script as

Code:
#!/usr/bin/sh
awk -F, '......'  "$1"

and run it in the same way:
Code:
$ ./genderExtract.sh input_file

So if I just wrote the following script it would work, right?

Code:
#!/usr/bin/sh
awk -F, '{(if $2 == 1940) print $3}' > gender.txt

EDIT: Would that even output to the file(gender.txt)?
# 6  
Old 02-16-2011
No. Your syntax is a bit off, and you're missing an input file.

Code:
awk -F, '{ if ($2 == 1940) print $3}' "$1" > gender.txt

where $1 is an argument to the script, like:

Code:
$ ./genderExtract.sh "some input file"

You should check in your script that "$1" was given.
# 7  
Old 02-16-2011
Quote:
Originally Posted by scottn
No. Your syntax is a bit off, and you're missing an input file.

Code:
awk -F, '{ if ($2 == 1940) print $3}' "$1" > gender.txt

Okay, so for this example, the $1 will be the input file parameter, but the $2 and $3 inside of single quotes will be the 2nd and 3rd columns, respectively in the actual $1 file?

Quote:
Originally Posted by scottn
where $1 is an argument to the script, like:

Code:
$ ./genderExtract.sh "some input file"

You should check in your script that "$1" was given.
Okay, thanks. If I wanted the file to be constant and not a parameter, could I just have:

Code:
awk -F, '{ if ($2 == 1940) print $3}' "listofpeeps.csv" > gender.txt

?

Thanks a lot for your help btw. I'm learning a lot.
 
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Match columns from two csv files and update field in one of the csv file

Hi, I have a file of csv data, which looks like this: file1: 1AA,LGV_PONCEY_LES_ATHEE,1,\N,1,00020460E1,0,\N,\N,\N,\N,2,00.22335321,0.00466628 2BB,LES_POUGES_ASF,\N,200,200,00006298G1,0,\N,\N,\N,\N,1,00.30887539,0.00050312... (10 Replies)
Discussion started by: djoseph
10 Replies

2. Linux

How do I format a Date field of a .CSV file with multiple commas in a string field?

I have a .CSV file (file.csv) whose data are all enclosed in double quotes. Sample format of the file is as below: column1,column2,column3,column4,column5,column6, column7, Column8, Column9, Column10 "12","B000QRIGJ4","4432","string with quotes, and with a comma, and colon: in... (3 Replies)
Discussion started by: dhruuv369
3 Replies

3. Shell Programming and Scripting

Update field value on a csv file

Hi I have a job status csv file. I want to update the status of the job in the file. Below is the csv file 1,jobname1,in_progress,starttime,somthing,somthing 2,jobname2,completed,starttime,somthing,somthing 3,jobname3,failed,starttime,somthing,somthing... (8 Replies)
Discussion started by: midhun19
8 Replies

4. Shell Programming and Scripting

Extracting field values from .csv

How can I select the bold fields from the following? "CLLI","SWREL","RPTDATE","RPTIME","TZ","RPTTYPE","RPTPD","IVALDATE","IVALSTART","IVALEND","NUMENTIDS" "tklc9010801","EAGLE5 45.0.0-64.70.1","2013-08-07","02:01:50","MST ","COMPONENT MEASUREMENTS ON... (4 Replies)
Discussion started by: leghorn
4 Replies

5. Shell Programming and Scripting

extract data in a csv file based on a certain field.

I have a csv file that I need to extract some data from depending on another field after reading info from another text file. The text file would say have 592560 in it. The csv file may have some data like so Field 1 Field2 Field3 Field4 Field5 Field6 20009756 1 ... (9 Replies)
Discussion started by: GroveTuckey
9 Replies

6. Shell Programming and Scripting

extract .csv file

Hi all I am new to unix . I need to write a script that extracts some data from oracle into a .csv file with heading of the columns in the file SO i created the following two scripts but they are not working ac.sql (this is the sql file that i will call inside the shell script when i run... (1 Reply)
Discussion started by: rajesh_tns
1 Replies

7. Shell Programming and Scripting

Extract Values from CSV

Hi, I need to extract values from a CSV file based on some conditions as explained below: File format details: 1. each set starts with AAA only 2. number of columns is fixed 3. number of rows per set may vary (as they are having different CCC rows) Now, i need to extract 3rd column of... (3 Replies)
Discussion started by: prvnrk
3 Replies

8. Shell Programming and Scripting

replace a field in a CSV file

Hello all, I've a CSV file and need to replace 5th field if its value is "X". The exact requirement is to replace 5th field (column) with "Y" if a. it's value is "X" AND b. the line must start with ABC string i guess this can be done with awk. Pl help. For security reasons, the... (2 Replies)
Discussion started by: prvnrk
2 Replies

9. UNIX for Dummies Questions & Answers

How to insert ' in a field in CSV file

print "count,pub,prodline,group,sector,date,source" > $fname cat sp_log.summary.$firstday-$lastday.ProdlineSector | sed "s/^ *//g;s/ *$//g" >sp_log.summary.$firstday-$lastday cat sp_log.summary.$firstday-$lastday | sed "s/$/ $lastyy-$lastmm cache/;s/ /,/g" >> $fname cat $fname | sed... (1 Reply)
Discussion started by: shikhakaul
1 Replies

10. Linux

Replace field in csv

Hi, I need to replace a field (field 5) in a csv file, based on the content of another field (field 2), something like this: actual file: field1, field2, filed3, field4, field5, field6 01,232,abb-pan,679,,pan 02,565,cdf-pan,683,,pan the result should be: ... (4 Replies)
Discussion started by: pcboss
4 Replies
Login or Register to Ask a Question