可不可以将文本文件导入到access数据库当中？

[wu781122]

请问是不是可以将文本文件导入到access数据库当中？如果可以，是否可以提供源程序？

[lulu1228]

为什么不行，可以，很简单的，不要源程序，先用Excel导入文本文件，再用Access导入Excel文件，不就可以了

[wu781122]

用excel我也可以，但我不想这么做。我还是想直接将文本中的所有数据直接导入到已有的access数据库当中。

[yoki]

Private Sub TxtToMdb(sTxtPath As String, sTxtFileName As String, sAccessFullFileName As String,sAccessTable As String)
'功能：将文本文件导入到Access中的表
'调用：Call TxtToMdb("c:\","test.txt","c:\a.mdb","NewTempTable")
'结果：将c:\test.txt导入到c:\a.mdb中的NewTempTable表中

Dim db As DAO.Database

Set db = DBEngine.CreateDatabase(sAccessFullFileName, dbLangGeneral)
If Err.Number = 3204 Then
Set db = Workspaces(0).OpenDatabase(sAccessFullFileName)
End If
db.Execute "SELECT * into " & sAccessTable & " FROM [Text;HDR=NO;DATABASE=" & sTxtPath & "]." & sTxtFileName
db.Close
Set db = Nothing
End Sub

[busisoft]

'下面代码将c:\temp1.txt转化为c:\temp2.mdb 注意要添加 DAO 3.51以上（包括3.51)
Dim db As DAO.Database
Dim tbl As DAO.TableDef

On Error Resume Next
Set db = DBEngine.CreateDatabase("c:\temp2.mdb", dbLangGeneral)
If Err.Number = 3204 Then
Set db = Workspaces(0).OpenDatabase("c:\temp2.mdb")
End If
Set tbl = db.CreateTableDef("Temp")
tbl.Connect = "Text;database=c:\"
tbl.SourceTableName = "temp1#txt"

db.TableDefs.Append tbl
db.TableDefs.Delete "NewTempTable"
db.Execute "select temp.* into NewTempTable from temp"
db.TableDefs.Delete tbl.Name
db.Close
Set tbl = Nothing
Set db = Nothing

[felix]

Dim mStream As ADODB.Stream
Set mStream = New ADODB.Stream
mStream.Type = adTypeBinary
mStream.Open
mStream.LoadFromFile TextPath
rst.Fields("txt") = mStream.Read

[wu781122]

Private Sub Command1_Click()
Dim DB As New ADODB.Connection
Dim RD As New ADODB.Recordset
Dim szSql As String
Dim intpoint As Integer
Dim F1, F2, F3, F4

'打开 .MDB
intpoint = MsgBox("请打开数据库", 36, "打开数据库")
If intpoint = 6 Then
On Error Resume Next
CommonDialog1.initdir = "c:\"
CommonDialog1.Filter = "所有文件(*.*)"
CommonDialog1.ShowOpen
End If
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=CommonDialog1.FileName"　　　　　　’会不会是这里出错了？？？
DB.CursorLocation = adUseClient
DB.Open
DB.BeginTrans
intpoint = MsgBox("请打开所要转换的文本文件", 36, "打开文本文件")
If intpoint = 6 Then
CommonDialog1.initdir = "c:\"
CommonDialog1.Filter = "所有文件(*.*)"
CommonDialog1.ShowOpen
End If
Open CommonDialog1.FileName For Input As #1 ' 打开输入文件。
Do While Not EOF(1) ' 循环至文件尾。
Input #1, F1, F2, F3, F4 ' 将数据读入两个变量。
Debug.Print F1, F2, F3, F4 ' 在立即窗口中显示数据。
szSql = "insert into NewTempTable(F1,F2,F3,F4) values ('" & Trim(F1) & "','" & Trim(F2) & "','" & Trim(F3) & "','" & Trim(F4) & "')" '数据可作相应的转化
DB.Execute (szSql)
Loop
Close #1 ' 关闭文件。
DB.CommitTrans
End Sub
或者毛病出在哪里？？？

[busisoft]

Private Sub Command1_Click()
Dim DB As New ADODB.Connection
Dim RD As New ADODB.Recordset
Dim szSql As String
Dim intpoint As Integer
Dim F1, F2, F3, F4

