Login or Register to Ask a Question and Join Our Community


Shell Programming and Scripting

sed command to add a new column entry

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting sed command to add a new column entry
# 1  
Old 04-23-2017
sed command to add a new column entry
My input file looks like this


Code:
12	3	5.122.281.413	172.31.15.220	3421	4133	2	2	1454	3421	4133	2	2	0	
12	44036	214.215.52.146	90.123.245.211	2312	3911	4	4	521	2312	3911	4	4	1	
14	504	6.254.324.219	192.61.27.120	4444	5611	7	5	1415	4444	5611	7	5	1	
13	9	146.216.51.151	5.132.212.115	5432	6123	9	1	78	5432	6123	9	1	1	
16	41228	96.201.221.214	5.132.212.115	3411	5120	3	3	1358	3411	5120	3	3	1


my aim is to convert these ip address to a decimal form using this formula
so if my ip address is 192.168.1.2
my result
Code:
192 x (256)^3 + 168 x (256)^2 + 1 x (256)^1 + 2 (256)^0 = ?
3221225472 + 11010048 + 256 + 2 = 3232235778

so for input file, my output file has to be
Code:
12	13	121.34.21.106	123.555.232.101	3212	4573	2	8	1497	4312	4573	2	8	1	2032276842	2100029541
12	59	102.212.234.12	96.201.221.215	3412	5557	9	7	1498	1212	5557	9	7	1	1725229580	1623842263
14	22	111.116.149.61	121.51.26.198	2122	4122	6	9	1498	344122	4122	6	9	0	1869911357	2033392326
13	10	214.328.274.31	122.81.25.110	2311	3632	4	7	1471	2311	3632	4	7	0	3611890207	2052135278
16	12	121.85.52.235	87.183.82.42	3311	3922	3	1	1495	3311	3922	3	1	1	2035627243	1471631914


Is there a sed or awk one liner that can do this:


I tried this:
Code:
sed 's/. 256^3\+\.256^2\+\.256\+\.\' input.txt> outfil.txt



Also if I do
Code:
sed 's/[0-9]\+\.[0-9]\+\.[0-9]\+\.[0-9]\+/XXX.XXX.XXX.XXX/g' ./ input.txt>output.txt

will this be a non integer value?
# 2  
Old 04-24-2017
First, sed does not have the ability to perform arithmetic operations on strings. Second, your first sed command does not contain a valid substitute command. Third, your second sed command tries to edit a directory (i.e., ./) and sed only operates on text files; not directories. And, fourth, the regular expression that is your search pattern in your second sed command looks for a single digit followed by a plus-sign followed by a period followed by a single digit followed by a plus-sign followed by a period followed by a single digit followed by a plus-sign followed by a period followed by a single digit, but that pattern does not match any text in any line in your sample input.

The awk utility, however, is perfectly capable of simple arithmetic like this. However, you will not get unique integer values if any of the four numbers in an IP address is larger than 255 with the algorithm you're using to convert an IP address to an integer AND, I don't get the output values you say you expect for any of the in IP addresses you supplied??? If all four numbers in your IP addresses are in the range 0 through 255, using the algorithm you specified, the result will fit in an unsigned 32-bit integer. It will fit in a signed 32-bit integer if and only if the 1st number is in the range 0 through 127.

You might try the following awk script and see if it comes close to the results you want:
Code:
awk '
function IP2int(IP,	a, n) {
	n = split(IP, a, /[.]/)
	if(n != 4 || a[1] > 255 || a[2] > 255 || a[3] > 255 || a[4] > 255)
		return("invalid IP")
	return(a[1] * 256 ^ 3 + a[2] * 256 ^ 2 + a[3] * 256 + a[4])
}
BEGIN {	FS = OFS = "\t"
}
{	print $0, IP2int($3), IP2int($4)
}' input

with the sample input you showed us in post #1, the above code produces the output:
Code:
12	3	5.122.281.413	172.31.15.220	3421	4133	2	2	1454	3421	4133	2	2	0		invalid IP	2887716828
12	44036	214.215.52.146	90.123.245.211	2312	3911	4	4	521	2312	3911	4	4	1		3604427922	1518073299
14	504	6.254.324.219	192.61.27.120	4444	5611	7	5	1415	4444	5611	7	5	1		invalid IP	3225230200
13	9	146.216.51.151	5.132.212.115	5432	6123	9	1	78	5432	6123	9	1	1		2463642519	92591219
16	41228	96.201.221.214	5.132.212.115	3411	5120	3	3	1358	3411	5120	3	3	1	1623842262	92591219

Note that there is an empty field before the two added fields in all but the last line of input because your sample input file has an empty field (i.e., a tab character) at the end of each of those lines.

