Sort and Split file with header and custom name


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Sort and Split file with header and custom name
# 1  
Old 10-06-2015
Sort and Split file with header and custom name

Hi,

I am using SUN SOLARIS (SunOS sun4v sparc SUNW, T5240).
I have a huge data file with header and trailer. This file gets used into an ETL process. ETL skips the header record (which is the first record of the file) and loads the rest of the record. The file can be delimited (comma, tab, pipe) or fixed width.

I am trying to write a script that:
  1. Sorts the records by first field. The value of first field is “Q” or “E”. So all “Q”record should sort first then “E” records.
  2. After the sort above is complete, I want to split the file on every 30,000 records with rules below:
A. The first record in every split file is a header record or blank record- starting with "H"
B. The split file needs to have same name as master file with __(double underscore) and the number.

For e.g:
Master File name is: MasterFile-HP-import-20151006.txt
Split file name needs to be : MasterFile-HP-import-20151006__1.txt ; MasterFile-HP-import-20151006__2.txt and so on.


I tried the two things below for file split:

Code:
split -dl 30000 MasterFile-HP-import-20151006.txt MasterFile-HP-import-20151006__

But getting error "split: illegal option – d error"

Also, tried the solution that I got from internet:
Code:
awk '{filename = " MasterFile-HP-import-20151006__" int((NR-1)/30000) ".txt"; print >> filename}' MasterFile-HP-import-20151006.txt

But getting an error: awk: too many output files 10 . It generates the first 10 files and gives error on 11th file.


I would appreciate any help.


Thanks

Last edited by Saanvi1; 10-06-2015 at 02:40 PM..
# 2  
Old 10-06-2015
Without sorting, try
Code:
awk '
NR==1           {HD=$0
                 OF=FILENAME
                 next
                }
!((NR-2)%lines) {if (OF) close (OF)
                 sub (/(__[0-9])*\./, "__" ++CNT ".", OF)
                 print HD > OF
                }
                {print $0 > OF
                }
' lines=30000 MasterFile-HP-import-20151006.txt

---------- Post updated at 20:27 ---------- Previous update was at 20:01 ----------

Or, try
Code:
{
        { readlink /proc/$$/fd/0; line; sort; } | awk '
        NR==1           {OF=$0
                         next
                        }    
        NR==2           {HD=$0
                         next
                        }
        !((NR-3)%lines) {if (OF) close (OF)
                         sub (/(__[0-9])*\./, "__" ++CNT ".", OF)
                         print HD > OF
                        }
                        {print $0 > OF
                        }
        ' lines=30000
} < MasterFile-HP-import-20151006.txt

This User Gave Thanks to RudiC For This Post:
# 3  
Old 10-06-2015
Thank you for the response. I tried the code below:
Code:
{
        { readlink /proc/$$/fd/0; line; sort; } | awk '
        NR==1           {OF=$0
                         next
                        }    
        NR==2           {HD=$0
                         next
                        }
        !((NR-3)%lines) {if (OF) close (OF)
                         sub (/(__[0-9])*\./, "__" ++CNT ".", OF)
                         print HD > OF
                        }
                        {print $0 > OF
                        }
        ' lines=30000
} < ${NEW_FILE}/MasterFile-HP-import-20151006.txt

But getting the error below:
readlink: not found
awk: syntax error near line 8
awk: bailing out near line 8


Also tried this:
Code:
awk '
NR==1           {HD=$0
                 OF=FILENAME
                 next
                }
!((NR-2)%lines) {if (OF) close (OF)
                 sub (/(__[0-9])*\./, "__" ++CNT ".", OF)
                 print HD > OF
                }
                {print $0 > OF
                }
' lines=30000 ${NEW_FILE}/MasterFile-HP-import-20151006.txt

Getting error:
awk: syntax error near line 6
awk: bailing out near line 6

Last edited by Saanvi1; 10-06-2015 at 05:38 PM.. Reason: Adding more statements
# 4  
Old 10-06-2015
As always, on Solaris/SunOS systems change awk to /usr/xpg4/bin/awk or nawk.
# 5  
Old 10-07-2015
Thank you Don and RudiC for your help/advice. Appreciate your time.
I was able to run both the code suggested by RudiC using nawk and /usr/xpg4/bin/awk.

The split is working fine. But getting one small issue.The files are getting generated fine from 1 to 10. But after 10 the number starts appending to itself. Please see below:

Code:
MasterFile-HP-import-20151006__1.txt
MasterFile-HP-import-20151006__2.txt

