Delete first 2 rows of each csv file in a folder - 1,000 CSV files

[ccondran08]

I have a folder that has over 1,000 excel CSV files that needs to be imported into an Access database. I have a tool that will do this but it turns out that the excel CSV files have the top 2 rows of blank data with the 3rd row having the headings of the data. Just wondering if anyone has an ideas on a macro to open up each file in the folder and loop through and delete the first 2 rows of each file ?

 

[Michael M]

This should do as required.
Copy all code into a Standard Module and procedd.....Make Sure you Do a backup of your data as this is UNTESTED
The code was originally written, I believe, by Jindon.....a great coder !!

Option Explicit
Sub FixCsvFiles()
    Dim SelectFolder As String
    Dim csvFiles As Variant
    Dim csvWb As Workbook
    Dim x As Integer
     'browse for folder with csv files
    On Error GoTo FixCsvFiles_Error
    SelectFolder = GetFolder("c:\")
    Application.ScreenUpdating = False
     'Check user did not cancel folder selection
    If SelectFolder = "" Then
        MsgBox "No Folder Selected - Cannot continue", vbCritical
        End
    End If
     
    SelectFolder = SelectFolder & "\"
    csvFiles = Dir(SelectFolder & "*.csv")
    Do While csvFiles <> ""
         
        Set csvWb = Workbooks.Open(SelectFolder & csvFiles)
        Rows("1:2").Delete
        x = x + 1
        csvWb.Close True
        csvFiles = Dir
    Loop
    Application.ScreenUpdating = True
    MsgBox "A total of " & CStr(x) & " files processed", vbInformation
    On Error GoTo 0
    Exit Sub
     
FixCsvFiles_Error:
     
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure FixCsvFiles of Module2"
End Sub
 
 
Function GetFolder(strPath As String) As String
    Dim fldr As FileDialog
    Dim sItem As String
     
     
    Set fldr = Application.FileDialog(msoFileDialogFolderPicker)
    With fldr
        .Title = "BROWSE TO FOLDER LOCATION WITH CSV FILES"
        .AllowMultiSelect = False
        .InitialFileName = strPath
        If .Show <> -1 Then GoTo NextCode
        sItem = .SelectedItems(1)
    End With
NextCode:
    GetFolder = sItem
    Set fldr = Nothing
End Function

 

[mrmmickle1]

Try something like this:

[COLOR=#0000ff]Option Explicit[/COLOR]


[COLOR=#0000ff]Sub[/COLOR] Delete_2_Rows()

  [COLOR=#0000ff]  Dim[/COLOR] Wkb        [COLOR=#0000ff]As[/COLOR] Workbook
    [COLOR=#0000ff]Dim [/COLOR]FileName[COLOR=#0000ff]   As String[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] strPath    [COLOR=#0000ff]As String[/COLOR]
    [COLOR=#0000ff]Dim[/COLOR] ThisWB     [COLOR=#0000ff]As String[/COLOR]
   
    ThisWB = ActiveWorkbook.Name   

    [COLOR=#0000ff]With[/COLOR] Application.FileDialog(3)
        .AllowMultiSelect = [COLOR=#0000ff]False[/COLOR]
        .ButtonName = "Select Folder"
        .InitialFileName = vbNullString
        .InitialView = 2
        .Title = "Select Folder"
    [COLOR=#0000ff]If[/COLOR] .Show [COLOR=#0000ff]Then[/COLOR]
        strPath = .SelectedItems(1) [COLOR=#006400]'Define the Folder FilePath based on the item the user has selected[/COLOR]
[COLOR=#0000ff]    End If[/COLOR]
[COLOR=#0000ff]    End With[/COLOR]
    
    FileName = Dir(strPath & "\*.csv", vbNormal)
   [COLOR=#0000ff] If[/COLOR] Len(FileName) = 0 [COLOR=#0000ff]Then Exit Sub[/COLOR]
  [COLOR=#0000ff]  Do Until[/COLOR] FileName = vbNullString
        [COLOR=#0000ff]If Not[/COLOR] FileName = ThisWB [COLOR=#0000ff]Then[/COLOR]
        
            [COLOR=#0000ff]Set[/COLOR] Wkb = Workbooks.Open(FileName:=strPath & "\" & FileName) [COLOR=#008000]'Open WorkBook[/COLOR]
            
            Rows("1:2").Delete Shift:=xlUp [COLOR=#008000]'Delete 2 Rows[/COLOR]
            
            Wkb.Close [COLOR=#0000ff]True[/COLOR] [COLOR=#008000]'Save File[/COLOR]
            FileName = Dir()
            
[COLOR=#0000ff]        End If[/COLOR]
[COLOR=#0000ff]    Loop[/COLOR][COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]End Sub[/COLOR]

 

[ccondran08]

Thanks Michael M, this works perfectly. Appreciate your help.

 

[Michael M]

Glad to help...