Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

Replace Null with 0 in 6th column in file

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Replace Null with 0 in 6th column in file
# 1  
Old 06-25-2014
Replace Null with 0 in 6th column in file
Hi Forum.

I tried to search for the solution online but I couldn't find specifically what I'm trying to achieve.

I want to replace Null with 0 in column position#6; Any other values would be retained.

Code:
Before:
52653363|3407732947|28-MAR-2014 11:47:28|A|4011||3200|D|SAV|152|06042|1MA4||CAD|31-JUL-2013|0|5265336|10|04-APR-2014|2001

52653363|3022280964|31-JUL-2013 10:53:01|D|3714|200.00|3000|D|SAV|152|06042|1MA30||CAD|19-MAR-2013|4|5265336|10|30-AUG-2013|6


Code:
After:
52653363|3407732947|28-MAR-2014 11:47:28|A|4011|0|3200|D|SAV|152|06042|1MA4||CAD|31-JUL-2013|0|5265336|10|04-APR-2014|2001

52653363|3022280964|31-JUL-2013 10:53:01|D|3714|200.00|3000|D|SAV|152|06042|1MA30||CAD|19-MAR-2013|4|5265336|10|30-AUG-2013|6

Thank you.
# 2  
Old 06-25-2014
Try
Code:
awk '!$6{$6=0}1' FS="|" OFS="|" file

This User Gave Thanks to RudiC For This Post:
pchang
# 3  
Old 06-25-2014
Its safe to check NF,if there is any blank line, slight modification to RudiC's solution

Code:
$ awk 'NF && !$6{$6=0}1' FS="|" OFS="|" file

This User Gave Thanks to Akshay Hegde For This Post:
RudiC
# 4  
Old 06-25-2014
If you might have strings like "000" or "0.0" in field 6 that you don't want changed to "0", it might be safer to use:
Code:
awk 'NF && $6==""{$6=0}1' FS="|" OFS="|" file

Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Dummies Questions & Answers

Check for not null column in a pipe delimited file

Hi, I have a requirement where I have to check whether the mandatory columns in a pipe delimited file is null and print error message. For eg, I have to check if the 3rd,5th,6th,7th and 8th column are null and print the message "<column name> is null". The data file will have aroung 100,000... (6 Replies)
Discussion started by: reshma15193
6 Replies

2. Shell Programming and Scripting

Match column 8 in file 1 with column 2 in file 2 and replace..

I am looking at the NR==FNR posts and trying to use them to achieve the following but I am not getting it. I have 2 files. I want to match column 8 in file 1 with column 2 in file 2. When they match I want to replace column 9 in file 1 with column 1 in file 2. This is and extract from file 1 ... (5 Replies)
Discussion started by: kieranfoley
5 Replies

3. Shell Programming and Scripting

Replace pipe delimited column string to null

Hi All, I have a large dat file where each lines are pipe delimited values. I need to parse the file depending on the request. For example: sometimes I have told to remove all the values in the 7th column (this case remove values '3333' only from the first line and '3543' from the second line)... (4 Replies)
Discussion started by: express14
4 Replies

4. Shell Programming and Scripting

Replace a field where values are null in a file.

Hi, I've a pipe delimited file and wanted to replace the 3rd field to 099990 where the values are null. How can I achieve it using awk or sed. 20130516|00000061|02210|111554|03710|2|205069|SM APPL $80-100 RTL|S 20130516|00000061|02210|111554|03710|2|205069|SM APPL $80-100 RTL|S... (12 Replies)
Discussion started by: rudoraj
12 Replies

5. Shell Programming and Scripting

Replace column that matches specific pattern, with column data from another file

Can anyone please help with this? I have 2 files as given below. If 2nd column of file1 has pattern foo1@a, find the matching 1st column in file2 & replace 2nd column of file1 with file2's value. file1 abc_1 foo1@a .... abc_1 soo2@a ... def_2 soo2@a .... def_2 foo1@a ........ (7 Replies)
Discussion started by: prashali
7 Replies

6. Shell Programming and Scripting

Replace column with column from another file

Hello, I am trying to replace the column in file1 with the column from file2. The two files will have the same amount of rows. Each row will correspond with the same row in the other file. File1 "Replace this column" 500 13-APR-2011... (11 Replies)
Discussion started by: doobe01
11 Replies

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

8. Shell Programming and Scripting

Find and replace a column that has '' to NULL in a comma delimited using awk or sed

Hi this is my first time posting ever. I'm relatively new in using AWK/SED, I've been trying many a solution. I'm trying to replace the 59th column in a file where if I encounter '' then I would like to replace it with the word NULL. example 0 , '' , '' , 0 , 195.538462 change it to 0... (5 Replies)
Discussion started by: gumal901
5 Replies

9. Shell Programming and Scripting

How to check Null values in a file column by column if columns are Not NULLs

Hi All, I have a table with 10 columns. Some columns(2nd,4th,5th,7th,8th and 10th) are Not Null columns. I'll get a tab-delimited file and want to check col by col and generate seperate error code for each col eg:102 if 2nd col value is NULL and 104 if 4th col value is NULL so on... I am a... (7 Replies)
Discussion started by: Mandab
7 Replies

10. Shell Programming and Scripting

Replace 3 fields with null in the file

Hi, I have a file with 104 columns delimited by comma. I have to replace fields 4,5 and 19 with null values and after replacing the columns in the file , the file should be still comma delimited. I am new to shell scripting, Experts please help me out. Thank you (1 Reply)
Discussion started by: vukkusila
1 Replies
Login or Register to Ask a Question