Dim Fso As New FileSystemObject, ts As TextStream Set ts = Fso.OpenTextFile("C:\Documents and Settings\Administrator\桌面\INVOUT.TXT")Dim inti As Integer Dim strs As String Dim strt As String Dim vntSplit As Variant Dim str1 As String Dim str2 As String Dim str3 As String Dim rs As New RecordsetSource=C:\db1.mdb;Persist Security Info=False", adOpenKeyset, adLockOptimistic For inti = 0 To 5000 strs = ts.ReadLine If Len(strs) > 100 Then vntSplit = Split(strs, "~~") str1 = vntSplit(0)
If str1 = "0" Then str2 = vntSplit(4) str3 = vntSplit(8)
BULK INSERT Test
FROM 'D:\Test.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
select * from Test
这是VB的一小段代码,简单说明一下
直接从TXT中找到数据,然后一行一行的循环判断
找到合适的数据后,用SPLIT函数分割,你的分割符可以用" ",下面有具体的代码
然后每次找到相应的数据时,就调用SQL存入数据库
这种程序直接用VB就行了,编译成个EXE文件,定好TXT的位置,每次直接让他点击这个文件就行了On Error GoTo err:
Dim Fso As New FileSystemObject, ts As TextStream
Set ts = Fso.OpenTextFile("C:\Documents and Settings\Administrator\桌面\INVOUT.TXT")Dim inti As Integer
Dim strs As String
Dim strt As String
Dim vntSplit As Variant
Dim str1 As String
Dim str2 As String
Dim str3 As String
Dim rs As New RecordsetSource=C:\db1.mdb;Persist Security Info=False", adOpenKeyset, adLockOptimistic
For inti = 0 To 5000
strs = ts.ReadLine
If Len(strs) > 100 Then
vntSplit = Split(strs, "~~")
str1 = vntSplit(0)
If str1 = "0" Then
str2 = vntSplit(4)
str3 = vntSplit(8)
rs.Open "insert into id(编号1,编号2) values('" & str2 & "','" & str3 & "')", "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\db1.mdb;Persist Security Info=False", adOpenKeyset, adLockOptimistic
End If
End If
Next
Exit Sub
err:
If err.Number = 62 Then
MsgBox "数据导入完毕!", vbExclamation
Else
MsgBox err.Description, vbExclamation
End If
to: zlp321002(如果有一天,世界会改变)
你们说的都要分隔符,但提供的文本格式是没有分隔符的,数据应该是用的等宽分割.to: liang80318(小亮)
一行一行的读是不行的,这个文本可能达到上百万条记录,这样速度肯定不行.
============>
schema.ini可以定义宽度
可以留个QQ吗,MSN什么的,详细谈谈
你的方法是改变导入数据的格式,我这里前提是不能改变.