在导入到MS Access之前替换.csv文件中的字符串

Question

我需要将多个csv文件导入一个访问表，但在导入之前，我想替换"quot；用"&"；。有办法做到这一点吗？现在我有这样的代码，只导入文件：

Private Sub bImportFiles_Click()
On Error GoTo bImportFiles_Click_Err
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim ts, tse As Date
ts = Now() 'Initializare start import
'Import fisiere colectare
strFolderPath = "C:Userscosticlatest"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files
For Each objF1 In objFiles
If Right(objF1.Name, 3) = "csv" Then
DoCmd.SetWarnings False
DoCmd.TransferText acImportDelim, "specs", "ALL", strFolderPath & objF1.Name, False
'DoCmd.RunSQL "INSERT INTO COLL_ALL ( Data_Inc, CNP, CB, CN, COM, N_UNITS, PUAN, Price, SN_ACT )"
Name strFolderPath & objF1.Name As "C:Userscosticlaimport" & objF1.Name 'Move the files to the archive folder
End If
Next
Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
'tse = Now()
DoCmd.SetWarnings True
'MsgBox ("Import done !!! start at:" & ts & " end at:" & tse)

MsgBox ("Import ALL done !!! " & _
"start at: " & ts & " end at: " & tse)

bImportFiles_Click_Exit:
Exit Sub
DoCmd.SetWarnings True
bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub

Answer 1

您可以使用VBA的文件I/O操作打开文件，一次性导入所有数据，替换逗号并将其输出到新文件。下面的代码应该让你开始：

Sub sReplaceDoubleComma(strInFile As String)
On Error GoTo E_Handle
Dim intInFile As Integer
Dim strOutFile As String
Dim intOutFile As Integer
Dim strInput As String
intInFile = FreeFile
Open strInFile For Input As intInFile
strOutFile = "J:test-datatemp.txt"
intOutFile = FreeFile
Open strOutFile For Output As intOutFile
strInput = Input(LOF(intInFile), intInFile)
Print #intOutFile, Replace(strInput, ",,", ",")
Close #intInFile
Close #intOutFile
'    Kill strInFile
'    Name strOutFile As strInFile
sExit:
On Error Resume Next
Exit Sub
E_Handle:
MsgBox Err.Description & vbCrLf & vbCrLf & "sReplaceDoubleComma", vbOKOnly + vbCritical, "Error: " & Err.Number
Resume sExit
End Sub

一旦您对此感到满意，就可以在末尾取消对这两行的注释，以替换输入文件。然后，您可以从现有代码的一部分调用此过程：

For Each objF1 In objFiles
If Right(objF1.Name, 3) = "csv" Then
DoCmd.SetWarnings False
Call sReplaceDoubleComma(strFolderPath & objF1.Name)
DoCmd.TransferText acImportDelim, "specs", "ALL", strFolderPath & objF1.Name, False
Name strFolderPath & objF1.Name As "C:Userscosticlaimport" & objF1.Name 'Move the files to the archive folder
End If
Next

Answer 2

链接，不要导入，文件，你有一个链接表。

现在，使用这个链接表作为simpelselect查询中的源，在这里您可以根据需要过滤、修改和转换数据并对字段进行别名。

然后在追加查询中将此查询用作源，该查询将把记录添加到COLL_ALL表中。