'打开 .MDB
intpoint = MsgBox("请打开数据库", 36, "打开数据库")
If intpoint = 6 Then
On Error Resume Next
CommonDialog1.initdir = "c:\"
CommonDialog1.Filter = "所有文件(*.*)"
CommonDialog1.ShowOpen
End If
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CommonDialog1.FileName '会不会是这里出错了？？？
DB.CursorLocation = adUseClient
DB.Open
DB.BeginTrans
intpoint = MsgBox("请打开所要转换的文本文件", 36, "打开文本文件")
If intpoint = 6 Then
CommonDialog1.initdir = "c:\"
CommonDialog1.Filter = "所有文件(*.*)"
CommonDialog1.ShowOpen
End If
Open CommonDialog1.FileName For Input As #1 ' 打开输入文件。
Do While Not EOF(1) ' 循环至文件尾。
Input #1, F1, F2, F3, F4 ' 将数据读入两个变量。
Debug.Print F1, F2, F3, F4 ' 在立即窗口中显示数据。
szSql = "insert into NewTempTable(F1,F2,F3,F4) values ('" & Trim(F1) & "','" & Trim(F2) & "','" & Trim(F3) & "','" & Trim(F4) & "')" '数据可作相应的转化
DB.Execute (szSql)
Loop
Close #1 ' 关闭文件。
DB.CommitTrans
End Sub

[wu781122]

4 119 5 20
8 238 11 35
12 358 16 88
16 477 21 122
我现使用的文本是上述情况。（１）我已经创建了一个数据库的表，且表的字段名已经确定。我如何将上述数据导入？（２）我可以重新创建一个数据库的表，字段名可以重新设定。我该如何导入数据？上述各位的程序我已经试过了，但是导入后数据库为空？不知道是出现了什么问题，还请说明。

[wu781122]

上述程序试过了，数据是导入成功了，但是没有字段名，且所有数据都是在一个字段下，请问这种情况应该如何解决？我的数据是对应不同字段的。请帮忙解决！

[busisoft]

'引用Microsoft ActiveX Data 2.X Object Library
Dim DB As New ADODB.Connection
Dim RD As New ADODB.Recordset
Dim szSql As String
Dim F1, F2, F3, F4

'打开 .MDB
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp2.mdb"
DB.CursorLocation = adUseClient
DB.Open
DB.BeginTrans

Open "C:\temp1.txt" For Input As #1 ' 打开输入文件。
Do While Not EOF(1) ' 循环至文件尾。
Input #1, F1, F2, F3, F4 ' 将数据读入两个变量。
Debug.Print F1, F2, F3, F4 ' 在立即窗口中显示数据。
szSql = "insert into NewTempTable(F1,F2,F3,F4) values ('" & Trim(F1) & "','" & Trim(F2) & "','" & Trim(F3) & "','" & Trim(F4) & "')"'数据可作相应的转化
DB.Execute (szSql)
Loop
Close #1 ' 关闭文件。
DB.CommitTrans

[wu781122]

我的数据库表的字段是很多个变量，我该如何解决呢？怎么样才能是文本中的数据一一对应到数据库中？

[busisoft]

'通用函数调用
Public Sub Comm(TXT As String) 'TXT文本文件名
'引用Microsoft ActiveX Data 2.X Object Library
Dim DB As New ADODB.Connection
Dim RD As New ADODB.Recordset
Dim szSql As String
Dim F1, F2, F3, F4
Dim F()
Dim i As Integer
Dim nCount As Integer

'打开 .MDB
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp2.mdb"
DB.CursorLocation = adUseClient
DB.Open
DB.BeginTrans
'不同的文件对应不同的表有效
'在这里加上选择表名Select Case语句
Select Case TXT
Case "Temp1.TXT"
szSql = "select top 1 * from NewTempTable"
Case "Temp2.TXT"
szSql = "select top 1 * from NewTempTable1"
End Select

Set RD = DB.Execute(szSql)
nCount = RD.Fields.Count
ReDim F(1 To nCount)

