Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Splitting file using value in a column

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Splitting file using value in a column
# 1  
Old 05-22-2005
Splitting file using value in a column
Hi,

Is it possible to split a file into 2 files based on the value in a specific column?

I need to split the input based on the value of “Y” or " " in column 68.

Here is the sample of the input file:
Code:
000000100%49499YCA97123BTECOONY     102YETE     Y0T003315A4AS  CTYBYYI56       
000000100%032628YIY975823T@MAIL.COM 1445MAIG    Y0T00337625A   CKT1 YC45       
0000001001032812YIY98146Y           221NERISE   N0T003335      FCKVY E3D25BRY  
000000300043822NIY258C1YTEC         24745RRIN   Y0T00337600A   DEAEYAAFC45     
000000300032942NNT925C1YTEC         5587YAN     Y0T00337600C   IKE  NYFC36ARET

The output files should be:

Output1 (Rec #1, #3 and #5....when value = “Y”):
Code:
000000100%49499YCA97123BTECOONY     102YETE     Y0T003315A4AS  CTYBYYI56       
0000001001032812YIY98146Y           221NERISE   N0T003335      FCKVY E3D25BRY  
000000300043822NIY258C1YTEC         24745RRIN   Y0T00337600A   DEAEYAAFC45

Output2 (Rec #2 and Rec#5....when value = “ ”):
Code:
000000100%032628YIY975823T@MAIL.COM 1445MAIG    Y0T00337625A   CKT1 YC45       
000000300032942NNT925C1YTEC         5587YAN     Y0T00337600C   IKE  NYFC36ARET

The record is fixed length with record length = 80. I have tried to use the following codes in my .ksh script on AIX server, it didn't work!
Code:
while read line                            
   do                                      
       echo $line | grep -q "^.*Y\.\{12\}$" 
       if [ $? -eq 0 ];                     
          then                              
             echo "$line" >> grepY.txt      
       else                                 
          echo "$line" >> grepN.txt         
       fi                                   
   done < input.txt

If the “Y” is the last non-blank character, then the following grep will work in the above while loop:
echo $line | grep -q "^.*Y[ ]*$"


Is there a way to use awk if I don't have the field delimiter? Use cut command “cut -c68 input.txt” can get the “Y” value, however, I don't know how to incorporate into this.

Thanks very much for your help!
Last edited by zazzybob; 05-22-2005 at 08:29 PM.. Reason: code tags added
# 2  
Old 05-22-2005
Code:
$ more infile
000000100%49499YCA97123BTECOONY     102YETE     Y0T003315A4AS  CTYBYYI56
000000100%032628YIY975823T@MAIL.COM 1445MAIG    Y0T00337625A   CKT1 YC45
0000001001032812YIY98146Y           221NERISE   N0T003335      FCKVY E3D25BRY
000000300043822NIY258C1YTEC         24745RRIN   Y0T00337600A   DEAEYAAFC45
000000300032942NNT925C1YTEC         5587YAN     Y0T00337600C   IKE  NYFC36ARET
$ more gboom.ksh
#!/bin/ksh

while read line
do
  char=`echo "$line" | awk -vFS='' '{print $68}'`
  if [ "$char" = "Y" ]; then
     echo "$line" >> Yfile.txt
  else
     echo "$line" >> Nfile.txt
  fi
done < infile

exit 0
$ ./gboom.ksh
$ more Yfile.txt Nfile.txt
::::::::::::::
Yfile.txt
::::::::::::::
000000100%49499YCA97123BTECOONY     102YETE     Y0T003315A4AS  CTYBYYI56
0000001001032812YIY98146Y           221NERISE   N0T003335      FCKVY E3D25BRY
000000300043822NIY258C1YTEC         24745RRIN   Y0T00337600A   DEAEYAAFC45
::::::::::::::
Nfile.txt
::::::::::::::
000000100%032628YIY975823T@MAIL.COM 1445MAIG    Y0T00337625A   CKT1 YC45
000000300032942NNT925C1YTEC         5587YAN     Y0T00337600C   IKE  NYFC36ARET

You could use cut in place of the awk too...
char=`echo "$line" | cut -c68`

Cheers
ZB
# 3  
Old 05-22-2005
Or just do it all in awk...
Code:
awk '{if(substr($0,68,1)=="Y") print > "Yfile"; else print > "Nfile"}' infile

# 4  
Old 05-23-2005
Thanks to zazzybob and Ygor for the quick response!

zazzybob: for some reason the "awk" method didn't work for me. The value of $char for all 5 record = " " and all records went to the Nfile. I tried the "cut" method, and it worked! The records went to 2 files. The interesting thing is the record length is no longer fixed (=80), I lost the trailing blanks in both methods. It appears that the echo "$line" didn't write the trailing blanks to the new files.

Ygor: I tried your method and it worked! It also wrote the trailing blanks to the 2 new files.

Thanks very much to both of you!!
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

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

2. Shell Programming and Scripting

Splitting a column in two separate fields

for making a summary I have a CSV file which is transformed to .DAT. I have an AWK file which is supposing to do the mapping of the DAT file. The code from the AWK file is the one below. The content of the DAT file looks like this (tab separated): ODT AGE CDT CO SEX TIME VALUE COMMENT ... (1 Reply)
Discussion started by: grikoss
1 Replies

3. UNIX for Dummies Questions & Answers

[SOLVED] splitting a single column(with spaces) into multiple rows

Hi All, My requisite is to split a single column of phonemes seperated by spaces into multiple rows. my input file is: a dh u th a qn ch A v U r k my o/p should be like: adhu a dh u (3 Replies)
Discussion started by: girlofgenuine
3 Replies

4. Shell Programming and Scripting

Splitting the data in a column into several columns

Hi, I have the following input file 32895901-d17f-414c-ac93-3e7e0f5ec240 AND @GDF_INPUT 73b129e1-1fa9-4c0d-b95b-4682e5389612 AUS @GDF_INPUT 40f82e88-d1ff-4ce2-9b8e-d827ddb39447 BEL @GDF_INPUT 36e9c3f1-042a-43a4-a80e-4a3bc2513d01 BGR @GDF_INPUT I want to split column 3 into two columns:... (1 Reply)
Discussion started by: ramky79
1 Replies

5. Shell Programming and Scripting

Replicating rows by splitting column in text file

Hi, I have flat file with following format Col1, Col2, Col3, Col4 --------------------------------- r1_c1, r1_c2, r1_c3, abc | def | efg r2_c1, r2_c2, r2_c3, abcwdw | dweweef | efg | ijk r3_c1, r3_c2, r3_c3, abaac ........... The first three columns contain only one entry per... (3 Replies)
Discussion started by: nick2011
3 Replies

6. Shell Programming and Scripting

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 So, now i want all the rows that have column 4 as "" go in file1.txt and all the rows that have column... (8 Replies)
Discussion started by: jakSun8
8 Replies

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

8. Shell Programming and Scripting

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2

Match column 3 in file1 to column 1 in file 2 and replace with column 2 from file2 file 1 sample SNDK 80004C101 AT XLNX 983919101 BB NETL 64118B100 BS AMD 007903107 CC KLAC 482480100 DC TER 880770102 KATS ATHR 04743P108 KATS... (7 Replies)
Discussion started by: rydz00
7 Replies

9. Shell Programming and Scripting

Changing one column of delimited file column to fixed width column

Hi, Iam new to unix. I have one input file . Input file : ID1~Name1~Place1 ID2~Name2~Place2 ID3~Name3~Place3 I need output such that only first column should change to fixed width column of 15 characters of length. Output File: ID1<<12 spaces>>Name1~Place1 ID2<<12... (5 Replies)
Discussion started by: manneni prakash
5 Replies

10. UNIX for Dummies Questions & Answers

splitting a column into rows

I have a column of data of the format: EDITORIAL OPED 193987141 193986701 193987451 193986321 STATISTICS 193986351 COLUMN EDITORIAL OPED 193987171 NEWS 193321171 NEWS 193321111 NEWS 193320891 NEWS 193321841 (3 Replies)
Discussion started by: spindoctor
3 Replies
Login or Register to Ask a Question