Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

how to include the missing column in the original file using awk

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting how to include the missing column in the original file using awk
# 1  
Old 03-24-2010
how to include the missing column in the original file using awk
Hi Experts,
The content of the raw file:
Code:
  
date,nomsgsent,nomsgnotdeliver,nomsgdelay
201003251000,1000,1,2
201003251000,900,0,0
201003251000,1450,0,0
201003251000,1230,0,0

However, sometimes, the column will missing in the raw files:
e.g.
Code:
 
date,nomsgsent,nomsgdelay
201003251000,1000,2
201003251000,900,0
201003251000,1450,0
201003251000,1230,0

So, when the raw files have the missing column, the raw files need to have the missing column too.
Code:
 
date,nomsgsent,nomsgnotdeliver,nomsgdelay
201003251000,1000,,2
201003251000,900,,0
201003251000,1450,,0
201003251000,1230,,0

I have try:
Code:
 
awk  -F, '{ if (NR == 1 ) {if ($3 == "nomsgnodeliver") {if (NR>=1) {print $3}  else print $2","$3}}}}' rawfile.txt

but the output just is "nomsgdeliver".
Any idea to do it?
Thanks for your guidance.
# 2  
Old 03-24-2010
Code:
 awk -F","  '{if (NR == 1 && NF == 3){ $3="nomsgnotdeliver,"$3 ;} else { if (NR != 1 && NF ==3)  $3 =","$3;}}1' OFS="," rawfile.txt

HTH,
PL
# 3  
Old 03-25-2010
Code:
sed '2,$s/^\(.*\),\(.*\),\(.*\)/\1,\2,,\3/;1{s/.*/date,nomsgsent,nomsgnotdeliver,nomsgdelay/}' file

# 4  
Old 03-25-2010
Code:
awk -F \, 'NF<4 {($1=="date")?$3="nomsgnotdeliver" FS $3:$3=FS $3}1' OFS="," urfile

# 5  
Old 03-25-2010
Code:
awk -F, 'NF<4{$3=(!(NR-1)?"nomsgnotdeliver":x)FS$3}1' OFS=, infile

Use gawk, nawk or /usr/xpg4/bin/awk on Solaris.
# 6  
Old 03-25-2010
Quote:
Originally Posted by danmero
Code:
awk -F, 'NF<4{$3=(!(NR-1)?"nomsgnotdeliver":x)FS$3}1' OFS=, infile

Use gawk, nawk or /usr/xpg4/bin/awk on Solaris.
Smilie
# 7  
Old 03-25-2010
Quote:
Originally Posted by daptal
Code:
 awk -F","  '{if (NR == 1 && NF == 3){ $3="nomsgnotdeliver,"$3 ;} else { if (NR != 1 && NF ==3)  $3 =","$3;}}1' OFS="," rawfile.txt

HTH,
PL
Hi Daptal,

Can you please explain the executuion of above command.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

How to lowercase the values in a column in awk and include a dynamic counter?

Hi, I am trying to incorporate 2 functions into my `awk` command. I want to lower case Column 2 (which is essentially the same information in Col1, except in Col1 I want to maintain the capitalization) and I want to count from 0-N that begins and ends with the start of certain markers that I... (6 Replies)
Discussion started by: owwow14
6 Replies

2. Programming

Find gaps in time data and replace missing time value and column 2 value by interpolation in awk

Dear all, I am kindly seeking assistance on the following issue. I am working with data that is sampled every 0.05 hours (that is 3 minutes intervals) here is a sample data from the file 5.00000 15.5030 5.05000 15.6680 5.10000 16.0100 5.15000 16.3450 5.20000 16.7120 5.25000... (4 Replies)
Discussion started by: malandisa
4 Replies

3. Shell Programming and Scripting

Mention file include on command line: awk

Hi All: I need your help please. I have a include file *.awk , and I would want to know if it's there a option's awk where I can use this file include, for example: awk -f decodifica_bitmap.awk cadena_hex.txt -e /home/snh/Awk/include/funciones.awk > sal.out it give error: awk:... (1 Reply)
Discussion started by: solaris21
1 Replies

4. Shell Programming and Scripting

Help with awk script to get missing numbers in column 1

Hello to all, I have show below a file separated by commas. In first column has numbers where the last number is 13. 1,4 2,6 3,7 5,2 6,5 7,5 8,65 9,10 11,78 13,2 What I want to know is which numbers are missing from 1 to 13 (in this case 13 is last number in column 1). My real... (17 Replies)
Discussion started by: Ophiuchus
17 Replies

5. Shell Programming and Scripting

How can I modify my script to include or substitute missing data?

Let me start off by saying I am a self taught sometimes scripter so what you will see below won't be pretty. I have created a script to parse through a file with a large amount of data and simply pull out what I need. In doing this I create several files and then paste them together in order to... (2 Replies)
Discussion started by: fsanchez
2 Replies

6. Shell Programming and Scripting

AWK "make a new column that include increasing numbers"

please help!!!!!! I have a file .txt that has only one column like that: 34.1 35.5 35.6 45.6 ... Now, i want to add a column in the left in which the values of this column increase by 0.4 , for example: 0.0 34.1 0.4 35.5 0.8 35.6 1.2 45.6 How can i do with awk instructions??? ... (2 Replies)
Discussion started by: tienete
2 Replies

7. Shell Programming and Scripting

Need to include two more columns in the file using awk

Hi, I have a input file with many records as below: 1J4RR4GG0BC508200 68646 1 N M i want my output file to be like with columns included dgismdh and timestamp : Example: 1J4RR4GG0BC508200 68646 1 N M dgismdh 2012-02-21 07:22:25.98591 How to do it.can we do using awk? Pls help. (6 Replies)
Discussion started by: sonam273
6 Replies

8. Shell Programming and Scripting

Merge CSV files and create a column with the filename from the original file

Hello everyone!! I am not completely new to shell script but I havent been able to find the answer to my problem and I'm sure there are some smart brains here up for the challenge :D. I have several CSV files that I need to combine into one, but I also need to know where each row came from.... (7 Replies)
Discussion started by: fransanchezoria
7 Replies

9. Shell Programming and Scripting

Fix CSV file with column missing quotes

I have a CSV file that is missing quotes around a column that contains text with commas. Example: Column1, Column2, Column3, Column4, Column5, Column6 Data1, Data2, Data3, Data, 4, Data5, Data6 Data1, Data3, Data3, Data, text, 4, Data5, Data6 I think the easiest way for me to fix this is to... (2 Replies)
Discussion started by: EmptyH
2 Replies

10. UNIX for Dummies Questions & Answers

Option in sql script to include column headers when spooling file to .csv format

Can anyone help me how to include COLUMN HEADER when spooling file to .CSV format through SQL statement. Thanks, Akbar (4 Replies)
Discussion started by: s1a2m3
4 Replies
Login or Register to Ask a Question