这是一个以文本格式从逗号分隔的值文件中将信息导入查询或表的模块! 这对于从其他应用程序和文件中获取信息非常有帮助!
可以完成此功能的函数是:import_csv_file_to_SQL(文件名,SQL,可选的text_delimiter,可选的Field_delimiter)
参数: File_name:应该从中导入的相应文件的文件名。 应该是真实的现有文件的名称。 在这里,您还可以指定相应文件SQL的路径: SQL表达式,查询名称或将保存信息的表! 列的顺序非常重要! 第一个值在第一列中,第二个在第二列中,依此类推! 因此,请小心安排您的列,以适合导入的信息! 请使用可更新的SQL表达式! 如果不是,则该操作应该带有导入错误! Text_delimiter:不是必须的! 有时,有些程序会区分放在它们之前和之后的字符串。 除了可以在Access中的相应字段中导入“”符号之外,该函数还可以删除不应在数据库中的所有类似符号。 如果函数是从Visual Basic运行的,则该参数可以留为空白! 从查询而不是空白运行它,请使用一个空字符串“” Field_delimiter:不是必须的! 指定字段的定界符。 如果省略,则默认为,学习本单元可以教你:
- 在Visual Basic中使用参数创建函数
- 在函数中声明和使用可选参数
- 声明变量
- 使用Scripting.FileSystemObject打开文件
- 像流一样处理文件
- 流属性和行为
- 处理数组
- 将记录集打开到数据库中
- 访问记录集中的所有字段并为其分配值
'-------------------------------------------------------------------------
' This code is written by Vladimir Dimitrov
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of Vladimir Dimitrov
'
'------------------------------------------------------------------------
Function import_csv_file_to_SQL(File_name, SQL, Optional text_delimiter, Optional Field_delimiter)
On Error Resume Next
Dim mydb As Database
Dim myr As Recordset
Dim line
Dim i
Dim frfile
Dim myupd_last, myupd_current
Const ForReading = 1, ForWriting = 2, ForAppending = 3
Const TristateUseDefault = -2, TristateTrue = -1, TristateFalse = 0
Dim fs, f
Dim ts
Dim myarray, myarrct
If IsMissing(Field_delimiter) = True Then Field_delimiter = ","
If IsNull(Field_delimiter) = True Then Field_delimiter = ","
Set mydb = CurrentDb()
Set myr = mydb.OpenRecordset(SQL)
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFile(File_name)
Set ts = f.OpenAsTextStream(ForReading, TristateUseDefault)
Do While Not ts.AtEndOfStream
line = ts.ReadLine
myarray = Split(line, Field_delimiter)
myarrct = UBound(myarray)
myr.AddNew
i = 0
Do While (i < MinNumb(myr.fields.Count, myarrct + 1))
' Stop
If IsMissing(text_delimiter) Then
myr(i) = myarray(i)
Else
If IsNull(text_delimiter) = True Then
myr(i) = myarray(i)
Else
myr(i) = remove_symbol(myarray(i), text_delimiter)
End If
End If
i = i + 1
Loop
myr.Update
myarray = Nothing
myarrct = 0
Loop
Set fs = Nothing
Set f = Nothing
ts.Close
myr.Close
mydb.Close
import_csv_file_to_SQL = True
End Function
'------------------------------------------------------------------------
Function MinNumb(a, b)
Dim aa As Double
Dim bb As Double
If a < b Then
MinNumb = a
Else
MinNumb = b
End If
End Function
'------------------------------------------------------------------------
Function ns(Stri) As String
On Error Resume Next
Dim result As String
If IsNull(Stri) Then
result = ""
Else
result = Stri
End If
ns = result
End Function
'-----------------------------------------------------------------------
Function remove_symbol(Stri, Sym)
On Error GoTo err_symbol
Dim result
result = Stri
If (result = "") Or (IsNull(result)) Then Exit Function
Do While ((Mid(result, 1, 1) = Sym) Or (Mid(result, Len(result), 1) = Sym))
If Mid(result, 1, 1) = Sym Then
result = Mid(result, 2, Len(result) - 1)
Else
If Mid(result, Len(result), 1) = Sym Then
result = Mid(result, 1, Len(result) - 1)
End If
End If
Loop
remove_symbol = result
err_symbol:
remove_symbol = result
Exit Function
End Function
'-------------------------------------------------------------------------
From: https://bytes.com/topic/access/insights/585240-import-csv-comma-separated-value-file