#214 loading multiple nested subnets

[Steve Clark]

quick question....is there a method to load multiple nested subnets instead of one at a time from the pulldown Select menu on the Add subnet panel.? i have 96 subnets to load into my main folder.....help.!

thanks

[Markus]

I have imported a huge amount of subnets directly into the MySQL DB of phpIPam with an excel sheet and some VB code that creates SQL-statements. Maybe the code snippets helps you to use it for your creating your own import sheet:

Public Function Transform2Long(IPadress As String) As Variant
    ' This function converts an IPv4-adress into a long int
    Dim s() As String

    s = Split(IPadress, ".", 4)

    Transform2Long = Val(s(0)) * 16777216 + Val(s(1)) * 65536 + Val(s(2)) * 256 + Val(s(3))
End Function

Public Function QuoteSQL(s As String) As String
    ' this function quotes the MySQL used characters
    s = Replace(s, "\", "\\")
    s = Replace(s, "'", "\'")
    QuoteSQL = Replace(s, Chr(34), "\" + Chr(34))
End Function

Public Sub createSQL()
    ' Creates SQL statements to add subnets into phpIPam Database
    Set src = ActiveSheet
    ofs = 12

    Dim sectionID, VLANid, masterSubnetID, allowRequests, showName, pingSubnet, isFolder, SystemID As Integer
    Dim dateiname, Permissions As String

    ' In this cells I have put global values:
    sectionID = src.Cells(1, 5)
    VLANid = src.Cells(2, 5)
    masterSubnetID = src.Cells(3, 5)
    allowRequests = src.Cells(4, 5)
    showName = src.Cells(5, 5)
    Permissions = src.Cells(6, 5)
    pingSubnet = src.Cells(7, 5)
    isFolder = src.Cells(8, 5)
    dateiname = src.Cells(7, 3)

    i = 0

    ' create file and open it for write access
    Set fs = CreateObject("Scripting.FileSystemObject")

    Set a = fs.CreateTextFile(dateiname, True)

    a.WriteLine ("LOCK TABLES `subnets` WRITE;")

    s = ""

    While src.Cells(ofs + i, 1) <> ""
        If s = "" Then s = "INSERT INTO `subnets` VALUES " Else s = s + ","

        subnet = Transform2Long(src.Cells(ofs + i, 1))
        Mask = src.Cells(ofs + i, 2)
        Description = QuoteSQL(src.Cells(ofs + i, 3))
        Bemerkung = QuoteSQL(src.Cells(ofs + i, 7))
        vrfid = 0
        If src.Cells(ofs + i, 5) = "" Then editDate = "NULL" Else editDate = "'" + Format(src.Cells(ofs + i, 5), "yyyy-MM-dd hh:mm:ss") + "'"
        Ansprechpartner = QuoteSQL(src.Cells(ofs + i, 4))
        System = QuoteSQL(src.Cells(ofs + i, 3))

        s = s + "(NULL,'" + Trim(Str(subnet)) + "','" + Trim(Str(Mask)) + "'," + Trim(Str(sectionID)) + ",'" + Description + "'," + Trim(Str(vrfid)) + "," + Trim(Str(masterSubnetID)) + "," + Trim(Str(allowRequests)) + "," + Trim(Str(VLANid)) + "," + Trim(Str(showName)) + ",'" + Permissions + "'," + Trim(Str(pingSubnet)) + ",'0'," + Trim(Str(isFolder)) + "," + editDate + ",'" + Ansprechpartner + "'," + Trim(Str(SystemID)) + ",'" + System + "','" + Bemerkung + "')"
        i = i + 1
    Wend

    s = s + ";"

    a.WriteLine (s)
    a.WriteLine ("UNLOCK TABLES;")

    a.Close

End Sub

Without any guarantee. Be very carefull with writing directly to your database. You have to know, what you are doing. A backup is mandatory.

[Steve Clark]

thanks for the reply Markus, your suggestion is a bit more than my skill
set allows. _i was wondering if there might be a hot key or something
fairly easy to allow for the generation of multiple nested subnets.

obi-wan steve.clark@noaa.gov

On Fri, May 13, 2016 at 3:37 AM, Markus markusd112@users.sf.net wrote:

I have imported a huge amount of subnets directly into the MySQL DB of
phpIPam with an excel sheet and some VB code that creates SQL-statements.
Maybe the code snippets helps you to use it for your creating your own
import sheet:

Public Function Transform2Long(IPadress As String) As Variant
' This function converts an IPv4-adress into a long int Dim s() As String
s = Split(IPadress, ".", 4)
Transform2Long = Val(s(0)) * 16777216 + Val(s(1)) * 65536 + Val(s(2)) * 256 + Val(s(3))End Function
Public Function QuoteSQL(s As String) As String ' this function quotes the MySQL used characters
s = Replace(s, "\", "\") s = Replace(s, "'", "\'") QuoteSQL = Replace(s, Chr(34), "\" + Chr(34))End Function
Public Sub createSQL() ' Creates SQL statements to add subnets into phpIPam Database
Set src = ActiveSheet
ofs = 12

Dim sectionID, VLANid, masterSubnetID, allowRequests, showName, pingSubnet, isFolder, SystemID As Integer
Dim dateiname, Permissions As String

' In this cells I have put global values:    sectionID = src.Cells(1, 5)    VLANid = src.Cells(2, 5)    masterSubnetID = src.Cells(3, 5)    allowRequests = src.Cells(4, 5)    showName = src.Cells(5, 5)    Permissions = src.Cells(6, 5)    pingSubnet = src.Cells(7, 5)    isFolder = src.Cells(8, 5)    dateiname = src.Cells(7, 3)
i = 0
' create file and open it for write access
Set fs = CreateObject("Scripting.FileSystemObject")

Set a = fs.CreateTextFile(dateiname, True)

a.WriteLine ("LOCK TABLES `subnets` WRITE;")

s = ""

While src.Cells(ofs + i, 1) <> ""
    If s = "" Then s = "INSERT INTO `subnets` VALUES " Else s = s + ","

    subnet = Transform2Long(src.Cells(ofs + i, 1))
    Mask = src.Cells(ofs + i, 2)
    Description = QuoteSQL(src.Cells(ofs + i, 3))
    Bemerkung = QuoteSQL(src.Cells(ofs + i, 7))
    vrfid = 0
    If src.Cells(ofs + i, 5) = "" Then editDate = "NULL" Else editDate = "'" + Format(src.Cells(ofs + i, 5), "yyyy-MM-dd hh:mm:ss") + "'"
    Ansprechpartner = QuoteSQL(src.Cells(ofs + i, 4))
    System = QuoteSQL(src.Cells(ofs + i, 3))

    s = s + "(NULL,'" + Trim(Str(subnet)) + "','" + Trim(Str(Mask)) + "'," + Trim(Str(sectionID)) + ",'" + Description + "'," + Trim(Str(vrfid)) + "," + Trim(Str(masterSubnetID)) + "," + Trim(Str(allowRequests)) + "," + Trim(Str(VLANid)) + "," + Trim(Str(showName)) + ",'" + Permissions + "'," + Trim(Str(pingSubnet)) + ",'0'," + Trim(Str(isFolder)) + "," + editDate + ",'" + Ansprechpartner + "'," + Trim(Str(SystemID)) + ",'" + System + "','" + Bemerkung + "')"
    i = i + 1
Wend

s = s + ";"

a.WriteLine (s)
a.WriteLine ("UNLOCK TABLES;")

a.Close

End Sub

Without any guarantee. Be very carefull with writing directly to your
database. You have to know, what you are doing. A backup is mandatory.

---

• [support-requests:#214]
  https://sourceforge.net/p/phpipam/support-requests/214/ loading multiple
  nested subnets*

Status: open
Group: version 0.8
Created: Wed May 11, 2016 09:59 PM UTC by Steve Clark
Last Updated: Wed May 11, 2016 09:59 PM UTC
Owner: Miha Petkovsek

quick question....is there a method to load multiple nested subnets
instead of one at a time from the pulldown Select menu on the Add subnet
panel.? i have 96 subnets to load into my main folder.....help.!

thanks

Sent from sourceforge.net because you indicated interest in
https://sourceforge.net/p/phpipam/support-requests/214/

To unsubscribe from further messages, please visit
https://sourceforge.net/auth/subscriptions/