Replace a string in a .csv file before import into MS Access

Question

I need to import multiple csv files into one access table, but before the import i would like to replace ",," with ",". Is there any way to do this? For now i've got this code that only imports the files:

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:\Users\costicla\test\"
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:\Users\costicla\import\" & 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

You can use VBA's File I/O operations to open a file, import all of the data in one go, replace the double commas and output it to a new file. The code below should get you started:

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-data\temp.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

Once you are happy that this works, you can uncomment the two lines towards the end to replace the input file. You can then call this procedure from within part of your existing code:

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:\Users\costicla\import\" & objF1.Name 'Move the files to the archive folder
    End If
Next