Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to write to different files based on a column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to write to different files based on a column
# 1  
Old 05-10-2012
How to write to different files based on a column
Hi,
I have the following sample file

Code:
32895901-d17f-414c-ac93-3e7e0f5ec240	AND	@GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ]
73b129e1-1fa9-4c0d-b95b-4682e5389612	AUS	@GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ]
40f82e88-d1ff-4ce2-9b8e-d827ddb39447	BEL	@GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]
ffbcc6fe-ba35-489c-ae08-e70e8897aa23	BEL	@FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
b6c0a729-c8ae-4737-8e73-b28d017e145c	BEL	@FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
9ab36071-ba17-4dd7-b6bd-50b268044f23	BEL	@FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
36e9c3f1-042a-43a4-a80e-4a3bc2513d01	BGR	@GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]
5436ab97-876e-4f34-b9ff-e52858b06cf1	BMU	@FF_INPUT[AddressString: 60 TUCKER'S POINT DRIVE HAMILTON PARISH, HS 02 BMU; ISOCountryCode: BMU; LanguageCode: ENG]

I would like to separate the data in this file into several files based on column3

Ex:
I want all the lines that start with @GDF_INPUT in column3 to be in one file and all the lines that start with @FF_INPUT in column3 in another file

File1:
Code:
32895901-d17f-414c-ac93-3e7e0f5ec240	AND	@GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ]
73b129e1-1fa9-4c0d-b95b-4682e5389612	AUS	@GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ]
40f82e88-d1ff-4ce2-9b8e-d827ddb39447	BEL	@GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]
36e9c3f1-042a-43a4-a80e-4a3bc2513d01	BGR	@GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]

