Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

How to sort a text file if certain columns are blank?

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting How to sort a text file if certain columns are blank?
# 8  
Old 04-15-2014
What error/output do you get? What's your OS?
# 9  
Old 04-15-2014
I'm guessing that the field separator may play a part here. Can you re-display your sample input using <sp> for a space and <tab> for tabs etc. so it's easy to see what we have as input. It might get quite into the geek mode, but we need to know the characters being sent, not what they look like on screen.



Regards,
Robin
# 10  
Old 04-15-2014
Quote:
Originally Posted by rbatte1
I'm guessing that the field separator may play a part here. Can you re-display your sample input using <sp> for a space and <tab> for tabs etc. so it's easy to see what we have as input. It might get quite into the geek mode, but we need to know the characters being sent, not what they look like on screen.



Regards,
Robin
Dear rbatte

I modify the input file and output file to make it easier to see. Both are tab-delimited files and blank columns are indicated by <Blank>. Basically, I have an original table containing 29 lines (the top 29 lines). I want to insert another 24 lines (the last 24 lines in the input file) of information in to the table. I want to sort according to column 2, 3, 4 but column 1 has to be in ascending order. Column 3 and 4 could be either ascending or descending. It is quite complicated.

Input:
Code:
1	A_scaffold000011	4339168	4339469	<Blank>
2	A_scaffold000011	4336216	4336952	<Blank>
3	A_scaffold000011	4335744	4336451	<Blank>
4	A_scaffold000011	4326850	4327454	<Blank>
5	A_scaffold000066	574913	575350	<Blank>
6	A_scaffold000011	4315302	4316020	<Blank>
7	A_scaffold000011	4312997	4313537	<Blank>
8	A_scaffold000011	4309058	4309666	<Blank>
9	A_scaffold000011	4303984	4304718	<Blank>
10	A_scaffold000011	4296794	4297091	<Blank>
11	A_scaffold000011	4296721	4297091	<Blank>
12	A_scaffold000011	4294183	4294527	<Blank>
13	A_scaffold000011	4293159	4294160	<Blank>
14	A_scaffold000011	4291440	4291890	<Blank>
15	A_scaffold000011	4289872	4290250	<Blank>
16	A_scaffold000011	4286640	4286947	<Blank>
17	A_scaffold000011	4286521	4286947	<Blank>
18	A_scaffold000011	4280797	4281196	<Blank>
19	A_scaffold000014	794245	794488	<Blank>
20	A_scaffold000014	794255	794488	<Blank>
21	A_scaffold000014	795894	796054	<Blank>
22	A_scaffold000014	808242	808642	<Blank>
23	A_scaffold000014	820613	820924	<Blank>
24	A_scaffold000014	829870	830251	<Blank>
25	A_scaffold000014	838447	838648	<Blank>
26	A_scaffold000014	838939	839062	<Blank>
27	A_scaffold000014	842685	842840	<Blank>
28	A_scaffold000014	843537	844392	<Blank>
29	A_scaffold000014	846997	847407	<Blank>
<Blank>	A_Scaffold000011	4364401	4364470	3129698
<Blank>	A_Scaffold000011	4358728	4358797	4168785
<Blank>	A_Scaffold000011	4323020	4323066	3139239
<Blank>	A_Scaffold000011	4235019	4235088	3163667
<Blank>	A_Scaffold000011	4223720	4223777	4118279
<Blank>	A_Scaffold000011	4223656	4223724	4331816
<Blank>	A_Scaffold000011	4082016	4082081	3118055
<Blank>	A_Scaffold000011	4023447	4023497	4332841
<Blank>	A_Scaffold000011	4023447	4023493	4116222
<Blank>	A_Scaffold000011	4021097	4021166	3091511
<Blank>	A_Scaffold000011	4011579	4011633	3087884
<Blank>	A_Scaffold000014	171832	171887	4335868
<Blank>	A_Scaffold000014	270626	270691	3087214
<Blank>	A_Scaffold000014	333155	333224	3117688
<Blank>	A_Scaffold000014	333219	333288	4111260
<Blank>	A_Scaffold000014	400018	400087	3122030
<Blank>	A_Scaffold000014	400195	400264	3150239
<Blank>	A_Scaffold000014	732332	732401	4167274
<Blank>	A_Scaffold000014	840515	840584	4119431
<Blank>	A_Scaffold000014	856967	857035	3131230
<Blank>	A_Scaffold000014	857030	857099	3146145
<Blank>	A_Scaffold000014	876175	876232	4169013
<Blank>	A_Scaffold000014	895598	895631	3217934
<Blank>	A_Scaffold000014	899441	899481	4112189

