SED and VIM to the rescue!

Almost every blog I come across on the internet has at least one blog entry about SED and its amazing powers. Why should this one be any different!? It is unfortunate but you can't control how other people provide you information. The other day I was given a very large list of IP address,subnets and gateways which needed to be put into a SQL database. They looked like this

10.1.0.0,255.255.0.0,10.1.0.1
10.2.0.0,255.255.0.0,10.2.0.1
10.3.0.0,255.255.0.0,10.3.0.1
10.4.0.0,255.255.0.0,10.4.0.1
10.5.0.0,255.255.0.0,10.5.0.1
10.6.0.0,255.255.0.0,10.6.0.1
10.7.0.0,255.255.0.0,10.7.0.1
10.8.0.0,255.255.0.0,10.8.0.1
10.9.0.0,255.255.0.0,10.9.0.1
...

I needed them to look like this

INSERT INTO `ipaddressranges` VALUES (2,'10.1.0.0/16','255.255.0.0','10.1.0.1');

To get from point A to point B I turned to sed. Instead of creating one crazy sed statement I usually find it easier to separate my task into multiple sed statements piped together. First I started with adding the "/16" to the end of the IP address range. To do this I first told sed to replace the first "," with "/16','". Notice how I am adding the "," back and adding a single quote on each side. The single quote will be needed around each address.

cat ipaddresses | sed "s/,/\/16','/" 

Notice that the "\" is escaped by putting a "/" in front of it. Also the entire sequence is in double quotes. I found this makes it easier to escape special characters. I first made the mistake of adding a "/g" at the end of said statement, however this means global and will replace all the "," which we don't want todo. We only want to replace the first one. Now we have:

10.1.0.0/16','255.255.0.0,10.1.0.1
10.2.0.0/16','255.255.0.0,10.2.0.1
10.3.0.0/16','255.255.0.0,10.3.0.1
10.4.0.0/16','255.255.0.0,10.4.0.1
10.5.0.0/16','255.255.0.0,10.5.0.1
10.6.0.0/16','255.255.0.0,10.6.0.1
10.7.0.0/16','255.255.0.0,10.7.0.1
10.8.0.0/16','255.255.0.0,10.8.0.1
10.9.0.0/16','255.255.0.0,10.9.0.1
...

Next we need to add the first part of the sql statement.

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" 

We pipe the previous statement into sed again and the start of each line indicated by a "^" with the first part of the sql statement. Again making sure to escape the single quotes. Also add the single quote at the end so the ip address will be now entirely in single quotes. Now we have:','

INSERT INTO `ipaddressranges` VALUES (2,'10.1.0.0/16','255.255.0.0,10.1.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.2.0.0/16','255.255.0.0,10.2.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.3.0.0/16','255.255.0.0,10.3.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.4.0.0/16','255.255.0.0,10.4.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.5.0.0/16','255.255.0.0,10.5.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.6.0.0/16','255.255.0.0,10.6.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.7.0.0/16','255.255.0.0,10.7.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.8.0.0/16','255.255.0.0,10.8.0.1
INSERT INTO `ipaddressranges` VALUES (2,'10.9.0.0/16','255.255.0.0,10.9.0.1

Next we need to finish the quotes around the subnet and start the quotes around the gateway so we replace the 3rd comma with "','".

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" | sed "s/,/','/3"

Notice the "/3" at end of the last sed statement which indicates the 3rd occurrence on each line. Now to finish the line we need to add a '); to the end of each statement. Todo this I replaced the end of the line "$" with "');".

cat ipaddresses | sed "s/,/\/16','/" | sed "s/^/INSERT INTO \`ipaddressranges\` VALUES (2,'/" | sed "s/,/','/3"| sed "s/$/');/" > output.sql

So this should produce what we need and store it into output.sql. However if you ran this you will notice two issues. One I forgot to replace the ID number in the beginning of the sql statement. This will create a problem when you go to import this into mysql. You will also notice a "^M" added in end of each line. I am not sure how I introduced this, if anyone knows, please comment. Regardless in order to fix both of these issues I turned to vim. First to replace the "^M" open output.sql in vim and enter the command :%s/ctrl V ctrlM//g. This will substitute all (/g) the "^M" with nothing hence removing them. Next to fix the id number we can use vim to replace it with an incrementing variable. To accomplish this type the command:

 :let i=1 | g/2/s//\=i/ | let i=i+1

This creates a variable "let i =1" and globally "g/" substitutes "s/" the number "2" with i's current value "/\=i". After each replacement it increments i. "let i=i+1". So finally we have the output we desire.

INSERT INTO `ipaddressranges` VALUES (1,'10.1.0.0/16','255.255.0.0','10.1.0.1');
INSERT INTO `ipaddressranges` VALUES (2,'10.2.0.0/16','255.255.0.0','10.2.0.1');
INSERT INTO `ipaddressranges` VALUES (3,'10.3.0.0/16','255.255.0.0','10.3.0.1');
INSERT INTO `ipaddressranges` VALUES (4,'10.4.0.0/16','255.255.0.0','10.4.0.1');
INSERT INTO `ipaddressranges` VALUES (5,'10.5.0.0/16','255.255.0.0','10.5.0.1');
INSERT INTO `ipaddressranges` VALUES (6,'10.6.0.0/16','255.255.0.0','10.6.0.1');
INSERT INTO `ipaddressranges` VALUES (7,'10.7.0.0/16','255.255.0.0','10.7.0.1');
INSERT INTO `ipaddressranges` VALUES (8,'10.8.0.0/16','255.255.0.0','10.8.0.1');
INSERT INTO `ipaddressranges` VALUES (9,'10.9.0.0/16','255.255.0.0','10.9.0.1');