PDA

View Full Version : [SOLVED] VBA, Excel 2010, Need to change headers in .csv files



weenie
07-20-2017, 05:48 PM
Hello,

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


csv file name is the sheet name which will vary (no generic sheet1, etc)
headers always in row 1,
start at col H
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:

go through files
replace those headers
save file


Thanks,
weenie

YasserKhalil
07-21-2017, 12:16 AM
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
07-21-2017, 09:45 AM
Wow!! Runs fast :)
Thank you so much YasserKhali

snb
07-21-2017, 10:10 AM
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
07-21-2017, 10:28 AM
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
07-21-2017, 12:32 PM
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
07-21-2017, 01:14 PM
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
07-22-2017, 09:00 AM
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
07-22-2017, 09:11 AM
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
07-22-2017, 09:22 AM
Hope this will help with other file attached last post.

Thanks,
weenie

snb
07-22-2017, 11:38 AM
If you open one of the csv files after running the code you will notice its columnheaders contain the correct texts.

weenie
07-22-2017, 01:12 PM
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
07-23-2017, 04:12 AM
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).