如何把Txt文件中的内容读到Access数据库

[chenlxbs2003]

各位大虾,我是位新手有个问题需大家帮忙!!急!!
我的文本文件内容如下:
1 12
2 12
4 14
5 39
6 24
7 40
ACCESS数据库的字段是mm和test,把上面文本中的两列数据分别读到各自对应的字段中请问我该怎么写??如果我只读1,3,5,7或2,4,6行内容又该怎么办?

[laviewpbt]

以下代码可以将文本文件中的内容读入一数组中，数组的每一个元素对应文本文件的一行
这样，通过适当的数据分离，就可以通过数组把数值填充到数据库中，比如，为了从array1(1)中分离出两个数据，可用mm1=left(array1(1),1):test1=right(array1(1),2)

Dim array1(1 To 7) As String
Private Sub Command1_Click()
Static i As Integer
i = 1
Open "f:\1.txt" For Input As #1
Do While Not EOF(1)
Input #1, array1(i)
i = i + 1
Loop
Close #1
End Sub

至于1，3，5，7行或2，4，6行，则可以通过改变i的初值或步长，比如
Static i As Integer
i = 1
Open "f:\1.txt" For Input As #1
Do While Not EOF(1)
Input #1, array1(i)
i = i + 2
Loop
Close #1 则读出地1，3，5，7行

Static i As Integer
i = 2
Open "f:\1.txt" For Input As #1
Do While Not EOF(1)
Input #1, array1(i)
i = i + 2
Loop
Close #1 ‘读出2，4，6行

数据库字段的添加你自己应该会做的吧。

[chuting1]

up

[flyingZFX]

1.文件文件的第一列是字段名称,分隔符一般是VBKEYTAB,也可以是",|"等,字段值的分隔必须与字段名的分隔符相同.
2.strsql="select * into [tablename] from [text;database=txtpath].textfileanem.txt"

[flyingZFX]

如果表不存在：
ADO.Execute "select * into table1 from [Text;database=c:\].test.txt"
如果表存在：
ADO.Execute "insert into table1 select * from [Text;database=c:\].test.txt"
文本文件中第一行为字段名



你的文本文件列之间是用的“空格”，所以说，对于数据库引擎来说，它就把其当成了一列，而且，还没有列名。

[flyingZFX]

你的文本文件应该是这个样子的
----------------------------
mm,test
1,12
2,12
4,14
5,39
6,24
7,40
----------------------------
然后就可以使用如下的程序了。

Option Explicit

Private Sub Command1_Click()
' On Error GoTo err1

Dim Conn As ADODB.Connection
Dim strSQL As String

Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\bb.mdb"
Conn.Open
strSQL = ""
strSQL = strSQL & " INSERT INTO BB"
strSQL = strSQL & " Select * From [aa.txt] IN '" & App.Path & "\' 'Text;'"
Debug.Print strSQL
Conn.Execute strSQL, , adCmdText
Conn.Close
Set Conn = Nothing
MsgBox "OK!"
Exit Sub
err1:
If Conn Is Nothing Then Set Conn = Nothing
MsgBox "Error!"
End Sub

[starsoulxp]

高手啊，佩服！
收藏

[flyingZFX]

Option Explicit

Private Sub Command1_Click()
' On Error GoTo err1

Dim Conn As ADODB.Connection
Dim strSQL As String

Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\bb.mdb"
Conn.Open
strSQL = ""
strSQL = strSQL & " INSERT INTO BB(mm,test)"
strSQL = strSQL & " Select F1 As mm,F2 As Test From [aa.txt] IN '" & App.Path & "\' 'Text;HDR=No;'"
Debug.Print strSQL
Conn.Execute strSQL, , adCmdText
Conn.Close
Set Conn = Nothing
MsgBox "OK!"
Exit Sub
err1:
If Conn Is Nothing Then Set Conn = Nothing
MsgBox "Error!"
End Sub

Private Sub Command2_Click()
Dim Conn As ADODB.Connection
Dim strSQL As String
Dim Rs As ADODB.Recordset
Dim i As Integer

Set Conn = New ADODB.Connection
Conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\bb.mdb"
Conn.Open
strSQL = ""
strSQL = strSQL & " Select F1 As mm,F2 As Test From [aa.txt] IN '" & App.Path & "\' 'Text;HDR=No;'"
Debug.Print strSQL
Set Rs = Conn.Execute(strSQL, , adCmdText)
If Not Rs.EOF Or Not Rs.BOF Then

For i = 0 To Rs.Fields.Count - 1
Debug.Print Rs.Fields(i).Name,
Next i
Debug.Print " "

While Not Rs.EOF
Debug.Print Rs.Fields(0).Value, Rs.Fields(1).Value
Rs.MoveNext
Wend
End If
Rs.Close
Set Rs = Nothing
Conn.Close
Set Conn = Nothing
MsgBox "OK!"
End Sub

这样就更好了，你可以试试上面的程序

[flyingZFX]

上面的程序可以在txt文件中没有标题行，如下图所示
----------------------------
1,12
2,12
4,14
5,39
6,24
7,40
----------------------------