May 25 2019 07:33 AM
Hello, I have over 1000 CSV files with different column order. For example,
file1: name, phone, date, question
file2: phone, date, question, name
file3: question, name, phone, date
how can I reorder the columns of them in a quick way?
May 25 2019 08:31 AM - edited May 25 2019 10:44 PM
Hi, the following vba macro read all csv and change the columns.
Function GetColumn(strTitle As String) As Integer
Dim rngFind As Range
Set rngFind = ActiveSheet.Rows(1).Find(what:=strTitle, lookat:=xlWhole)
If rngFind Is Nothing Then
GetColumn = 0
Else
GetColumn = rngFind.Column
End If
End Function
Sub Readall()
Dim strPfad As String
Dim strDatei As String
Dim Quelle As Workbook
Dim strName As String
Dim lngRowFree As Long
Dim lngRowMax As Long
Dim lngCol As Long
strPfad = ThisWorkbook.Path & "\"
strDatei = Dir(strPfad & "*.csv")
Application.DisplayAlerts = False
Do While strDatei <> ""
Workbooks.OpenText Filename:=strPfad & strDatei, Semicolon:=True, local:=True
Set Quelle = ActiveWorkbook
With Quelle.Worksheets(1)
lngRowMax = Quelle.Worksheets(1).UsedRange.Rows.Count
lngCol = GetColumn("name")
.Range(.Cells(1, lngCol), .Cells(lngRowMax, lngCol)).Copy Destination:=.Cells(1, 4)
lngCol = GetColumn("phone")
.Range(.Cells(1, lngCol), .Cells(lngRowMax, lngCol)).Copy Destination:=.Cells(1, 5)
lngCol = GetColumn("question")
.Range(.Cells(1, lngCol), .Cells(lngRowMax, lngCol)).Copy Destination:=.Cells(1, 6)
.Range("A:C").Delete
Quelle.SaveAs ThisWorkbook.Path & "\" & strDatei
Quelle.Close
End With
strDatei = Dir()
Loop
Application.DisplayAlerts = True
End Sub
regards
Bernd
May 26 2019 02:18 AM