Delete CSV files if empty in folder

[davidp13]

Hi. I have been trying for a while now to delete csv files in a directory if these are empty/blank. Basically I have a routine that updates multiple csv files in a directory and within certain csv files i will delete all the values as i dont need to use them, but some files will be left with content. The ones with empty value should be deleted.

The best I get right at the moment is to delete all the files.

Any help/guidance pls?

 

[Fazza]

I wonder how the updates are done.

If via code, can you tweak the code that updates the files to instead read from the files? And the ones with no records are then deleted.
Or otherwise a SELECT query against each file, and those that return zero records get deleted.

 

[davidp13]

I have tried this, but it deletes all the files.

Public Sub Setup3()'Loop through all the files in the directory by using Dir$ function
Dim MyFile As String
MyFile = Dir$("C:\Users\admi n\Google Drive\Everest\MSA\DailyMSA - New\*.csv")
Do While MyFile <> ""
    
    ActiveSheet.Range("A1").Select
    'Sheets(1).Select.Range("A:A").Select
    'If ActiveSheet.Range("A1").SpecialCells(xlCellTypeConstants, xlTextValues).Cells.Count = 0 Then
    
        If (ActiveSheet.Range("A1").Value < 1) Then
           Kill "C:\Users\admi n\Google Drive\Everest\MSA\DailyMSA - New\" & MyFile


        End If
       
    'need to specify full path again because a file was deleted 1
    MyFile = Dir$("C:\Users\admi n\Google Drive\Everest\MSA\DailyMSA - New\*.csv")
Loop
End Sub

 

[Fazza]

Please back up files before running the code. And maybe run on some test files first.

Decision on whether to delete a file is based solely on cell A1 being empty or not. If this isn't good for you, please modify or advise what would be a better check.

Sub trythis()

    Const sFILE_PATH_TO_CHECK As String = "C:\Users\admin\Google Drive\Everest\MSA\DailyMSA - New"


    Dim sFileName As String
    Dim sFileFullName As String
    
    Application.ScreenUpdating = False
    sFileName = Dir(sFILE_PATH_TO_CHECK & "\*.csv")
    Do While Len(sFileName)
        sFileFullName = sFILE_PATH_TO_CHECK & "\" & sFileName
        If bFileIsEmptyInFirstCell(FileName:=sFileFullName) Then Kill sFileFullName
        sFileName = Dir()
    Loop
    
End Sub


Private Function bFileIsEmptyInFirstCell(ByVal FileName As String) As Boolean


    'just opens file and checks if first cell is empty
    
    Dim wbkFileToCheck As Excel.Workbook


    Set wbkFileToCheck = Workbooks.Open(FileName:=FileName)
    bFileIsEmptyInFirstCell = Len(Range("A1").Value) = 0
    wbkFileToCheck.Close
    Set wbkFileToCheck = Nothing
    DoEvents


End Function

 

[davidp13]

Thanks, it worked 99%. I needed to change one line in the function section and add Sheets(1). Works 100% now. See below:

bFileIsEmptyInFirstCell = Len(Sheets(1).Range("A1").Value) = 0

 

[Fazza]

Well done.

I 'm unsure what difference that change makes. Do some csv files have multiple worksheets?

 

[davidp13]

I'm speculating here, but it might be because the CSV file has a sheet that is named and not just Sheet1. I have noticed that I get better feedback when I specify it.

 

[Fazza]

thanks