vba 从文本文件创建 Access 表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16678517/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Create Access table from text file
提问by bassman592
I need to create an access (2007) table from a text file. I know ahead of time what columns should exist, but from time to time the vendors slip up and submit a text file that contains an incorrect number of columns. So I don't want to specify the columns in advance. I want to load all data as text into whatever columns exist. Then I will do QC.
我需要从文本文件创建一个访问 (2007) 表。我提前知道应该存在哪些列，但供应商有时会出错并提交包含不正确数量列的文本文件。所以我不想提前指定列。我想将所有数据作为文本加载到任何存在的列中。然后我会做QC。
The columns are pipe delimited and there are over 200 columns per record. There are no column headers, but there is one line of header text for the file, and one line at the end that states how many records there are. There may be anywhere from 1 to over 5,000 records in a text file. Records are identified with CRLF (windows).
列以竖线分隔，每条记录有 200 多列。没有列标题，但文件有一行标题文本，最后一行说明有多少记录。一个文本文件中可能有 1 到 5,000 多条记录。记录用 CRLF (windows) 标识。
Here is what I have so far, and it works (in that it reads the file and places the correct information in the recordset (columns and records), and I can count the number of records), except that the SELECT INTO gives me an error:
这是我到目前为止所拥有的，它可以工作（因为它读取文件并将正确的信息放在记录集（列和记录）中，并且我可以计算记录的数量），除了 SELECT INTO 给我一个错误：
Sub OpenTextADO(strFileName As String, strPath As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim recs As Integer
Dim strRecord As String
Dim strSQL As String
recs = 0
Set cn = New ADODB.Connection
If Right(strFileName, 3) = "txt" Then
'cn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & "DBQ=" & strPath & "\" 'need schema.ini file
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & "\;Extended Properties='text;HDR=No;FMT=Delimited(|)'" 'need schema.ini file
End If
Set rs = New ADODB.Recordset
rs.Open "SELECT * INTO TESTTEXT FROM [" & strFileName & "]", cn, adOpenStatic, adLockOptimistic, adCmdText
'Do Until rs.EOF
' For Each fld In rs.Fields
' strRecord = strRecord & "|" & fld.Value
' Next fld
' strRecord = strRecord & vbCr
' recs = recs + 1
' rs.MoveNext
'Loop
'Debug.Print strRecord
'recs = rs.RecordCount
rs.Close
Set rs = Nothing
MsgBox "Text was opened and there are " & recs & " records in the table."
cn.Close
Set cn = Nothing
End Sub
NOTE: I included both the OLEDB version and the text driver version - they both seem to operate identically. I also created a schema.ini file that looks like this:
注意：我同时包含了 OLEDB 版本和文本驱动程序版本 - 它们的运行方式似乎相同。我还创建了一个如下所示的 schema.ini 文件：
[test.txt]
Format=Delimited(|)
ColNameHeader=False
Both drivers seem to need this to desregard column headers, despite the "HDR=No" in the OLEDB version.
尽管 OLEDB 版本中有“HDR=No”，但两个驱动程序似乎都需要这个来忽略列标题。
The error I get is: "Cannot update. Database or object is read-only".
我得到的错误是：“无法更新。数据库或对象是只读的”。
I appreciate any help.
我很感激任何帮助。
回答by wlgreg
Could you do a sequential read of the text file, using the count of pipe-delimited fields in the first data line of the file to create a table with the proper number of columns, then just write subsequent lines into that table? I just threw the following together, but it seems to work.
您能否对文本文件进行顺序读取，使用文件第一个数据行中以竖线分隔的字段的数量来创建一个具有适当列数的表，然后将后续行写入该表中？我只是将以下内容放在一起，但似乎有效。
Public Function import_txt_to_db(strFile As String) As Boolean
On Error GoTo ErrHandle
Dim strLine As String
Dim intFileNum As Integer
Dim blnFirstLine As Boolean
blnFirstLine = True
Dim varArray As Variant
intFileNum = FreeFile
Open strFile For Input Access Read As intFileNum
Do While Not EOF(intFileNum)
Line Input #intFileNum, strLine
varArray = Split(strLine, "|")
If blnFirstLine = True Then
'Use count of fields in first line to determine # of columns to create
Dim intColCount As Integer
intColCount = UBound(varArray)
Dim strQry As String
strQry = "CREATE TABLE tblImport ("
Dim intCtr As Integer
For intCtr = 1 To intColCount + 1
strQry = strQry & "[COLUMN_" & intCtr & "] TEXT(255),"
Next intCtr
strQry = Left(strQry, Len(strQry) - 1) & ")" 'get rid of terminal comma
CurrentDb.Execute strQry
blnFirstLine = False
End If
Dim strQry2 As String
strQry2 = "INSERT INTO tblImport VALUES('" & Replace(strLine, "|", "','") & "')"
CurrentDb.Execute strQry2
Loop
Close #intFileNum
import_txt_to_db = True
Exit Function
ErrHandle:
import_txt_to_db = False
End Function
I did a simple test with the folowing five-line text file
我用下面的五行文本文件做了一个简单的测试
Thomas|Jefferson|Virginia
Bill|Clinton|Arkansas
Jimmy|Carter|Georgia
Lyndon|Johnson|Texas
George|Washington|Virginia
After running the code, here's my (simple) table:
运行代码后，这是我的（简单）表：
