将 csv 文件导入访问的 VBA 程序
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11275356/
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
VBA procedure to import csv file into access
提问by lama27
i need procedure in VBA to import data into access from csv excel file without some records,, as header and footer. Example,,, i have table in csv file, which contains some sentence which not belong table date
我需要在 VBA 中的程序将数据导入到来自 csv excel 文件的访问中，而没有一些记录，如页眉和页脚。例如，，，我在 csv 文件中有表格，其中包含一些不属于表格日期的句子
A1 this is some sentence title.......
A2 title
A3.......
A7 DATA DATA DATA DATA DATA
A8 rows DATA DATA DATA DATA DATA
......
....
A256 DATA DATA DATA DATA
A257 this is some sentence
A1 这是一些句子标题.......
A2 标题
A3.......
A7 DATA DATA DATA DATA DATA
A8 行 DATA DATA DATA DATA DATA ......
A256 DATA DATA DATA DATA
A257 这是一些句子
My Acess shoud contain only rows between A7 to A256. Does anyone knows procedure or whatever in VBA who solves my problems ?
我的 Acess 应该只包含 A7 到 A256 之间的行。有谁知道解决我的问题的 VBA 中的程序或任何内容？
thanks a lot
多谢
Edit
编辑
回答by Olivier Jacot-Descombes
The easiest way to do it is to link the CSV-file into the Access database as a table. Then you can work on this table as if it was an ordinary access table, for instance by creating an appropriate query based on this table that returns exactly what you want.
最简单的方法是将 CSV 文件作为表链接到 Access 数据库中。然后，您可以像处理普通访问表一样处理该表，例如通过基于该表创建适当的查询，该查询返回您想要的内容。
You can link the table either manually or with VBA like this
您可以像这样手动或使用 VBA 链接表
DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblImport", _
FileName:="C:\MyData.csv", HasFieldNames:=true
UPDATE
更新
Dim db As DAO.Database
' Re-link the CSV Table
Set db = CurrentDb
On Error Resume Next: db.TableDefs.Delete "tblImport": On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferText TransferType:=acLinkDelim, TableName:="tblImport", _
FileName:="C:\MyData.csv", HasFieldNames:=true
db.TableDefs.Refresh
' Perform the import
db.Execute "INSERT INTO someTable SELECT col1, col2, ... FROM tblImport " _
& "WHERE NOT F1 IN ('A1', 'A2', 'A3')"
db.Close: Set db = Nothing
回答by Fionnuala
Your file seems quite small (297 lines) so you can read and write them quite quickly. You refer to Excel CSV, which does not exists, and you show space delimited data in your example. Furthermore, Access is limited to 255 columns, and a CSV is not, so there is no guarantee this will work
您的文件看起来很小（297 行），因此您可以非常快速地读写它们。您引用了不存在的 Excel CSV，并在示例中显示了以空格分隔的数据。此外，Access 限制为 255 列，而 CSV 不是，因此不能保证这会起作用
Sub StripHeaderAndFooter()
Dim fs As Object ''FileSystemObject
Dim tsIn As Object, tsOut As Object ''TextStream
Dim sFileIn As String, sFileOut As String
Dim aryFile As Variant
sFileIn = "z:\docs\FileName.csv"
sFileOut = "z:\docs\FileOut.csv"
Set fs = CreateObject("Scripting.FileSystemObject")
Set tsIn = fs.OpenTextFile(sFileIn, 1) ''ForReading
sTmp = tsIn.ReadAll
Set tsOut = fs.CreateTextFile(sFileOut, True) ''Overwrite
aryFile = Split(sTmp, vbCrLf)
''Start at line 3 and end at last line -1
For i = 3 To UBound(aryFile) - 1
tsOut.WriteLine aryFile(i)
Next
tsOut.Close
DoCmd.TransferText acImportDelim, , "NewCSV", sFileOut, False
End Sub
Edit re various comments
编辑各种评论
It is possible to import a text file manually into MS Access and this will allow you to choose you own cell delimiters and text delimiters. You need to choose External data from the menu, select your file and step through the wizard.
可以手动将文本文件导入 MS Access，这将允许您选择自己的单元格分隔符和文本分隔符。您需要从菜单中选择外部数据，选择您的文件并逐步完成向导。
About importing and linking data and database objects -- Applies to: Microsoft Office Access 2003
关于导入和链接数据和数据库对象 -- 适用于：Microsoft Office Access 2003
Introduction to importing and exporting data -- Applies to: Microsoft Access 2010
导入导出数据简介 -- 适用于：Microsoft Access 2010
Once you get the import working using the wizards, you can save an import specification and use it for you next DoCmd.TransferText as outlined by @Olivier Jacot-Descombes. This will allow you to have non-standard delimiters such as semi colon and single-quoted text.
使用向导进行导入后，您可以保存导入规范并在下一个 DoCmd.TransferText 中使用它，如@Olivier Jacot-Descombes 所述。这将允许您使用非标准分隔符，例如分号和单引号文本。
