Multiple files in different column order

[PPDD129056]

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?

[#2]

@PPDD129056 

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

www.vba-tanker.com

[#3]

Thanks for your reply. But my CSV files don't have the heading and the content of the data is in Chinese. FIle1: 蒙AJ6140 2008年09月26日 15272419841224001X 内蒙古呼和浩特市土勘院2号楼1单元4号 林凯 13654885004 蒙AJ7961 2008年09月27日 150104197108073611 内蒙古呼和浩特市玉泉区桃花乡呼清路呼市第十二中学44号 刘文义 13947139489 蒙AJ9546 2008年09月27日 150102198110063043 内蒙古呼和浩特市新城区健康街19号院2号楼2单元15号 刘宣 15047888294 File2: 童冲山 310107530103505 13901701395 甘泉二村53号9室 20040514 金亚新 310222196203061452 13501929685 迎圆路300弄65支弄4号4 20040514 卢海泉 310107560617121 13818176240 上海市普陀区金沙江路长风一村 20040514 FIle3: 蒙DD6909 袁幸福 13694773726 150425198511090019 内蒙古自治区赤峰市克什克腾旗 2008年10月22日 蒙DD6910 长青 13847631979 150426197904145377 内蒙古自治区赤峰市翁牛特旗乌丹镇 2008年10月28日 蒙DD6911 汤海浩 13848872416 150423197409100017 内蒙古自治区赤峰市巴林右旗 2008年10月22日 What I hope is let my files reorder like Name (Usually composed of 2 to 3 Chinese characters) ID (Usually composed of 15 or 18 digits, sometimes the 18th will be X) Phone (Usually composed of 11 digits) License plate Address and other moves to the back column