Open App.Path & "\" & TXT For Input As #1 ' 打开输入文件。
Do While Not EOF(1) ' 循环至文件尾。
'Input #1, F1, F2, F3, F4 ' 将数据读入两个变量。
For i = 1 To nCount
Input #1, F(i)
Debug.Print F(i)
Next
'Debug.Print F1, F2, F3, F4 ' 在立即窗口中显示数据。
'在这里加上选择表名Select Case语句
Select Case TXT
Case "Temp1.TXT"
szSql = "insert into NewTempTable(F1,F2,F3,F4) values ('" & Trim(F(1)) & "','" & Trim(F(2)) & "','" & Trim(F(3)) & "','" & Trim(F(4)) & "')" '数据可作相应的转化
Case "Temp2.TXT"

End Select
DB.Execute (szSql)
Loop
Close #1 ' 关闭文件。
DB.CommitTrans

End Sub

[busisoft]

Public Sub Comm(TXT As String) 'TXT文本文件名
'引用Microsoft ActiveX Data 2.X Object Library
Dim DB As New ADODB.Connection
Dim RD As New ADODB.Recordset
Dim szSql As String
Dim F1, F2, F3, F4
Dim F()
Dim i As Integer
Dim nCount As Integer
Dim FieldName() As String

'打开 .MDB
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp2.mdb"
DB.CursorLocation = adUseClient
DB.Open
DB.BeginTrans
'不同的文件对应不同的表有效
'在这里加上选择表名Select Case语句
Select Case TXT
Case "Temp1.TXT"
szSql = "select top 1 * from NewTempTable"
Case "Temp2.TXT"
szSql = "select top 1 * from NewTempTable1"
End Select

Set RD = DB.Execute(szSql)
nCount = RD.Fields.Count
ReDim FieldName(nCount)
ReDim F(1 To nCount)

For i = 0 To nCount - 1
FieldName(i) = RD.Fields.Item(0).Name
Next i

Open "C:\temp1.txt" For Input As #1 ' 打开输入文件。
Do While Not EOF(1) ' 循环至文件尾。
'Input #1, F1, F2, F3, F4 ' 将数据读入两个变量。
For i = 1 To nCount
Input #1, F(i)
Debug.Print F(i)
Next
'Debug.Print F1, F2, F3, F4 ' 在立即窗口中显示数据。
'在这里加上选择表名Select Case语句
Select Case TXT
Case "Temp1.TXT"
szSql = "insert into NewTempTable(" & Join(FieldName, ",") & ") values ('" & Join(F, "','") & "')" '数据可作相应的转化
Case "Temp2.TXT"
szSql = "..."
End Select
DB.Execute (szSql)
Loop
Close #1 ' 关闭文件。
DB.CommitTrans

End Sub

[busisoft]

Public Sub Comm(TXT As String) 'TXT文本文件名
'引用Microsoft ActiveX Data 2.X Object Library
Dim DB As New ADODB.Connection
Dim RD As New ADODB.Recordset
Dim szSql As String
Dim F1, F2, F3, F4
Dim F()
Dim i As Integer
Dim nCount As Integer
Dim FieldName() As String

'打开 .MDB
DB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\temp2.mdb"
DB.CursorLocation = adUseClient
DB.Open
DB.BeginTrans
'不同的文件对应不同的表有效
'在这里加上选择表名Select Case语句
Select Case TXT
Case "Temp1.TXT"
szSql = "select top 1 * from NewTempTable"
Case "Temp2.TXT"
szSql = "select top 1 * from NewTempTable1"
End Select

Set RD = DB.Execute(szSql)
nCount = RD.Fields.Count
ReDim FieldName(1 To nCount)
ReDim F(1 To nCount)

For i = 0 To nCount - 1
FieldName(i + 1) = RD.Fields.Item(i).Name
Next i

Open "C:\temp1.txt" For Input As #1 ' 打开输入文件。
Do While Not EOF(1) ' 循环至文件尾。
'Input #1, F1, F2, F3, F4 ' 将数据读入两个变量。
For i = 1 To nCount
Input #1, F(i)
Debug.Print F(i)
Next
'Debug.Print F1, F2, F3, F4 ' 在立即窗口中显示数据。
'在这里加上选择表名Select Case语句
Select Case TXT
Case "Temp1.TXT"
szSql = "insert into NewTempTable(" & Join(FieldName, ",") & ") values ('" & Join(F, "','") & "')" '数据可作相应的转化
Case "Temp2.TXT"
szSql = "..."
End Select
DB.Execute (szSql)
Loop
Close #1 ' 关闭文件。
DB.CommitTrans

End Sub