You didn't tell us what shell or operating system you're using. If you want to try this on a Solaris/SunOS system, change awk in the above script to /usr/xpg4/bin/awk or nawk.
This User Gave Thanks to Don Cragun For This Post:
sampitosh
# 3  
Old 04-24-2017
For the IP2int function, try also
Code:
function IP2int(IP)     {split (IP, T, ".")
                         TMP = sprintf ("0X%02X%02X%02X%02X", T[1], T[2], T[3], T[4])
                         printf (length(TMP) <= 10) ? "%.f\n" : "illegal IP: %s\n",  TMP
                        }
.
.
.
illegal IP: 0X057A11919D
3604427922
illegal IP: 0X06FE144DB
2463642519
1623842262

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

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Awk/sed summation of one column based on some entry in first column

Hi All , I am having an input file as stated below Input file 6 ddk/djhdj/djhdj/Q 10 0.5 dhd/jdjd.djd.nd/QB 01 0.5 hdhd/jd/jd/jdj/Q 10 0.5 512 hd/hdh/gdh/Q 01 0.5 jdjd/jd/ud/j/QB 10 0.5 HD/jsj/djd/Q 01 0.5 71 hdh/jjd/dj/jd/Q 10 0.5 ... (5 Replies)
Discussion started by: kshitij
5 Replies

2. Shell Programming and Scripting

sed command to replace multiple column in one go

Hi, I want to replace the value in more than one column. For one column ,following command is working - sed 's/./$value_to_replace/$column number' file_name e.g. suppose this is input 1111000000 command - sed 's/./M/5' output= 1111M000000 For two column also command is like - cat... (22 Replies)
Discussion started by: Preeti Chandra
22 Replies

3. Shell Programming and Scripting

How to replace multiple "&nbsp;" entry with in <td> tag into single entry using sed?

I have the input file like this. Input file: 12.txt 1) There are one or more than one <tr> tags in same line. 2) Some tr tags may have one <td> or more tna one <td> tags within it. 3) Few <td> tags having "<td> &nbsp; </td>". Few having more than one "&nbsp;" entry in it. <tr> some td... (4 Replies)
Discussion started by: thomasraj87
4 Replies

4. Shell Programming and Scripting

How to search and replace string in column in file with command sed?

how to search and replace string in column in file with command sed or other search "INC0000003.in" and replace column 4 = "W" $ cat file.txt INC0000001.in|20150120|Y|N|N INC0000002.in|20150120|Y|N|N INC0000003.in|20150120|Y|N|N INC0000004.in|20150120|Y|N|Noutput... (4 Replies)
Discussion started by: ppmanja3
4 Replies

5. UNIX for Dummies Questions & Answers

List 2nd column entry from a command

How can i retrieve 2nd column only from output of "ls - l " as my output. pls help (5 Replies)
Discussion started by: upvan111
5 Replies

6. UNIX for Dummies Questions & Answers

Rename a header column by adding another column entry to the header column name

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (1 Reply)
Discussion started by: Vavad
1 Replies

7. Shell Programming and Scripting

Rename a header column by adding another column entry to the header column name URGENT!!

Hi All, I have a file example.csv which looks like this GrpID,TargetID,Signal,Avg_Num CSCH74_1_1,2007,61,256 CSCH74_1_1,212007,647,679 CSCH74_1_1,12007,3,32 CSCH74_1_1,207,299,777 I want the output as GrpID,TragetID,Signal-CSCH74_1_1,Avg_Num CSCH74_1_1,2007,61,256... (4 Replies)
Discussion started by: Vavad
4 Replies

8. Shell Programming and Scripting

for each different entry in column 1 extract maximum values from column 2 in unix/awk

Hello, I have 2 columns (1st column has multiple entries but the corresponding values in the column 2 may be the same or different.) however I want to extract unique values for each entry in column 1 by assigning the max value from column 2 SDF4 -0.211654 SDF4 0.978068 ... (1 Reply)
Discussion started by: Diya123
1 Replies

9. Shell Programming and Scripting

sed copy column value add to certain area

I have a base file FILE1 with the following data FILE1.dat 21111111110001343 000001004OLFXXX029100020091112 21111111110000060 000001004ODL-CH001000020091112 22222222220000780 000001013OLFXXX006500020091112 23333333330001695 000001039OLFXXX030600020091112 23333333330000111... (2 Replies)
Discussion started by: kshuser
2 Replies

10. Shell Programming and Scripting

how to specify the column(field) in sed command

the following command removes the first two characters of the first column, how can I change it to to remove the first two characters of the second column? sed 's/^..//' file1.txt > file2.txt Thanks Joseph (4 Replies)
Discussion started by: jdhahbi
4 Replies
Login or Register to Ask a Question