Amend column contents within multiple CSV files where all of the files contain columns with the same order and format

[TonWales]

I have a folder containing multiple CSV files. All of the files contain columns in the same order and format. One of the columns (column C) has incorrect data and needs to be replaced with correct data.

So, I wish to remove the contents of Column C (row 11 through to row 2029) in all of the CSV files within the same folder and replace with contents (Column C, from Row 11 to Row 2029) from a 'master' CSV file.

I have been struggling to figure out a macro to go through every CSV in the folder that will do the necessary amendments efficiently. Can this be done in Excel using VBA?

I'd be very grateful if someone could help me with a suitable macro. Thank you

 

[duggie33]

How many csv files need to be updated? I am sure it can be done with a macro by looping through all the csv files. Are all the files in a specific folder? Is the master csv file in the same folder? How big are the csv files in KB? Do you have some code that is not working as expected?

Doug

 

[TonWales]

Hi, I have over 600 csv files to amend but this can be done in several chunks if that makes things easier.
Yes, all the files, including the master csv file, are in a specific folder. File size is small, between 4 - 8KBs.
I've had a few attempts at writing code (essentially amending what I've found on the web as I'm very new to VBA) but the difficulty is specifying the cell range within a specific column that requires amending within in the macro. I hope this makes sense?

 

[duggie33]

Right, if you said 20 files, I would have said it would be quicker to open them one at a time and copy/paste. There may be a way to do it without opening each csv in Excel, but I am not aware of how to do that. I would get the macro to loop through all csv files that need to change, open each in Excel, replace the data as specified, save, and close. I have no idea how long it would take to process 600 files. I do know it would be much faster than doing it manually

I have to go do yard work, I will contemplate an approach while working. Maybe someone else will have this worked-out before I get a chance to get back to it... In any case, I believe to be possible.

Maybe post some of your code and identify where it doesn't seem to work right...

Doug

 

[TonWales]

Thanks very much for looking into this

Tony

 

[Herakles]

I have a folder containing multiple CSV files. All of the files contain columns in the same order and format. One of the columns (column C) has incorrect data and needs to be replaced with correct data.

So, I wish to remove the contents of Column C (row 11 through to row 2029) in all of the CSV files within the same folder and replace with contents (Column C, from Row 11 to Row 2029) from a 'master' CSV file.

I have been struggling to figure out a macro to go through every CSV in the folder that will do the necessary amendments efficiently. Can this be done in Excel using VBA?

I'd be very grateful if someone could help me with a suitable macro. Thank you

Try this but back up all of your files before you do.

The Master.csv file needs to be loaded for this to work.

Try it on a few files first as it takes about 6 minutes.

Private Sub subSwopData()
Dim strPath As String
Dim strFile As String
Dim intCount As Integer
Dim dteTimeStart As Date

    dteTimeStart = Now()
    
    ActiveWorkbook.Save
    
    strPath = "C:\Dump\Amend column contents within multiple CSV files\"
    ' CHANGE THE LINE ABOVE AS APPROPRIATE.
    
    strFile = Dir(strPath)

    Application.ScreenUpdating = False
    
    Do While strFile <> ""
        If Right(strFile, 3) = "csv" And strFile <> "Master.csv" Then
            Workbooks.Open strPath & strFile
            ActiveWorkbook.Sheets(1).Range("C11:C2029").Value = Workbooks("Master.csv").Sheets(1).Range("C11:C2029").Value
            ActiveWorkbook.Close savechanges:=True
            intCount = intCount + 1
        End If
        strFile = Dir
    Loop
    
    Application.ScreenUpdating = True
        
    MsgBox intCount & " CSV files updated from the master file." & vbCrLf & _
        "Start Time : " & Format(dteTimeStart, "HH:MM:SS") & vbCrLf & "End Time : " & Format(Time(), "HH:MM:SS"), vbOKOnly, "Confirmation"
    
End Sub

 

[Herakles]

Try this but back up all of your files before you do.

The Master.csv file needs to be loaded for this to work.

Try it on a few files first as it takes about 6 minutes.

Private Sub subSwopData()
Dim strPath As String
Dim strFile As String
Dim intCount As Integer
Dim dteTimeStart As Date

    dteTimeStart = Now()
   
    ActiveWorkbook.Save
   
    strPath = "C:\Dump\Amend column contents within multiple CSV files\"
    ' CHANGE THE LINE ABOVE AS APPROPRIATE.
   
    strFile = Dir(strPath)

    Application.ScreenUpdating = False
   
    Do While strFile <> ""
        If Right(strFile, 3) = "csv" And strFile <> "Master.csv" Then
            Workbooks.Open strPath & strFile
            ActiveWorkbook.Sheets(1).Range("C11:C2029").Value = Workbooks("Master.csv").Sheets(1).Range("C11:C2029").Value
            ActiveWorkbook.Close savechanges:=True
            intCount = intCount + 1
        End If
        strFile = Dir
    Loop
   
    Application.ScreenUpdating = True
       
    MsgBox intCount & " CSV files updated from the master file." & vbCrLf & _
        "Start Time : " & Format(dteTimeStart, "HH:MM:SS") & vbCrLf & "End Time : " & Format(Time(), "HH:MM:SS"), vbOKOnly, "Confirmation"
   
End Sub

Change the path in the above code where indicated.

 

[TonWales]

Dear Herakles,

That's amazing! Your code does exactly what I required.
Thanks very much indeed, I'm most grateful

Cheers

 

[Herakles]

Dear Herakles,

That's amazing! Your code does exactly what I required.
Thanks very much indeed, I'm most grateful

Cheers

That will save you a lot of time. Glad it helped.