Output file:
Code:
<Blank>	A_Scaffold000011	4364401	4364470	3129698
<Blank>	A_Scaffold000011	4358728	4358797	4168785
1	A_scaffold000011	4339168	4339469	<Blank>
2	A_scaffold000011	4336216	4336952	<Blank>
3	A_scaffold000011	4335744	4336451	<Blank>
4	A_scaffold000011	4326850	4327454	<Blank>
<Blank>	A_Scaffold000011	4323020	4323066	3139239
5	A_scaffold000066	574913	575350	<Blank>
6	A_scaffold000011	4315302	4316020	<Blank>
7	A_scaffold000011	4312997	4313537	<Blank>
8	A_scaffold000011	4309058	4309666	<Blank>
9	A_scaffold000011	4303984	4304718	<Blank>
10	A_scaffold000011	4296794	4297091	<Blank>
11	A_scaffold000011	4296721	4297091	<Blank>
12	A_scaffold000011	4294183	4294527	<Blank>
13	A_scaffold000011	4293159	4294160	<Blank>
14	A_scaffold000011	4291440	4291890	<Blank>
15	A_scaffold000011	4289872	4290250	<Blank>
16	A_scaffold000011	4286640	4286947	<Blank>
17	A_scaffold000011	4286521	4286947	<Blank>
18	A_scaffold000011	4280797	4281196	<Blank>
<Blank>	A_Scaffold000011	4235019	4235088	3163667
<Blank>	A_Scaffold000011	4223720	4223777	4118279
<Blank>	A_Scaffold000011	4223656	4223724	4331816
<Blank>	A_Scaffold000011	4082016	4082081	3118055
<Blank>	A_Scaffold000011	4023447	4023497	4332841
<Blank>	A_Scaffold000011	4023447	4023493	4116222
<Blank>	A_Scaffold000011	4021097	4021166	3091511
<Blank>	A_Scaffold000011	4011579	4011633	3087884
<Blank>	A_Scaffold000014	171832	171887	4335868
<Blank>	A_Scaffold000014	270626	270691	3087214
<Blank>	A_Scaffold000014	333155	333224	3117688
<Blank>	A_Scaffold000014	333219	333288	4111260
<Blank>	A_Scaffold000014	400018	400087	3122030
<Blank>	A_Scaffold000014	400195	400264	3150239
<Blank>	A_Scaffold000014	732332	732401	4167274
19	A_scaffold000014	794245	794488	<Blank>
20	A_scaffold000014	794255	794488	<Blank>
21	A_scaffold000014	795894	796054	<Blank>
22	A_scaffold000014	808242	808642	<Blank>
23	A_scaffold000014	820613	820924	<Blank>
24	A_scaffold000014	829870	830251	<Blank>
25	A_scaffold000014	838447	838648	<Blank>
26	A_scaffold000014	838939	839062	<Blank>
<Blank>	A_Scaffold000014	840515	840584	4119431
27	A_scaffold000014	842685	842840	<Blank>
28	A_scaffold000014	843537	844392	<Blank>
29	A_scaffold000014	846997	847407	<Blank>
<Blank>	A_Scaffold000014	856967	857035	3131230
<Blank>	A_Scaffold000014	857030	857099	3146145
<Blank>	A_Scaffold000014	876175	876232	4169013
<Blank>	A_Scaffold000014	895598	895631	3217934
<Blank>	A_Scaffold000014	899441	899481	4112189

Last edited by huiyee1; 04-15-2014 at 10:43 PM..
# 11  
Old 04-16-2014
Well, I regret that is of little use. I asked you to replace the space and tab characters so we can see the file byte-by-byte. All the white-space and the <Blank> markers just confuse it.

It might make it messy, but perhaps you could run the following:-
Code:
tr " \t" "^~" < filename

It will convert the spaces to a carat ^ and the tabs to a tilde ~ so we can see what is what. Please do not alter the file in any other way to point out blank columns, but feel free to describe them or highlight them with a colour.

