Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Splitting file based on column values

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Splitting file based on column values
# 1  
Old 02-07-2012
Splitting file based on column values
Hi all,
I have a file (say file.txt) which contains comma-separated rows. Each row has seven columns. Only column 4 or 5 (not both) can have empty values like "" in each line.
Sample lines
Quote:
"Jay","M","1","2","","3","B"
"Ann","F","4","","5","6","A"
So, now i want all the rows that have column 4 as "" go in file1.txt and all the rows that have column 5 as "" go in file2.txt.
i.e row Jay should go in file2.txt and row Ann should go in file1.txt

Thanks in advance,
JaK
# 2  
Old 02-07-2012
Code:
awk -F, '{ V=$4; gsub(/"/,"",V); print >"file" V ".txt" }' filename

# 3  
Old 02-08-2012
Thanks for the reply. I am getting this error

Quote:
cat file.txt
"Jay","M","1","2","","3","B"
"Ann","F","4","","5","6","A"

awk -F, '{ V=$4; gsub(/"/,"",V); print >"file" V ".txt" }' ./file.txt
awk: syntax error near line 1
awk: illegal statement near line 1
JaK
# 4  
Old 02-08-2012
Try This !!
Code:
# cat myfile.txt  | awk -F"," '{gsub(/"/,""); if ($4 == "") print > "file4"; else if ($5 == "") print > "file5"; else print > "file"}'

--Shirish Shukla
Last edited by Franklin52; 02-09-2012 at 04:19 AM.. Reason: Please use code tags for code and data samples, thank you
# 5  
Old 02-08-2012
Quote:
Originally Posted by jakSun8
Thanks for the reply. I am getting this error
JaK
Which is your OS? If solaris use nawk

--ahamed
# 6  
Old 02-08-2012
Thanks Sirish, it worked!
# 7  
Old 02-08-2012
Quote:
Originally Posted by Shirishlnx
Try This !!

# cat myfile.txt | awk -F"," '{gsub(/"/,""); if ($4 == "") print > "file4"; else if ($5 == "") print > "file5"; else print > "file"}'

--Shirish Shukla
You get a useless use of cat award.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

UNIX command -Filter rows in fixed width file based on column values

Hi All, I am trying to select the rows in a fixed width file based on values in the columns. I want to select only the rows if column position 3-4 has the value AB I am using cut command to get the column values. Is it possible to check if cut -c3-4 = AB is true then select only that... (2 Replies)
Discussion started by: ashok.k
2 Replies

2. Shell Programming and Scripting

Splitting the numeric vs alpha values in a column to distinct columns

How could i take an input file and split the numeric values from the alpha values (123 vs abc) to distinc columns, and if the source is blank to keep it blank (null) in both of the new columns: So if the source file had a column like: Value: |1 | |2.3| | | |No| I would... (7 Replies)
Discussion started by: driftlogic
7 Replies

3. 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

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. Shell Programming and Scripting

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.... (3 Replies)
Discussion started by: Vivekit82
3 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

splitting single column values into text and number component

Hey guys, I have a column that consists of string and integer values without a distinctive deliminator, looking like this... 7ASA 14LAL 245FOO 656MOM 87577DAD ... I want to split the column into two columns, one containing the numbers and one containing the text part. edit: numbers... (3 Replies)
Discussion started by: origamisven
3 Replies

10. Shell Programming and Scripting

How to pick values from column based on key values by usin AWK

Dear Guyz:) I have 2 different input files like this. I would like to pick the values or letters from the inputfile2 based on inputfile1 keys (A,F,N,X,Z). I have done similar task by using awk but in that case the inputfiles are similar like in inputfile2 (all keys in 1st column and values in... (16 Replies)
Discussion started by: repinementer
16 Replies
Login or Register to Ask a Question