☰

用文本文件信息填充 mySQL 数据库

tag-icon • tag-icon shell-script awk grep sql
用文本文件信息填充 mySQL 数据库

我一直在使用awk一些 cisco 命令来获取有关连接到网络的设备的信息，该脚本通过 telnet 连接到特定设备，获取该设备的IP、serial number和name（设备 ID）并生成如下文本文件：

SN: FDO1129Z9ZQ
Barragan_3750
IP address: 148.228.4.197

然后它会询问连接到该设备的设备并生成第二个文件，如下所示：

Device ID: BIOTERIO
IP address: 148.228.83.140
Interface: GigabitEthernet1/0/6
Port ID (outgoing port): GigabitEthernet0/1
SN: P7K08UR      

Device ID: N7K-LAN(JAF1651ANDL)
IP address: 148.228.4.193
Interface: GigabitEthernet1/0/1
Port ID (outgoing port): Ethernet7/23
SN: H006K024       

Device ID: LAB_PESADO
IP address: 148.228.131.133
Interface: GigabitEthernet1/0/11
Port ID (outgoing port): GigabitEthernet0/1
SN: FNS174002FY    

Device ID: Arquitectura_Salones
IP address: 148.228.135.33
Interface: GigabitEthernet1/0/9
Port ID (outgoing port): GigabitEthernet0/49
SN: FNS14420544    

Device ID: CIVIL_253
IP address: 148.228.132.256
Interface: GigabitEthernet1/0/4
Port ID (outgoing port): GigabitEthernet1/0/52
SN: H006K042    

Device ID: Arquitectura
IP address: 148.228.134.456
Interface: GigabitEthernet1/0/3
Port ID (outgoing port): GigabitEthernet0/1
SN: H006K044      

Device ID: ING_CIVIL
IP address: 148.228.133.234
Interface: GigabitEthernet1/0/7
Port ID (outgoing port): GigabitEthernet0/2
SN: H006K011       

Device ID: ING_CIVIL_DIR
IP address: 148.228.4.987
Interface: GigabitEthernet1/0/10
Port ID (outgoing port): GigabitEthernet0/2
SN: FNS16361SW1    

Device ID: Ingenieria_Posgrado
IP address: 148.228.137.343
Interface: GigabitEthernet1/0/8
Port ID (outgoing port): GigabitEthernet0/1
SN: H006K432   

Device ID: Biblio_Barragan
IP address: 148.228.136.45
Interface: GigabitEthernet1/0/2
Port ID (outgoing port): GigabitEthernet0/1
SN: 00000MTC1444080D

Device ID: Electronica_Edif_3
IP address: 148.228.130.345
Interface: GigabitEthernet1/0/5
Port ID (outgoing port): GigabitEthernet0/1
SN: FNS11190FRT

我需要将此信息放入MySQL数据库中，因此我创建了一个包含两个表的数据库，如下所示：

+------------------+
| Tables_in_db_cdp |
+------------------+
| Trelaciones      |
| dispositivos     |
+------------------+

mysql> DESCRIBE dispositivos;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| sn         | varchar(20) | NO   | PRI | NULL    |       |
| device_id  | varchar(25) | NO   |     | NULL    |       |
| ip_address | varchar(15) | NO   |     | NULL    |       |
+------------+-------------+------+-----+---------+-------+

mysql> DESCRIBE Trelaciones
    -> ;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| id          | int(11)     | NO   | PRI | NULL    | auto_increment |
| Device_SN_O | varchar(25) | NO   |     | NULL    |                |
| Device_SN_D | varchar(25) | NO   |     | NULL    |                |
| Interface   | varchar(25) | NO   |     | NULL    |                |
| Port_ID     | varchar(25) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+

现在我需要用文件信息填充这个数据库，但老实说我不知道​​该怎么做，我想我需要在脚本中创建一个循环，但仅此而已，

有什么帮助吗？

提前致谢。

更新：使用已接受答案的代码我得到了这个结果：