If we can read the input clearly, then we can work on helping you.

As an example:-
Code:
$ cat infile
Here is a line then a tab       tab bit

$ tr " \t" "^~" < infile
Here^is^a^line^then^a^tab~tab^bit



Regards,

Robin
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Converting columns of text to rows, with blank lines

I've spent the past hour trying different things and googling for this solution and cannot find the answer. Found variations of this, but not this exact thing. I have the following text, which is the output from our mainframe. Each field is on a separate line, with a blank line between each... (7 Replies)
Discussion started by: lupin..the..3rd
7 Replies

2. Shell Programming and Scripting

Script to find blank records in a file except for few columns

I have a file with the following format: X|High|2|GIC|DM||XHM|||6 Months X|Moderate|2|GIC|DM||XHM|||6 Months X|High|2|GCM|DM||XSF|||6 Months X|Med|2|GCM|DM||XSF|||6 Here there are ten columns but I need to print rows having blank records in any of the rows (except for 6th,8th and 9th... (10 Replies)
Discussion started by: chatwithsaurav
10 Replies

3. Shell Programming and Scripting

Remove blank columns from a tab delimited text file

Hello, I have some tab delimited files that may contain blank columns. I would like to delete the blank columns if they exist. There is no clear pattern for when a blank occurs. I was thinking of using sed to replace instances of double tab with blank, sed 's/\t\t//g' All of the examples... (2 Replies)
Discussion started by: LMHmedchem
2 Replies

4. Shell Programming and Scripting

Inserting blank columns in already present CSV file

Hi, i have a csv file which have headers and values of it like below : headers --> CI Ref SerialNumber LastScanDate values --> VMware-42,VMware-42,Tue, 20 May 2014 11:03:44 +0000 i want to have a above csv in below format : headers --> CI Name CI Description CI Ref... (6 Replies)
Discussion started by: omkar.jadhav
6 Replies

5. Shell Programming and Scripting

sort second columns in file.

File will have two columns key column and second column which is pipe separated and that need to be sorted. Below is input file. 1, D|B|A|C 2, C|A|B 3, E|A|F|G|H|D|B|C 4, A|B|D|C|F Output should be 1, A|B|C|D 2, A|B|C 3, A|B|C|D|E|F|G|H 4, A|B|D|C|F (11 Replies)
Discussion started by: girish119d
11 Replies

6. UNIX for Dummies Questions & Answers

Removing columns from a text file that do not have any values in second and third columns

I have a text file that has three columns. But at the end of the text file, there are trailing lines that have missing second and third columns: 4 0.04972604 KLHL28 4 0.0497332 CSTB 4 0.04979822 AIF1 4 0.04983331 DECR2 4 0.04990344 KATNB1 4 4 4 4 How can I remove the trailing... (3 Replies)
Discussion started by: evelibertine
3 Replies

7. Shell Programming and Scripting

Extract columns from a file if the name dont exist put blank

Hi, I am very new to Unix script. Suppose i have a file with column header: NAME1 NAME2 Address Tel And I always need to make a file with column header: ID NAME1 NAME2 EMail Address Tel For the columns that do not exist in the file, I would still like to make a column with blank. ... (11 Replies)
Discussion started by: nightrider
11 Replies

8. UNIX for Dummies Questions & Answers

How to insert alternative columns and sort text from first column to second?

Hi Everybody, I am just new to UNIX as well as to this forum. I have a text file with 10,000 coloumns and each coloumn contains values separated by space. I want to separate them into new coloumns..the file is something like this as ad af 1 A as ad af 1 D ... ... 1 and A are in one... (7 Replies)
Discussion started by: Unilearn
7 Replies

9. UNIX for Dummies Questions & Answers

Sort file using 2 columns

Hi, I am trying to sort a file first by the string column, then by the number column. file: xyz1 2 xyzX 4 xyz2 1 xyz13 3 xyz11 5 xyz13 10 xyz1 1 xyz10 1 xyz4 2 result should be xyz1 1 xyz1 2 ... (3 Replies)
Discussion started by: fargo
3 Replies

10. Shell Programming and Scripting

Removing blank columns from a file

Hi, I have a sample file as shown below -- id parent name dba -----------------------------------... (7 Replies)
Discussion started by: sumirmehta
7 Replies
Login or Register to Ask a Question