remove multiple headers after importing csv files in excel

Question

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...

Dave · Accepted Answer · 2016-06-15 11:47:10Z

1

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

edited Jun 15, 2016 at 11:47

answered Jun 14, 2016 at 9:19

Dave

4,3562 gold badges26 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Brocodile Over a year ago

Seems to be a good idea, however I get a compilation error at ReDim Preserve sArray(UBound(sArray)) (Array Already Dimensioned); If I declare sArray (), I get the runtime error Subscript out of range (Error 9) at sArray(UBound(sArray)) = oSourceFile.ReadLine

Dave Over a year ago

Oops. Fixed in code now... The trick is to declare it as dynamic initially with Dim sArray() then redimension it so it has the expected upper boundary for the first element with a ReDim

Brocodile Over a year ago

I still get Error 9 (Subscript out of range) for Set oSourceFile = oFso.OpenTextFile(dateien(i), 1) :(

Dave Over a year ago

Having checked with the code directly, it seems that the array returned by Application.GetOpenFileName is not zero indexed, rather indexed from 1. I've modified the answer; should work now. Let me know if there's any issues

Dave Over a year ago

I've moved the ReDim sArray(0) statement so that it resets for each file; previously it was always appending the other file's data to the original array. This will clear it out for each file and should properly resolve your header issue

|

Collectives™ on Stack Overflow

remove multiple headers after importing csv files in excel

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related