Device_SN_O,Device_SN_D,Interface,Port_ID
FDO1129Z9ZJ
,P7K08UQ         
,GigabitEthernet1/0/6,GigabitEthernet0/1
FDO1129Z9ZJ
,H006K022        
,GigabitEthernet1/0/1,Ethernet7/23
FDO1129Z9ZJ
,FNS174002FT     
,GigabitEthernet1/0/11,GigabitEthernet0/1
FDO1129Z9ZJ
,FNS14420533     
,GigabitEthernet1/0/9,GigabitEthernet0/49
FDO1129Z9ZJ
,H006K021        
,GigabitEthernet1/0/4,GigabitEthernet1/0/52
FDO1129Z9ZJ
,H006K083        
,GigabitEthernet1/0/3,GigabitEthernet0/1
FDO1129Z9ZJ
,H006K032        
,GigabitEthernet1/0/7,GigabitEthernet0/2
FDO1129Z9ZJ
,FNS16361SG0     
,GigabitEthernet1/0/10,GigabitEthernet0/2
FDO1129Z9ZJ
,H006K040        
,GigabitEthernet1/0/8,GigabitEthernet0/1
FDO1129Z9ZJ
,00000MTC1444080Z
,GigabitEthernet1/0/2,GigabitEthernet0/1
FDO1129Z9ZJ
,FNS11190FLE     
,GigabitEthernet1/0/5,GigabitEthernet0/1

当我使用“ load data infile”时，信息填写在错误的字段中。

我注意到，如果我使用“writer”打开 .cvs 文件，分隔符和换行符是正确的，但使用 gedit 则不然：

两者是同一个文件

如果我手动编辑 .cvs 文件并正确放置逗号和换行符，“ load data infile”功能将完美运行。

答案1

生成INSERT报表很麻烦，更简单的方法是使用 将文件转换为 CSV awk，然后使用mysqlimport将结果导入到相关表中。

对于第一个表，转换可能是这样的：

awk '
    BEGIN {
        RS = "\n\n"
        FS = "\n"
        OFS = ","
        print "sn,device_id,ip_address"
    }
    {
        for(i=1; i<=NF; i++) {
            split($i, a, ": ");
            k[a[1]] = a[2]
        }
        print k["SN"], k["Device ID"], k["IP address"]
    }' file2.txt > table1.csv

结果应该如下所示：

sn,device_id,ip_address
P7K08UR,BIOTERIO,148.228.83.140
H006K024,N7K-LAN(JAF1651ANDL),148.228.4.193
FNS174002FY,LAB_PESADO,148.228.131.133
FNS14420544,Arquitectura_Salones,148.228.135.33
H006K042,CIVIL_253,148.228.132.256
H006K044,Arquitectura,148.228.134.456
H006K011,ING_CIVIL,148.228.133.234
FNS16361SW1,ING_CIVIL_DIR,148.228.4.987
H006K432,Ingenieria_Posgrado,148.228.137.343
00000MTC1444080D,Biblio_Barragan,148.228.136.45
FNS11190FRT,Electronica_Edif_3,148.228.130.345

然后，假设我了解Device_SN_O和Device_SN_D应该是什么，对于第二个表，您可能会执行以下操作：

awk -v orig=$(awk '$1=="SN:" {print $2}' file1.txt) '
    BEGIN {
        RS = "\n\n"                                               
        FS = "\n"                                                 
        OFS = ","                                                 
        print "Device_SN_O,Device_SN_D,Interface,Port_ID"         
    }                                                             

    {                                                             
        for(i=1; i<=NF; i++) {                                    
            split($i, a, ": ");                                   
            k[a[1]] = a[2]                                        
        }                                                         
        print orig, k["SN"], k["Interface"], k["Port ID (outgoing port)"]
    }' file2.txt > table2.csv

结果是这样的：

Device_SN_O,Device_SN_D,Interface,Port_ID
FDO1129Z9ZQ,P7K08UR,GigabitEthernet1/0/6,GigabitEthernet0/1
FDO1129Z9ZQ,H006K024,GigabitEthernet1/0/1,Ethernet7/23
FDO1129Z9ZQ,FNS174002FY,GigabitEthernet1/0/11,GigabitEthernet0/1
FDO1129Z9ZQ,FNS14420544,GigabitEthernet1/0/9,GigabitEthernet0/49
FDO1129Z9ZQ,H006K042,GigabitEthernet1/0/4,GigabitEthernet1/0/52
FDO1129Z9ZQ,H006K044,GigabitEthernet1/0/3,GigabitEthernet0/1
FDO1129Z9ZQ,H006K011,GigabitEthernet1/0/7,GigabitEthernet0/2
FDO1129Z9ZQ,FNS16361SW1,GigabitEthernet1/0/10,GigabitEthernet0/2
FDO1129Z9ZQ,H006K432,GigabitEthernet1/0/8,GigabitEthernet0/1
FDO1129Z9ZQ,00000MTC1444080D,GigabitEthernet1/0/2,GigabitEthernet0/1
FDO1129Z9ZQ,FNS11190FRT,GigabitEthernet1/0/5,GigabitEthernet0/1

那么你可以进口将 CSV 文件导入 MySQL。

相关内容