File2:
Code:
ffbcc6fe-ba35-489c-ae08-e70e8897aa23	BEL	@FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
b6c0a729-c8ae-4737-8e73-b28d017e145c	BEL	@FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
9ab36071-ba17-4dd7-b6bd-50b268044f23	BEL	@FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
5436ab97-876e-4f34-b9ff-e52858b06cf1	BMU	@FF_INPUT[AddressString: 60 TUCKER'S POINT DRIVE HAMILTON PARISH, HS 02 BMU; ISOCountryCode: BMU; LanguageCode: ENG]

How do i do this...
# 2  
Old 05-10-2012
what's wrong with using just grep?
Code:
grep @GDF_INPUT infile>>file01;grep @FF_INPUT infile>>file02

Too simple?

Code:
n12:/home/vbe/wks $ grep @GDF_INPUT zizi>zifile01;grep @FF_INPUT zizi>zifile02  
n12:/home/vbe/wks $ cat zizi
32895901-d17f-414c-ac93-3e7e0f5ec240    AND     @GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ]
73b129e1-1fa9-4c0d-b95b-4682e5389612    AUS     @GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ]
40f82e88-d1ff-4ce2-9b8e-d827ddb39447    BEL     @GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]
ffbcc6fe-ba35-489c-ae08-e70e8897aa23    BEL     @FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
b6c0a729-c8ae-4737-8e73-b28d017e145c    BEL     @FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
9ab36071-ba17-4dd7-b6bd-50b268044f23    BEL     @FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
36e9c3f1-042a-43a4-a80e-4a3bc2513d01    BGR     @GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]
5436ab97-876e-4f34-b9ff-e52858b06cf1    BMU     @FF_INPUT[AddressString: 60 TUCKER'S POINT DRIVE HAMILTON PARISH, HS 02 BMU; ISOCountryCode: BMU; LanguageCode: ENG]

n12:/home/vbe/wks $ cat zifile01
32895901-d17f-414c-ac93-3e7e0f5ec240    AND     @GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ]
73b129e1-1fa9-4c0d-b95b-4682e5389612    AUS     @GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ]
40f82e88-d1ff-4ce2-9b8e-d827ddb39447    BEL     @GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]
36e9c3f1-042a-43a4-a80e-4a3bc2513d01    BGR     @GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]
n12:/home/vbe/wks $ cat zifile02
ffbcc6fe-ba35-489c-ae08-e70e8897aa23    BEL     @FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
b6c0a729-c8ae-4737-8e73-b28d017e145c    BEL     @FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
9ab36071-ba17-4dd7-b6bd-50b268044f23    BEL     @FF_INPUT[AddressString: TUNNEL BRABANT PASSAGE CHARLES ROGIER 23D, BRUSSELS, , 1210; ISOCountryCode: BEL; LanguageCode: ENG]
5436ab97-876e-4f34-b9ff-e52858b06cf1    BMU     @FF_INPUT[AddressString: 60 TUCKER'S POINT DRIVE HAMILTON PARISH, HS 02 BMU; ISOCountryCode: BMU; LanguageCode: ENG]
n12:/home/vbe/wks $

Last edited by vbe; 05-10-2012 at 12:12 PM..
# 3  
Old 05-10-2012
awk way
awk '$3 ~ /@GDF_INPUT/{print $0 >> "File1"}; $3 ~ /@FF_INPUT/{print $0 >> "File2"}' infile
This User Gave Thanks to 47shailesh For This Post:
ramky79
# 4  
Old 05-10-2012
Quote:
Originally Posted by 47shailesh
awk way
awk '$3 ~ /@GDF_INPUT/{print $0 >> "File1"}; $3 ~ /@FF_INPUT/{print $0 >> "File2"}' infile
47shailesh... your solution works for me...
@vbe .. grep did not work for me , when i tested the whole file...some of the records still had @GAV in File2.


How do i split column 3 into two columns:

Lets say I want BaseName, HouseNumber and PostalCode in column 4 and
ISOCountryCode,LanguageCode in column 5; if there are more fields then i want to ignore them

Here's how I want my output to look like
Code:
32895901-d17f-414c-ac93-3e7e0f5ec240    AND     @GDF_INPUT[BaseName:CATALUNYA; HouseNumber:1; ISOCountryCode:AND; PostalCode:AD200; LanguageCode:GER; ]    CATALUNYA;1;AD200    AND;GER    
73b129e1-1fa9-4c0d-b95b-4682e5389612    AUS     @GDF_INPUT[BaseName:MARKET STREET; HouseNumber:68; Builtup:SYDNEY; ISOCountryCode:AUS; PostalCode:NSW 2000; LanguageCode:GER; ]    MARKET STREET;68;NSW 2000    AUS;GER
40f82e88-d1ff-4ce2-9b8e-d827ddb39447    BEL     @GDF_INPUT[BaseName:CHAUSSEE DE CHARLEROI; HouseNumber:38; Order8:BRUSSELS; ISOCountryCode:BEL; PostalCode:1060; LanguageCode:GER; ]    CHAYSSEE DE CHARLEROI;38;1060    BEL
36e9c3f1-042a-43a4-a80e-4a3bc2513d01    BGR     @GDF_INPUT[BaseName:BULGARIA BOULEVARD; HouseNumber:1; Builtup:SOFIA; ISOCountryCode:BGR; PostalCode:1421; LanguageCode:GER; ]    BULGARIA BOULEVARD;1;1421    BGR;GER

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Add New Column Based on Files Name

Dear Sir, I need to add new column (last column) based on files name. my files name 20170809_target_tdc_pmx4.xls 20170809_target_tdc_pmx5.xls 20170809_target_tdc_pmx6.xls for example file : 20170809_target_tdc_pmx4.xls Item code Quantity Unit price Serial number... (1 Reply)
Discussion started by: radius
1 Replies

2. UNIX for Dummies Questions & Answers

Add 1 column based on name of files

Hi all, I already transferred list of files from ftp server with files name end with $Y$m$d Example my files data_20140101.xls data_20140102.xls data-20140103.xls and content of each files, for example data_20140101.xls USA|16846481|8871374|534909|0|0|0|700|5351981|0|31605445... (6 Replies)
Discussion started by: radius
6 Replies

3. UNIX for Dummies Questions & Answers

Join 2 files based on certain column

I have file input1.txt 11103|11|OTTAWA|City|AA|CAR|0|0|1|-1|0|8526|2014-09-07 23:00:14 11103|11|OTTAWA|City|BB|TRAIN|0|0|2|-2|6|6359|2014-09-07 23:00:14 11104|11|CANADA|City|CC|CAR|0|0|2|-2|0|5947|2014-09-07 23:00:14 11104|11|CANADA|City|DD|TRAIN|0|0|2|-2|1|4523|2014-09-07 23:00:14... (5 Replies)
Discussion started by: radius
5 Replies

4. Shell Programming and Scripting

Compare two files based on column

Hi, I have two files roughly 1200 fields in length for each row, sorted on the 2nd field. I need to compare based on that 2nd column between file1 and file2 and print lines that exist in both files into separate files (I can't guarantee that every line in file1 is in file2). Example: File1: ... (1 Reply)
Discussion started by: origon
1 Replies

5. Shell Programming and Scripting

Merge files based on the column value

Hi Friends, I have a file file1.txt 1|ABC|3|jul|dhj 2|NHU|4|kil|eu 3|hjd|34|hfd|43 file2.txt 1||3|KING|dhj 2|NHU||k| 3|hjd|34|hd|43 i want to merge file1.txt file2.txt based on the column null values in file2.txif there are any nulls in column values , (5 Replies)
Discussion started by: i150371485
5 Replies

6. Shell Programming and Scripting

join two files based on one column

Hi All, I am trying to join to files based on one common column. Cat File1 ID HID Ab_1 23 Cd 45 df 22 Vv 33 Cat File2 ID pval Ab_1 0.3 Cd 10 Vv 0.0444 (3 Replies)
Discussion started by: newpro
3 Replies

7. Shell Programming and Scripting

merging two files based on first column

I had two files file1 and file2. I want a o/p file(file3) like below using first column as ref. Pls give suggestion ass join is not working as the number of lines in each file is nealry 5 C? file1 --------------------- 404000324810001 Y 404000324810004 N 404000324810008 Y 404000324810009 N... (1 Reply)
Discussion started by: p_sai_ias
1 Replies

8. Shell Programming and Scripting

Matching 2 files based on one column

Hi, On a similar subject, the following. I have two files: file1.txt dbSNP_rsID,Chromosome,Position,Gene rs10399749,chr. 01,45162,? rs4030303,chr. 01,72434,? rs4030300,chr. 01,72515,? rs940550,chr. 01,78032,? rs13328714,chr. 01,81468,? rs11490937,chr. 01,222077,? rs6683466,chr.... (5 Replies)
Discussion started by: swvanderlaan
5 Replies

9. Shell Programming and Scripting

Compare files column to column based on keys

Here is my situation. I need to compare two tab separated files (diff is not useful since there could be known difference between files). I have found similar posts , but not fully matching.I was thinking of writing a shell script using cut and grep and while loop but after going thru posts it... (2 Replies)
Discussion started by: blackjack101
2 Replies

10. Shell Programming and Scripting

Merge Two Files based on First column

Hi, I need to join two files based on first column of both files.If first column of first file matches with the first column of second file, then the lines should be merged together and go for next line to check. It is something like: File one: 110001 abc efd 110002 fgh dfg 110003 ... (10 Replies)
Discussion started by: apjneeraj
10 Replies
Login or Register to Ask a Question