…… and so on upto 10
Code:
MasterFile-HP-import-20151006__10.txt

After 10. The number starts appending to 10.
Code:
MasterFile-HP-import-20151006__10_11.txt
MasterFile-HP-import-20151006__10_11_12.txt
MasterFile-HP-import-20151006__10_11_12_13.txt
MasterFile-HP-import-20151006__10_11_12_13_14.txt


Would it be possible to get the 11th file as:
Code:
MasterFile-HP-import-20151006__11.txt
MasterFile-HP-import-20151006__12.txt

and so on....

Thanks again
Moderator's Comments:
Mod Comment Please use CODE tags (not ICODE tags) for multi-line displays.

Last edited by Don Cragun; 10-07-2015 at 01:14 AM.. Reason: correcting the typo
# 6  
Old 10-07-2015
Try changing:
Code:
                 sub (/(__[0-9])*\./, "__" ++CNT ".", OF)

to:
Code:
                 sub (/(__[0-9]+)*\./, "__" ++CNT ".", OF)

These 2 Users Gave Thanks to Don Cragun For This Post:
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Sort file data according to a custom list of string

I have a string of pre defined ip address list which will always remain constant their order will never change like in below sample: iplist=8.8.5.19,9.7.5.14,12.9.9.23,8.8.8.14,144.1.113 In the above example i m considering only 5 ips but there could be many more. Now i have a file which... (15 Replies)
Discussion started by: mohtashims
15 Replies

2. UNIX for Beginners Questions & Answers

How do I custom sort the files in a directory using the filenames in a text file.?

Hi all, (5 Replies)
Discussion started by: KMusunuru
5 Replies

3. Shell Programming and Scripting

Split file by column value, each with header

Hello all, I have a csv with with different testcase values in column 5. year,min,max,Instrument,Testcase 2016,201,1003,GEOTROPH-02116,TATA7980 2016,53,1011,GEOTROPH-01963,TATA7980 2016,3,1024,GEOTROPH-02067,TATA7980 2016,203,1027,GEOTROPH-02011,TATA7980... (16 Replies)
Discussion started by: senhia83
16 Replies

4. Shell Programming and Scripting

Split a file based on encountering header

I need to split a file based on headers found Input file file1 content: ADD john mickey DROP matt sam output of file F1 john mickey output of file F2 matt sam (5 Replies)
Discussion started by: Diddy
5 Replies

5. UNIX for Dummies Questions & Answers

Sort a las file keep the header as it is

I have several las files with a header and each file start Version and text and before the data starts end up with ~Ascii, then the numbers starts: ------------------------------------------------------------------------- ~Version .....text.... ~Ascii 2 abc 230 1 name 1 abc 400 1... (17 Replies)
Discussion started by: tk2000
17 Replies

6. UNIX for Dummies Questions & Answers

Sort a las file keep the header as it is

I have several las files with a header and each file start Version and text and before the data starts end up with ~Ascii, then the numbers starts: ------------------------------------------------------------------------- Code: ~Version .....text.... ~Ascii 2 abc 230 1 name 1 abc ... (1 Reply)
Discussion started by: tk2000
1 Replies

7. Shell Programming and Scripting

Split and add header and trailer from input file

I need to split the file based on pattern from position 34-37 while retaining the header and trailer records in each individual split file Also is it possible to output the TOM and PAT records in the same output file ? I need the output file names same as xyz_pattern_Datetimestamp.txt ... (23 Replies)
Discussion started by: techedipro
23 Replies

8. UNIX for Dummies Questions & Answers

Split a file and give custom names

Hello, I want to split a file based on an input list file that contains the lines each split should have + a corresponding file name. #!/bin/sh # sed -n 'start_line_#,end_line_#p' my_input_file > lines_extracted_output_file while read a b c do sed -n '$a,$bp' myLarge.file > $c.split... (2 Replies)
Discussion started by: Gussifinknottle
2 Replies

9. UNIX for Dummies Questions & Answers

Sort a tab file with header.

How to sort a tab delimited file first on col1 and then on col2. Also I need to keep the header intact. file.txt val1 val2 val3 val4 a b c d m n o p e f g h i j k l ... (3 Replies)
Discussion started by: mary271
3 Replies

10. Shell Programming and Scripting

Split large file and add header and footer to each file

I have one large file, after every 200 line i have to split the file and the add header and footer to each small file? It is possible to add different header and footer to each file? (1 Reply)
Discussion started by: ashish4422
1 Replies
Login or Register to Ask a Question