How to remove text delimiters on 10,000 csv files?

[mel20]

Hi Everyone,

I am hoping someone might be able to help me. I have 10,000 files that I need to remove the " text delimiters from so that they can open in another third party programme. They are saved as .dat files but open in excel through the text import function. Is there any way to do this as a batch process rather than having to open every file and resave it manually?!

Thanks,

Mel

 

[John_w]

It's not entirely clear from your post and screenshot. Do you want to remove the double quote text qualifiers? If so, try this macro, testing it on a folder containing a few .dat files. The modified files are saved in the MODIFIED subfolder, which is created if it doesn't exist.

Public Sub Find_Replace_In_Files()

    Dim FSO As Object
    Dim FSfolder As Object, FSfile As Object, FSstream As Object
    Dim inputFolder As String, outputFolder As String
    Dim allData As String
   
    With Application.FileDialog(msoFileDialogFolderPicker)
        .Title = "Select folder containing files to be modified"
        .InitialFileName = ThisWorkbook.Path
        If Not .Show Then Exit Sub
        inputFolder = .SelectedItems(1) & "\"
        outputFolder = inputFolder & "MODIFIED\"
    End With
       
    Set FSO = CreateObject("Scripting.FileSystemObject")
           
    If FSO.FolderExists(outputFolder) Then
        Set FSfolder = FSO.GetFolder(outputFolder)
    Else
        Set FSfolder = FSO.CreateFolder(outputFolder)
    End If
   
    For Each FSfile In FSO.GetFolder(inputFolder).files
        If FSfile.Name Like "*.dat" Then
            Set FSstream = FSO.OpenTextFile(FSfile.Path)
            allData = FSstream.ReadAll
            FSstream.Close
            allData = Replace(allData, Chr(34), "")
            Set FSstream = FSO.CreateTextFile(outputFolder & FSfile.Name)
            FSstream.Write allData
            FSstream.Close
        End If
    Next
   
    MsgBox "Done"
   
End Sub

 

[mel20]

Hi John,

Thank you for the really quick reply and apologies it wasn't clear. Your interpretation was right that I want to remove the double quote marks throughout the file.

I am a little stuck as it seems to be looking for somewhere to save to when I run the macro and it can't find the desired file. I have tried this:

Public Sub Find_Replace_In_Files()

Dim FSO As Object
Dim FSfolder As Object, FSfile As Object, FSstream As Object
Dim inputFolder As String, outputFolder As String
Dim allData As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Title = "C:\Users\M.SMITH\OneDrive - University\Desktop\Data"
.InitialFileName = ThisWorkbook.Path
If Not .Show Then Exit Sub
inputFolder = .SelectedItems(1) & "\"
outputFolder = "C:\Users\M.SMITH\OneDrive - University\Desktop\Data\MODIFIED"
End With

Set FSO = CreateObject("Scripting.FileSystemObject")

If FSO.FolderExists(outputFolder) Then
Set FSfolder = FSO.GetFolder(outputFolder)
Else
Set FSfolder = FSO.CreateFolder(outputFolder)
End If

For Each FSfile In FSO.GetFolder(inputFolder).Files
If FSfile.Name Like "*.dat" Then
Set FSstream = FSO.OpenTextFile(FSfile.Path)
allData = FSstream.ReadAll
FSstream.Close
allData = Replace(allData, Chr(34), "")
Set FSstream = FSO.CreateTextFile(outputFolder & FSfile.Name)
FSstream.Write allData
FSstream.Close
End If
Next

MsgBox "Done"

End Sub

Would it be ok to point out what I have done wrong please? No doubt something obvious so apologies for the silly question.

Thanks,

Mel

 

[John_w]

You shouldn't need to edit the code. Just run it exactly as I posted and it presents a folder browser dialogue allowing you to browse to the folder containing the .dat files. It then reads all the .dat files in that folder (that's why I suggested having only a few .dat files in the folder to test the macro, not all 10,000), and saves the modified version of each .dat file in the MODIFIED subfolder of the selected folder. The macro creates the MODIFIED subfolder if necessary.

If the test on the few .dat files is successful and the files are correctly modified, run the macro again, this time selecting the folder containing the 10,000 .dat files.