Visit The New, Modern Unix Linux Community


Script for extracting data from csv file based on column values.


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Script for extracting data from csv file based on column values.
# 1  
Script for extracting data from csv file based on column values.

Hi all,

I am new to shell script.I need your help to write a shell script.
I need to write a shell script to extract data from a .csv file where columns are ',' separated.
The file has 5 columns having values say column 1,column 2.....column 5 as below along with their valuesm.
ref_num,Account Number,Misc, error 1,error 2
Code:
COD  , 121, misc, 0, 0
COD1, 123, misc2,1, 1

Now i want to write a script to first check if the file name.csv exists at /home/home1.
If the file exists i want to display column 1 to column 5 header along with their values only if the column error 1 or error 2 column has any other value than 0 i.e it should not have 0.

Output for above input should be like below:
Code:
ref_num:COD1
Account Number:123
Misc:misc2
error 1:1
error 2:1


Last edited by Scott; 08-29-2013 at 01:25 AM.. Reason: Use code tags, please...
# 2  
so what did you try? Here are hints.

-f FILENAME can be used to identify if the file is exist or not.

awk can be used to get the report.
# 3  
I tried below but seems some problem.
Code:
awk -F, '(NR==1){h1=$1;h2=$2;h3=$3;h4=$4;h5=$5;next} ($4||$5){print h1":"$1"\n"h2":"$2"\n"h3":"$3"\n"h4":"$4"\n"h5":"$5"\n"}' file


Last edited by Scott; 08-29-2013 at 01:26 AM.. Reason: Code tags
# 4  
Code:
#!/usr/bin/bash
FILE=/home/home1/name.csv
if [ ! -f $FILE ] ; then
  echo " file $FILE is not exist"
  exit
fi

awk -F , 'int($4)!="0"||int($5)!="0" {printf "ref_num:%s\nAccount Number:%s\nMisc:%s\nerror 1:%s\nerror2:%s\n",$1,$2,$3,$4,$5}' $FILE


Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #399
Difficulty: Medium
The 11i v2 release of HP-UX introduced kernel-based intrusion detection, strong random number generation, stack buffer overflow protection, security partitioning, role-based access management, and various open-source security tools.
True or False?

9 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Extracting values based on line-column numbers from multiple text files

Dear All, I have to solve the following problems with multiple tab-separated text file but I don't know how. Any help would be greatly appreciated. I have access to Linux mint (but not as a professional). I have multiple tab-delimited files with the following structure: file1: 1 44 2 ... (5 Replies)
Discussion started by: Bastami
5 Replies

2. Linux

To get all the columns in a CSV file based on unique values of particular column

cat sample.csv ID,Name,no 1,AAA,1 2,BBB,1 3,AAA,1 4,BBB,1 cut -d',' -f2 sample.csv | sort | uniq this gives only the 2nd column values Name AAA BBB How to I get all the columns of CSV along with this? (1 Reply)
Discussion started by: sanvel
1 Replies

3. Shell Programming and Scripting

Remove the values from a certain column without deleting the Column name in a .CSV file

(14 Replies)
Discussion started by: dhruuv369
14 Replies

4. Shell Programming and Scripting

Fetching values in CSV file based on column name

input.csv: Field1,Field2,Field3,Field4,Field4 abc ,123 ,xyz ,000 ,pqr mno ,123 ,dfr ,111 ,bbb output: Field2,Field4 123 ,000 123 ,111 how to fetch the values of Field4 where Field2='123' I don't want to fetch the values based on column position. Instead want to... (10 Replies)
Discussion started by: bharathbangalor
10 Replies

5. Linux

Filter a .CSV file based on the 5th column values

I have a .CSV file with the below format: "column 1","column 2","column 3","column 4","column 5","column 6","column 7","column 8","column 9","column 10 "12310","42324564756","a simple string with a , comma","string with or, without commas","string 1","USD","12","70%","08/01/2013",""... (2 Replies)
Discussion started by: dhruuv369
2 Replies

6. UNIX for Dummies Questions & Answers

Shell script to extract data from csv file based on certain conditions

Hi Guys, I am new to shell script.I need your help to write a shell script. I need to write a shell script to extract data from a .csv file where columns are ',' separated. The file has 5 columns having values say column 1,column 2.....column 5 as below along with their valuesm.... (1 Reply)
Discussion started by: Vivekit82
1 Replies

7. UNIX for Dummies Questions & Answers

Extracting rows from a space delimited text file based on the values of a column

I have a space delimited text file. I want to extract rows where the third column has 0 as a value and write those rows into a new space delimited text file. How do I go about doing that? Thanks! (2 Replies)
Discussion started by: evelibertine
2 Replies

8. UNIX for Dummies Questions & Answers

Extracting rows from a text file based on numerical values of a column

I have a text file where the second column is a list of numbers going from small to large. I want to extract the rows where the second column is smaller than or equal to 0.0001. My input: rs10082730 9e-08 12 46002702 rs2544081 1e-07 12 46015487 rs1425136 1e-06 7 35396742 rs2712590... (1 Reply)
Discussion started by: evelibertine
1 Replies

9. Shell Programming and Scripting

extracting data from a .csv file

I have a .csv file equipment,bandtype abc,aws def,mmds ghi,umts jkl,mmds I can get the equipment from `hostname`. In my script i want to check what is the hostname. then see if it exists in the.csv file. if it does then i want to store the second parameter(bandtype) for the corresponding... (3 Replies)
Discussion started by: lassimanji
3 Replies

Featured Tech Videos