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

mel20

New Member
Joined
Mar 27, 2006
Messages
11
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
 

Attachments

  • Picture1.png
    Picture1.png
    207.8 KB · Views: 10

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

John_w

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,087
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.

VBA Code:
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

New Member
Joined
Mar 27, 2006
Messages
11
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

MrExcel MVP
Joined
Oct 15, 2007
Messages
7,087
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.
 
Last edited:

Forum statistics

Threads
1,148,018
Messages
5,744,355
Members
423,863
Latest member
teehexcel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top