While Not EOF(1) Line Input #1, str strtxt = "" strtest = Split(str) For i = 0 To UBound(strtest) If strtest(i) <> "" Then strtxt = strtxt & strtest(i) & "/" End If Next i Debug.Print strtxt strstring = Split(strtxt, "/") ' For i = 0 To UBound(strstring) If InStrRev(strstring(2), "福建", , 1) Then sql = "insert into tb (ip1,ip2,address,opt) " & _ "values ('" & strstring(0) & "','" & strstring(1) & "'," & _ "'" & strstring(2) & "','" & strstring(3) & "')" Debug.Print sql 'Cn.Execute sql End If ' Next i Wend这样呢 我试了一下,应该没问题了
61.131.26.210/61.131.26.210/福建省宁德市/寿宁县康乐网吧/ insert into tb (ip1,ip2,address,opt) values ('61.131.26.210','61.131.26.210','福建省宁德市','寿宁县康乐网吧') 61.131.26.211/61.131.26.213/福建省宁德市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.26.211','61.131.26.213','福建省宁德市','电信') 61.131.26.214/61.131.26.214/福建省宁德市/若比邻网吧/ insert into tb (ip1,ip2,address,opt) values ('61.131.26.214','61.131.26.214','福建省宁德市','若比邻网吧') 61.131.26.215/61.131.26.217/福建省宁德市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.26.215','61.131.26.217','福建省宁德市','电信') 61.131.26.218/61.131.26.218/福建省宁德市/寿宁天缘网吧/ insert into tb (ip1,ip2,address,opt) values ('61.131.26.218','61.131.26.218','福建省宁德市','寿宁天缘网吧') 61.131.26.219/61.131.27.255/福建省宁德市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.26.219','61.131.27.255','福建省宁德市','电信') 61.131.28.0/61.131.28.89/福建省莆田市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.0','61.131.28.89','福建省莆田市','电信') 61.131.28.90/61.131.28.90/福建省莆田市/第二中学/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.90','61.131.28.90','福建省莆田市','第二中学') 61.131.28.91/61.131.28.97/福建省莆田市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.91','61.131.28.97','福建省莆田市','电信') 61.131.28.98/61.131.28.98/福建省莆田市涵江区/好来屋网吧/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.98','61.131.28.98','福建省莆田市涵江区','好来屋网吧') 61.131.28.99/61.131.28.99/福建省莆田市涵江区/好来屋软件店/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.99','61.131.28.99','福建省莆田市涵江区','好来屋软件店') 61.131.28.100/61.131.28.100/福建省莆田市涵江区/好来屋网吧二楼/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.100','61.131.28.100','福建省莆田市涵江区','好来屋网吧二楼') 61.131.28.101/61.131.28.101/福建省莆田市涵江区/好来屋网吧三楼/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.101','61.131.28.101','福建省莆田市涵江区','好来屋网吧三楼') 61.131.28.102/61.131.28.137/福建省莆田市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.102','61.131.28.137','福建省莆田市','电信') 61.131.28.138/61.131.28.138/福建省莆田市涵江区/六一路星潮网吧/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.138','61.131.28.138','福建省莆田市涵江区','六一路星潮网吧') 61.131.28.139/61.131.28.141/福建省莆田市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.139','61.131.28.141','福建省莆田市','电信') 61.131.28.142/61.131.28.142/福建省莆田市涵江区/星潮网吧/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.142','61.131.28.142','福建省莆田市涵江区','星潮网吧') 61.131.28.143/61.131.28.162/福建省莆田市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.143','61.131.28.162','福建省莆田市','电信') 61.131.28.163/61.131.28.163/福建省莆田市城厢区/优雅网吧/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.163','61.131.28.163','福建省莆田市城厢区','优雅网吧') 61.131.28.164/61.131.28.173/福建省莆田市/电信/ insert into tb (ip1,ip2,address,opt) values ('61.131.28.164','61.131.28.173','福建省莆田市','电信')这个结果是没有重复的 我只是把原来的for去掉了
谢谢冰,我调试后可以了, 还有一个问题就是,我现在不要弄成四个字段,我要把address和opt字段合成一个字段address,本来是这样的 insert into tb (ip1,ip2,address,opt) values ('61.131.28.164','61.131.28.173','福建省莆田市','电信')我想改成 insert into tb (ip1,ip2,address) values ('61.131.28.164','61.131.28.173','福建省莆田市电信')行吗
呵呵,想过头了,冰说的没错,哪儿不能用循环了 Option ExplicitPrivate Sub Command1_Click() Dim cn As New ADODB.Connection Dim rs As New ADODB.Recordset Dim sql As String Dim strTest() As String Dim strString() As String Dim str As String, strTxt As String Dim i cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _ & "Data Source=" & App.Path & "\db1.mdb;Persist " _ & "Security Info=False;Jet OLEDB:Database Password=" Open App.Path & "\test.txt" For Input As #1 While Not EOF(1) Line Input #1, str If str = "" Then Exit Sub strTxt = "" strTest = Split(str) For i = 0 To UBound(strTest) If strTest(i) <> "" Then strTxt = strTxt & strTest(i) & "/" End If Next i Debug.Print strTxt strString = Split(strTxt, "/") If InStrRev(strString(2), "福建", , 1) Then sql = "insert into tb (ip1,ip2,address,opt) " & _ "values ('" & strString(0) & "','" & strString(1) & "'," & _ "'" & strString(2) & "','" & strString(3) & "')" cn.Execute sql End If Wend Close #1 Set cn = Nothing End Sub
If InStrRev(strString(2), "福建", , 1) Then
sql = "insert into tb (ip1,ip2,address,opt) " & _
"values ('" & strString(0) & "','" & strString(1) & "'," & _
"'" & strString(2) & "','" & strString(3) & "')"
cn.Execute sql
End If
Next i应该是这个地方的问题
不应该用for 循环,把for循环去掉
Line Input #1, str
strTxt = ""
'strTest = Split(str)
strString = Split(str)
'For i = 0 To UBound(strString)
If InStrRev(strString(2), "福建", , 1) Then
sql = "insert into tb (ip1,ip2,address,opt) " & _
"values ('" & strString(0) & "','" & strString(1) & "'," & _
"'" & strString(2) & "','" & strString(3) & "')"
cn.Execute sql
End If
'Next i
Wend'这样应该就可以了
我要把txt内符合要求的都弄进数据库
可现在如果有一条符合要求,它就往数据库里写入5条同样的记录(一模一样啊),可我只要一条啊
59.56.4.0 59.56.12.255 福建省福州市 电信ADSL那程序就会自动添加,5条同样的记录进数据库,依此类批,如果找到10条合适的,就会添加50条...
59.56.4.0 59.56.12.255 福建省福州市 电信ADSL
59.56.4.0 59.56.12.255 福建省福州市 电信ADSL
59.56.4.0 59.56.12.255 福建省福州市 电信ADSL
59.56.4.0 59.56.12.255 福建省福州市 电信ADSL
59.56.4.0 59.56.12.255 福建省福州市 电信ADSL
Line Input #1, str
strtxt = ""
strtest = Split(str)
For i = 0 To UBound(strtest)
If strtest(i) <> "" Then
strtxt = strtxt & strtest(i) & "/"
End If
Next i
Debug.Print strtxt
strstring = Split(strtxt, "/")
' For i = 0 To UBound(strstring)
If InStrRev(strstring(2), "福建", , 1) Then
sql = "insert into tb (ip1,ip2,address,opt) " & _
"values ('" & strstring(0) & "','" & strstring(1) & "'," & _
"'" & strstring(2) & "','" & strstring(3) & "')"
Debug.Print sql
'Cn.Execute sql
End If
' Next i Wend这样呢
我试了一下,应该没问题了
insert into tb (ip1,ip2,address,opt) values ('61.131.26.210','61.131.26.210','福建省宁德市','寿宁县康乐网吧')
61.131.26.211/61.131.26.213/福建省宁德市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.26.211','61.131.26.213','福建省宁德市','电信')
61.131.26.214/61.131.26.214/福建省宁德市/若比邻网吧/
insert into tb (ip1,ip2,address,opt) values ('61.131.26.214','61.131.26.214','福建省宁德市','若比邻网吧')
61.131.26.215/61.131.26.217/福建省宁德市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.26.215','61.131.26.217','福建省宁德市','电信')
61.131.26.218/61.131.26.218/福建省宁德市/寿宁天缘网吧/
insert into tb (ip1,ip2,address,opt) values ('61.131.26.218','61.131.26.218','福建省宁德市','寿宁天缘网吧')
61.131.26.219/61.131.27.255/福建省宁德市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.26.219','61.131.27.255','福建省宁德市','电信')
61.131.28.0/61.131.28.89/福建省莆田市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.0','61.131.28.89','福建省莆田市','电信')
61.131.28.90/61.131.28.90/福建省莆田市/第二中学/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.90','61.131.28.90','福建省莆田市','第二中学')
61.131.28.91/61.131.28.97/福建省莆田市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.91','61.131.28.97','福建省莆田市','电信')
61.131.28.98/61.131.28.98/福建省莆田市涵江区/好来屋网吧/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.98','61.131.28.98','福建省莆田市涵江区','好来屋网吧')
61.131.28.99/61.131.28.99/福建省莆田市涵江区/好来屋软件店/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.99','61.131.28.99','福建省莆田市涵江区','好来屋软件店')
61.131.28.100/61.131.28.100/福建省莆田市涵江区/好来屋网吧二楼/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.100','61.131.28.100','福建省莆田市涵江区','好来屋网吧二楼')
61.131.28.101/61.131.28.101/福建省莆田市涵江区/好来屋网吧三楼/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.101','61.131.28.101','福建省莆田市涵江区','好来屋网吧三楼')
61.131.28.102/61.131.28.137/福建省莆田市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.102','61.131.28.137','福建省莆田市','电信')
61.131.28.138/61.131.28.138/福建省莆田市涵江区/六一路星潮网吧/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.138','61.131.28.138','福建省莆田市涵江区','六一路星潮网吧')
61.131.28.139/61.131.28.141/福建省莆田市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.139','61.131.28.141','福建省莆田市','电信')
61.131.28.142/61.131.28.142/福建省莆田市涵江区/星潮网吧/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.142','61.131.28.142','福建省莆田市涵江区','星潮网吧')
61.131.28.143/61.131.28.162/福建省莆田市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.143','61.131.28.162','福建省莆田市','电信')
61.131.28.163/61.131.28.163/福建省莆田市城厢区/优雅网吧/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.163','61.131.28.163','福建省莆田市城厢区','优雅网吧')
61.131.28.164/61.131.28.173/福建省莆田市/电信/
insert into tb (ip1,ip2,address,opt) values ('61.131.28.164','61.131.28.173','福建省莆田市','电信')这个结果是没有重复的
我只是把原来的for去掉了
还有一个问题就是,我现在不要弄成四个字段,我要把address和opt字段合成一个字段address,本来是这样的
insert into tb (ip1,ip2,address,opt) values ('61.131.28.164','61.131.28.173','福建省莆田市','电信')我想改成
insert into tb (ip1,ip2,address) values ('61.131.28.164','61.131.28.173','福建省莆田市电信')行吗
Option ExplicitPrivate Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sql As String
Dim strTest() As String
Dim strString() As String
Dim str As String, strTxt As String
Dim i
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & App.Path & "\db1.mdb;Persist " _
& "Security Info=False;Jet OLEDB:Database Password="
Open App.Path & "\test.txt" For Input As #1
While Not EOF(1)
Line Input #1, str
If str = "" Then Exit Sub
strTxt = ""
strTest = Split(str)
For i = 0 To UBound(strTest)
If strTest(i) <> "" Then
strTxt = strTxt & strTest(i) & "/"
End If
Next i
Debug.Print strTxt
strString = Split(strTxt, "/")
If InStrRev(strString(2), "福建", , 1) Then
sql = "insert into tb (ip1,ip2,address,opt) " & _
"values ('" & strString(0) & "','" & strString(1) & "'," & _
"'" & strString(2) & "','" & strString(3) & "')"
cn.Execute sql
End If
Wend
Close #1
Set cn = Nothing
End Sub
"values ('" & strString(0) & "','" & strString(1) & "'," & _
"'" & strString(2) & strString(3) & "')"