CODEWITHSUNDEEP
excelvbacsv
Brocodile
Brocodile

Reputation: 67

remove multiple headers after importing csv files in excel

I need to combine hundreds of csv files with the same format to one without having duplicate headers using an excel makro. I can select the files I want to import (and import them) with the following code:

Dim dateien, i, lastrow
lastrow = 1
dateien = Application.GetOpenFilename _
("csv-Dateien (*.csv), *.csv", MultiSelect:=True)

If IsArray(dateien) Then
    For i = 1 To UBound(dateien)
        Workbooks.Open dateien(i), local:=True
        With ThisWorkbook.Sheets(1)
            ActiveSheet.UsedRange.Copy Destination:=.Range("A" & lastrow)
            lastrow = .UsedRange.Rows.Count + 1
        End With
        ActiveWorkbook.Close False
    Next i
End If

However i don't really have a clue how to remove the duplicate headers...

Upvotes: 1

Views: 1185

Answers (1)

Dave
Dave

Reputation: 4356

I'd take the approach of opening each file in a FileSystemObject, reading all its data in and then firing it back out without the headers myself:

Dim dateien, i, lastrow
lastrow = 1
dateien = Application.GetOpenFilename _
("csv-Dateien (*.csv), *.csv", MultiSelect:=True)

dim oFso : Set oFso = CreateObject("Scripting.FileSystemObject")
Dim oSourceFile, oTargetFile
Set oTargetFile = oFso.CreateTextFile("pathtofilehere", True)
Dim sArray()

If IsArray(dateien) Then
    For i = 1 To UBound(dateien) ' Arrays in VBA index from zero, not 1 - you're skipping the first element in dateien
        ReDim sArray(0)
        Set oSourceFile = oFso.OpenTextFile(dateien(i), 1) ' open for reading
        While Not oSourceFile.AtEndOfStream ' while there is data in the file
            sArray(Ubound(sArray)) = oSourceFile.ReadLine  ' add the line to an array
            ReDim Preserve sArray(UBound(sArray)+1) ' increase size of the array by 1
        Wend
        ' Now we have the whole file in an array
        For myLoop = 1 to UBound(sArray) ' Loop from 1 and we skip the header line in the file
            oTargetFile.WriteLine sArray(myLoop) ' write array values into file
        Next myLoop   ' repeat for each line in the array
    Next i    ' repeat for each file in dateien
End If

Upvotes: 1

Related Questions