vba 导入csv文件时添加文件名以访问表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8402814/
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
Add filename to access table while importing csv file
提问by Sid Vicious
I have multiple csv files in multiple folders all in one master folder on drive C:. Some files are updated each day. If a file has an update, I need to load the new daily data into an access table including the file name. The script so far imports all data from all csv files. Then it adds the file name in a new record. I need the file name added to all records.
我在驱动器 C: 上的一个主文件夹中的多个文件夹中有多个 csv 文件。有些文件每天更新。如果文件有更新，我需要将新的每日数据加载到包含文件名的访问表中。到目前为止，脚本从所有 csv 文件导入所有数据。然后它将文件名添加到新记录中。我需要将文件名添加到所有记录中。
Any help would be much appreciated.
任何帮助将非常感激。
Script:
脚本：
Sub Import_multiple_csv_files()
子导入_multiple_csv_files()
Const strPath As String = "C:\text1\" 'Directory Path
Dim strFile As String 'Filename
Dim strFileList() As String 'File Array
Dim intFile As Integer 'File Number
Dim rs As DAO.Recordset
'Loop through the folder & build file list
strFile = Dir(strPath & "*.csv")
While strFile <> ""
'add files to the list
intFile = intFile + 1
ReDim Preserve strFileList(1 To intFile)
strFileList(intFile) = strFile
strFile = Dir()
Wend
'see if any files were found
If intFile = 0 Then
MsgBox "No files found"
Exit Sub
End If
'cycle through the list of files & import to Access
'creating a new table called MyTable
For intFile = 1 To UBound(strFileList)
DoCmd.TransferText acImportDelimi, , _
"Test", strPath & strFileList(intFile)
‘add file name to record
Set rs = CurrentDb.OpenRecordset("Test")
rs.AddNew
rs.Fields("Com").Value = Dir(strPath & "*")
rs.Update
rs.Close
Set rs = Nothing
Next
MsgBox UBound(strFileList) & " Files were Imported"
End Sub
结束子
回答by Simon
I'm not 100% sure what the question is, but if you're trying to update the filename in the records you've just imported you can do with a simple update statement:
我不是 100% 确定问题是什么，但是如果您尝试更新刚刚导入的记录中的文件名，您可以使用简单的更新语句：
UPDATE Test SET Com='MyFileName' WHERE Com IS NULL OR Com=''
Here I'm assuming that the field will be null or an empty string but you can replace this with your own criteria if this is incorrect. You can use DoCmd.RunSQLto do this.
在这里，我假设该字段将为 null 或空字符串，但如果不正确，您可以将其替换为您自己的条件。您可以使用DoCmd.RunSQL来执行此操作。
If I am misunderstanding the problem then please update your question to make it clearer.
如果我误解了这个问题，请更新您的问题以使其更清楚。
