VBA, Excel 2010, Need to change headers in .csv files

[weenie]

Hello,

I have .csv files that have some headers mislabeled. I need to open these files and correct headers.
These are:

1. csv file name is the sheet name which will vary (no generic sheet1, etc)
2. headers always in row 1,
3. start at col H
4. ending column varies

I have attached sample file of above description & another excel file called "Change_Column_Headers" has 'Sheet1' with mislabeled headers in col A & correct headers in Col b.

How do I write code:

1. go through files
2. replace those headers
3. save file

Thanks,
weenie

[YasserKhalil]

Hello weenie
Try this code in your xlsm file

Sub LoopCSVFiles()
    Dim wbk         As Workbook
    Dim wsh         As Worksheet
    Dim found       As Variant
    Dim strFolder   As String
    Dim strFile     As String
    Dim rng         As Range
    Dim c           As Long
    Dim lc          As Long


    With Application
        .ScreenUpdating = False
        .Calculation = xlManual
        .DisplayAlerts = False
        .AskToUpdateLinks = False
    End With
        Set wsh = ThisWorkbook.Worksheets("column_headers")
        Set rng = wsh.Columns(1)
    
        'Change To Your Folder Path For CSV Files
        strFolder = ThisWorkbook.Path & "\"
        strFile = Dir(strFolder & "*.csv")
    
        Do While strFile <> ""
            Set wbk = Workbooks.Open(strFolder & strFile)
            lc = wbk.Worksheets(1).Cells(1, Columns.Count).End(xlToLeft).Column
    
            For c = 8 To lc
                found = Application.Match(wbk.Worksheets(1).Cells(1, c).Value, rng, 0)
    
                If Not IsError(found) Then
                    wbk.Worksheets(1).Cells(1, c).Value = wsh.Cells(found, 2).Value
                End If
            Next c
    
            wbk.Close SaveChanges:=True
            strFile = Dir
        Loop
    With Application
        .AskToUpdateLinks = True
        .DisplayAlerts = True
        .Calculation = xlAutomatic
        .ScreenUpdating = True
    End With


    MsgBox "Done...", 64
End Sub

[weenie]

Wow!! Runs fast
Thank you so much YasserKhali

[snb]

Replace G:\OF\ by your foldername.

Sub M_snb()
   sn = ThisWorkbook.Sheets("column_headers").Cells(1).CurrentRegion
   
   sp = Split(CreateObject("wscript.shell").exec("cmd /c dir ""G:\OF\*.csv"" /b/s").stdout.readall, vbCrLf)
   
   With CreateObject("scripting.filesystemobject")
        For j = 0 To UBound(sp) - 1
           c00 = .opentextfile(sn(j)).readall
           For jj = 1 To UBound(sn)
            c00 = Replace(c00, sn(jj, 1), sn(jj, 2))
           Next
           .createtextfile(sn(j)).write c00
        Next
   End With
End Sub

[YasserKhalil]

You're welcome. Glad I can offer some help
@snb I tried to test your code but I encountered error at this line

c00 = .opentextfile(sn(j)).readall

Another question: is it possible to use a variable for the path instead of typing it in the second line directly?

[snb]

Debugging is the fast way to learn VBA....

Sub M_snb()
    c00 = "G:\OF\"
    sn = ThisWorkbook.Sheets("column_headers").Cells(1).CurrentRegion
    sp = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s").stdout.readall, vbCrLf)
     
    With CreateObject("scripting.filesystemobject")
        For j = 0 To UBound(sp) - 1
            c00 = .opentextfile(sp(j)).readall
            For jj = 1 To UBound(sn)
                c00 = Replace(c00, sn(jj, 1), sn(jj, 2))
            Next
            .createtextfile(sp(j)).write c00
        Next
    End With
End Sub

[YasserKhalil]

That's amazing and awesome. Thanks a lot for great solution
I always use Debugging to learn but I can't understand everything (I am keep trying)
Best Regards

[weenie]

looking closer at data. I noticed the unique id number in column b was being changed. Not sure why since not one of the items needing to be changed. I'll submit file and can see after code it changes unique id number 417330000000. how do I stop it from changing any data that is not on header row 1?

Thanks,
irene

[weenie]

snb I tried running code. I see the cmd window pop open & close but nothing happens with the files. Not sure what I'm doing wrong. as you see from code below I changed to folder location. When it didn't work I thought maybe I need to add the name of excel workbook but that didn't work either.

Sub M_snb()    c00 = "C:\test-change"
    'sn = Change_Column_Headers.Sheets("column_headers").Cells(1).CurrentRegion
    sn = ThisWorkbook.Sheets("column_headers").Cells(1).CurrentRegion
    sp = Split(CreateObject("wscript.shell").exec("cmd /c dir """ & c00 & "*.csv"" /b/s").stdout.readall, vbCrLf)
     
    With CreateObject("scripting.filesystemobject")
        For j = 0 To UBound(sp) - 1
            c00 = .opentextfile(sp(j)).readall
            For jj = 1 To UBound(sn)
                c00 = Replace(c00, sn(jj, 1), sn(jj, 2))
            Next
            .createtextfile(sp(j)).write c00
        Next
    End With
End Sub

[weenie]

Hope this will help with other file attached last post.

Thanks,
weenie

[snb]

If you open one of the csv files after running the code you will notice its columnheaders contain the correct texts.

[weenie]

I must be doing something wrong because I opened cvs files and nothing has changed. Also would the dates & sizes of the Csv file change? I also noticed those tones & sizes have not changed
I dropped in 26 csv files in folder 'tester'.
I have excel workbook open & located on desktop with
Microsoft Visual Basic window open to run the code.


snb I appreciate your time & help.

Thanks
weenie

[snb]

In that case the texts in the csv file do not match the texts in the Excelfile column A ( as was the case in your sample files).