Can’t edit CSV because it has too many columns. How to search and replace list of strings in CSV?

[Tanquen]

I have a large odd CSV export that has too many columns to open without error. I need to replace about a thousand strings in the file. I ran a crude macro that extracted all the strings I need to find and replace. So I not have a one column listing the old strings and a new column listing the new strings.

What is the best way to search and replace all the strings in the original file? There are two instances of each string in the file and the original CSV must remain unchanged in any other way. I know Excel like to add commas for empty cells and such.

Other than manually doing hundreds of search and replace actions in notepad or something is there a cool VBA way to open the CSV as a text file and parse through it and then save it?
Thanks.

 

[yky]

Can't you do it with notepad or Word? Notepad can do "replace all".

 

[Tanquen]

Yes.

"Other than manually doing hundreds of search and replace actions in notepad or something"

But.

There are over 500 unique strings that I would need to search for. That is why I was also asking about using a list. Like I have a list of 500 strings to search for in another file. I’ve seen some examples but they add lines to the file or they check one row and then move on. This CSV has hundreds of unique strings in a row.

 

[yky]

Open the csv file in Word and follow the example below.

Sub Macro2()

    With Selection.Find
        .Text = "word"
        .Replacement.Text = "wordword"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    
        With Selection.Find
        .Text = "office"
        .Replacement.Text = "officeoffice"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll

End Sub

 

[Tanquen]

I'll have an array of 500+ strings but this test works. It's very slow with just the two test strings though.

Sub Macro2()


    Dim ArrayOLD(1 To 2) As String
    Dim ArrayNEW(1 To 2) As String
    
    ArrayOLD(1) = "SPWT_SLC504.SLC504.N17:52"
    ArrayOLD(2) = "SPWT_SLC504.SLC504.N17:152"
    
    ArrayNEW(1) = "{[Process_PLC]B3[0].1}"
    ArrayNEW(2) = "[Process_PLC]B3[10].1]"


    For i = 1 To UBound(ArrayOLD)
       
        With Selection.Find
            .Text = ArrayOLD(i)
            .Replacement.Text = ArrayNEW(i)
            .Forward = True
            .Wrap = wdFindContinue
            .Format = False
        End With
        Selection.Find.Execute Replace:=wdReplaceAll
        
    Next i


End Sub