Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Split file based on a column/field value

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Split file based on a column/field value
# 1  
Old 08-27-2014
Split file based on a column/field value
Hi All,

I have a requirement to split file into 2 sets of file. Below is a sample data of the file

Code:
AU;PTN;24EX;25-AUG-14;AU;123;SE;123;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;456;SE;456;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;147;SE;147;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;369;SE;369;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;369;SE;369;Test NN;;;;QWE;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;RTY;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;UIO;

The fields are seperated by ";"
I want the file to be split based on the 13th field.
Based on the 13th field, I need all ASD related data in one file, and the remaining (QWE, RTY and UIO) in one file

I came up with

Code:
awk -F';' '{print > $13".txt"}' input_file

But this is generating 4 sets of files.. 1 for ASD, 1 for QWE, 1 for RTY, and 1 for UIO.. but i need only 2 sets of files.. i.e. one file for ASD related values, and another file for remaining files.

Please help me to achive this !

Regards,
# 2  
Old 08-27-2014
Code:
awk -F ';' '$13 == "ASD" {print > ("ASD.txt"); next} {print > ("REST.txt")}' input_file

This User Gave Thanks to SriniShoo For This Post:
galaxy_rocky
# 3  
Old 08-27-2014
Lightbulb
With field 13 being the last field at the same time, this approach may be sufficient:
Code:
$ grep -E 'ASD;$' in > out.asd
$ grep -vE 'ASD;$' in > out.other

This User Gave Thanks to junior-helper For This Post:
galaxy_rocky
# 4  
Old 08-27-2014
Hello,

Here is one more approach by reading the files 2 times. Let us say we have input filename whose name is test7.

Code:
awk -F";" 'NR==FNR{a["ASD"]=$0;next} ($13 in a){print $0 >> $13"_"FILENAME".txt"} !($13 in a){print $0 >> "Other.txt"}' OFS=";" test7 test7

It will create 2 files named ASD_test7.txt and Other.txt as follows

Code:
cat ASD_test7.txt
AU;PTN;24EX;25-AUG-14;AU;123;SE;123;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;456;SE;456;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;147;SE;147;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;369;SE;369;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;ASD;
 
cat Other.txt
AU;PTN;24EX;25-AUG-14;AU;369;SE;369;Test NN;;;;QWE;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;RTY;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;UIO;


EDIT: Without reading file twice. Thanks Scrutinizer for suggestion.

Code:
awk -F";" '($13=="ASD"){print $0 >> "ASD_"FILENAME".txt"} ($13!="ASD"){print $0 >> "Others.txt"}' test7
OR
awk -F";" '{if($13=="ASD"){print $0 >> "ASD_"FILENAME".txt"} else {print $0 >> "Others.txt"}}' test7

Output will be as follows.

Code:
cat Others.txt
AU;PTN;24EX;25-AUG-14;AU;369;SE;369;Test NN;;;;QWE;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;RTY;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;UIO;
 
cat ASD_test7.txt
AU;PTN;24EX;25-AUG-14;AU;123;SE;123;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;456;SE;456;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;147;SE;147;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;369;SE;369;Test NN;;;;ASD;
AU;PTN;24EX;25-AUG-14;AU;789;SE;789;Test NN;;;;ASD;

Thanks,
R. Singh
Last edited by RavinderSingh13; 08-27-2014 at 11:10 AM.. Reason: Added solution as per Scrutinizer's suggestion
This User Gave Thanks to RavinderSingh13 For This Post:
galaxy_rocky
# 5  
Old 08-27-2014
@ravinder, the use of an array with one single element and for every line changing it content with random information and reading the file twice is not necessary. Instead of $13 in a you can use $13=="ASD" and then you can leave out the NR==FNR section.
# 6  
Old 08-27-2014
Code:
awk -F';' '"ASD"==$13 {print > "ASD.txt";next}1' input_file >other.txt

This User Gave Thanks to RudiC For This Post:
galaxy_rocky
# 7  
Old 09-01-2014
Thank you all for the replies.. this solved my issue Smilie
Thanks once again for your valuable time....

Regards,

---------- Post updated at 01:50 AM ---------- Previous update was at 01:49 AM ----------

