Re: [Phpip-support] Add Exsisting Data to phpip

[Michael E. <mi...@mi...>]

The perl script was written by Vijay Avarachen back in 2003.  Let me know
if this still works.

Michael

cat ipmanager_csv_import.pl

#!/usr/bin/perl -w
# We were using a simple excel document to keep track of ip's
# After I implemented phpIPMANAGER I had to import the data
# from the excel file to MySQL.
# This perl script reads from a csv file of format
# HOSTNAME,IP,COMMENT<EOL> and inputs the data in
# phpIPMANAGER utility (http://www.phpip.net)
# Written by Vijay Avarachen (vi...@av...)
# April 02 2003
# ver 1.0

use strict;
use DBI;
my $server = 'localhost';
my $db = 'ipmanager';
my $table = 'addresses';
my $username = 'ipmanager';
my $password = 'PaSsWoRd';
my $phone = 'x6651';
my $email = 'vav...@in...';
my $dbh = DBI->connect("dbi:mysql:$db:$server", $username, $password);

# Hash to find the gateway
my %subnet = ('11', '192.200.11.1',
           '40', '192.200.40.1',
           '41', '192.200.41.1',
           '81', '192.200.81.1',
           '82', '192.200.82.1',
           '86', '192.200.86.1',
           '98', '192.168.98.1');

while(<>){
# Spliting the csv input
# CSV file should be in the format HOSTNAME,xxx.xxx.xxx.xxx,COMMENT
my ($hostname,$ip,$comment)=split /\,/;
# chomp did not work, so I was forced to use chop twice to get rid of

# I have a feeling it was something to do with excel export feature
# end of line character
chop($comment);
chop($comment);

# spliting $ip to get the subnet
my ($oct1,$oct2,$oct3,$oct4)=split (/\./,$ip);

# Run the third octate through subnet hash to get the right gateway
my $gateway=$subnet{$oct3};
# build the query string to update ipmanager
my $query="UPDATE $table SET mask='255.255.255.0', gateway='$gateway',
description='$comment', phone='$phone', email='$email' WHERE ip='$ip';";
# print ("$query\n");
my $sth = $dbh->prepare($query);
$sth->execute();
}
$dbh->disconnect;


-- 
Michael Earls

http://www.vermeer.org
http://www.vermeer.org/pgp
http://www.phpip.net

PGP Info: KeyID 0x168CBA4E
Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E


> Yeah I can export it as CSV.
>  i've already created cidr's and prefixes.. only needed to import all host
> addresses.
>  Hope you'r script will help me.. please send me that script
>   Regards,
>
> -Geni
>
>  On 10/12/05, Michael Earls <mi...@mi...> wrote:
>>
>> Can you export the Excel to cvs format? I have a perl script that will
>> parse and insert the data into sql.
>>
>> --
>> Michael Earls
>>
>> http://www.vermeer.org
>> http://www.vermeer.org/pgp
>> http://www.phpip.net
>>
>> PGP Info: KeyID 0x168CBA4E
>> Fingerprint 14DD 0899 8FE4 1C16 9561 FA2A 1ACB CA91 168C BA4E
>>
>>
>> > hi all
>> > i've Question here. I am interested in using your software .. and it
>> > looks
>> > really good to me.
>> > from past 5 years we've been mantaining IP Schema of our organization
>> on
>> > Excel sheets. Which have similar Colmns as " IP MASK DESCRIPTION
>> CLIENT
>> > and your software also generates output in same format. So is there
>> any
>> > possbility that i can import that IP Schema from Excel to this
>> software???
>> > bcos there are around 8000 IP adressse and it will be real pain to
>> > re-enter
>> > that manually..
>> > And also. In search page is it possible to include Search in ANY
>> field?
>> > Waiting for response.
>> > -Geni.
>> >
>>
>>
>