Help Needed for Data Extraction from Hundreds of Zipped Files!

[adgezi]

I'm now in a headache to extract data from files and really wish somebody could help me out of this.

The problem is that there is a directory, e.g. I:\john

In this directory, there are 100 zipped files. In each zipped file, there are 26 different .csv files.

I only want to extract the data from one of these files. The file includes the data I want has the file name starting with ABC_DATA_.

Then similarly, repeat this process to extract this data from all 100 zipped files.

All the extracted data might be stored in a new excel or text file.

Really appreciate it for your guidance or help if somebody could provide some sample VBA code for this problem.

John

 

[adgezi]

Could somebody help on this? Thank you in advance.

 

[Sektor]

The code create temp directory where the workbook located. It iterates over each zip files, unpack its content to temp dir. Then you insert your logic of processing them. csvFile is File object. It has Path property which gives you full path to this extracted file. After you process it, code cleans up temp folder.

Sub ProcessCSV()


    Dim ofile, o, fso As Object, zipFile, zipFile2, csvFile, tempDir
        
    Set fso = CreateObject("Scripting.FileSystemObject")
    
    'Create temp directory where the workbook is located
    tempDir = ThisWorkbook.Path & "\UNZIPPED_FILES"
    On Error Resume Next
    MkDir tempDir
    On Error GoTo 0
    
    ' Iterate over each zip file in "I:\John" folder
    For Each zipFile In fso.GetFolder("I:\john").Files


        'Copy each file to the destination folder
        Set o = CreateObject("Shell.Application")
        For Each ofile In o.Namespace(zipFile.Path).items
            o.Namespace(tempDir).CopyHere (ofile)
        Next
        Set o = Nothing
        
        For Each csvFile In fso.GetFolder(tempDir).Files
            If csvFile.Name Like "ABC_DATA_*" Then Exit For
        Next
        
        'Here we have our csvFile File object
        'Do something with them...
        
        
        'Delete every file in temp folder after processing CSV files
        For Each zipFile2 In fso.GetFolder(tempDir).Files
            zipFile2.Delete True
        Next
        
    Next
    
    fso.DeleteFolder tempDir, True 'Delete temp folder


End Sub