Thanks Srini !!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

To Split the file based on column value

Hi Team, I have a requirement in such a way that need to split the file into two based on which column particular value appears.Please find my sample file below. Lets consider the delimiter of this file as either comma or two colons.(:: and ,). So I need to split the file in such a way that all... (2 Replies)
Discussion started by: ginrkf
2 Replies

2. UNIX for Dummies Questions & Answers

Split file based on column

i have file1.txt asdas|csada|130310|0423|A1|canberra sdasd|sfdsf|130426|2328|A1|sydney Expected output : on eaceh third and fourth colum, split into each two characters asdas|csada|13|03|10|04|23|A1|canberra sdasd|sfdsf|13|04|26|23|28|A1|sydney (10 Replies)
Discussion started by: radius
10 Replies

3. Shell Programming and Scripting

Split the file based on column

Hi, I have a file sample_1.txt (300k rows) which has data like below: * Also each record is around 64k bytes 11|1|abc|102553|125589|64k bytes of data 10|2|def|123452|123356|...... 13|2|geh|144351|121123|... 25|4|fgh|165250|118890|.. 14|1|abc|186149|116657|......... (6 Replies)
Discussion started by: sol_nov
6 Replies

4. Shell Programming and Scripting

How to split file into multiple files using awk based on 1 field in the file?

Good day all I need some helps, say that I have data like below, each field separated by a tab DATE NAME ADDRESS 15/7/2012 LX a.b.c 15/7/2012 LX1 a.b.c 16/7/2012 AB a.b.c 16/7/2012 AB2 a.b.c 15/7/2012 LX2 a.b.c... (2 Replies)
Discussion started by: alexyyw
2 Replies

5. Shell Programming and Scripting

Split a file into multiple files based on field value

Hi, I've one requirement. I have to split one comma delimited file into multiple files based on one of the column values. How can I achieve this Unix Here is the sample data. In this case I have split the files based on date column(c4) Input file c1,c2,c3,c4,c5... (1 Reply)
Discussion started by: manasvi24
1 Replies

6. Shell Programming and Scripting

How to split a fixed width text file into several ones based on a column value?

Hi, I have a fixed width text file without any header row. One of the columns contains a date in YYYYMMDD format. If the original file contains 3 dates, I want my shell script to split the file into 3 small files with data for each date. I am a newbie and need help doing this. (14 Replies)
Discussion started by: bhanja_trinanja
14 Replies

7. Shell Programming and Scripting

split the file based on the 2nd column passing as a parameter

I am unable to spit the file based on the 2nd column passing as a parameter with awk command. Source file: “100”,”customer information”,”10000” “200”,”customer information”,”50000” “300”,”product information”,”40000” script: the command is not allowing to pass the parameters with the awk... (7 Replies)
Discussion started by: number10
7 Replies

8. Shell Programming and Scripting

Split large file based on last digit from a column

Hello, What's the best way to split a large into multiple files based on the last digit in the first column. input file: f 2738483300000x0y03772748378831x1y13478378358383x2y23743878383802x3y33787828282820x4y43748838383881x5y5 Desired Output: f0 3738483300000x0y03787828282820x4y4 f1... (9 Replies)
Discussion started by: alain.kazan
9 Replies

9. Shell Programming and Scripting

split large file based on field criteria

I have a file containing date/time sorted data of the form ... 2009/06/10,20:59:59.950,XAG/USD,Q,1,1115, 14.3025,100,1,1 2009/06/10,20:59:59.950,XAG/USD,Q,1,1116, 14.3026,125,1,1 2009/06/10,20:59:59.950,XAG/USD,R,0,0, , 0,0,0 2009/06/10,20:59:59.950,XAG/USD,R,1,0, 14.1910,100,1,1... (6 Replies)
Discussion started by: asriva
6 Replies

10. Shell Programming and Scripting

Split file based on field

Hi I have a large file 2.6 million records and I am trying to split the file based on last column. I am doing awk -F"|" '{ print > $NF }' filename1 After around 1000 splits it gives me a error awk: can't open file 3332332423 input record number 1068, file filename1 source... (6 Replies)
Discussion started by: s_adu
6 Replies
Login or Register to Ask a Question