Results 1 to 8 of 8
  1. #1
    darls15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8

    Combining, linking to/importing and extracting .gz ZIP files using VBA

    Hi Everyone

    I'm really hoping someone can help me with the following as I have no idea how to achieve the outcome I need or if it's even possible.

    I have a directory location which contains 11 folders and within them up to 100 gz (ZIP) files in each.

    Structure goes something like...

    Directory = C:\Users\user104\App_Data\

    The 11 sub-folders within App_Data directory are as follows...
    Instance_01
    Instance_02
    Instance_03
    Instance_04
    Instance_05
    Instance_06
    Instance_07
    Instance_08
    Instance_09
    Instance_10
    Instance_11

    Each of these 11 sub-folders contains multiple .gz (ZIP) files (some have up to 100 files). Most of these ZIP files are unique, however nearly all of the 11 instance folders have multiple "requests" .gz files (the number of these can vary) as shown below. The sub-folders (instances) may not contain the exact same files or number of files. All the same files in each of the instances, all contain the same columns and names. GZip file examples include:
    • account_dim-00450-e2067f5f5.gz
    • score_fact-00340-2876ab7e.gz
    • section_dim-06890-2115nr3p.gz
    • requests-02040-c1e2b025.gz
    • requests-10006-c6d24a3r.gz
    • requests-00500-c9bjk50b.gz


    One thing about the files, they all have different/changing filenames upon refresh, e.g. account_dim-00450-e2067f5f5.gz could change to account_dim-030900-a186g2e4.gz. The first part (before the "-" doesn't change) and is only part of the filename I'm really interested in importing, e.g. account_dim, score_fact, section_dim, requests.

    Ultimately, this is what I need to achieve first and foremost in the most effective and efficient way (keeping database performance in mind) is...

    1. Import into MSAccess and extract the ZIP files in each sub-folder (Instance_01 to Instance_11)

    2. As part of the import process:
    (a) combine all files with the same name (from every folder) and drop the changing part "-" to leave the first part as the table name, e.g. account_dim, score_fact, section_dim, requests
    (b) include a column/field in each table to identify which "instance" the file came from, (e.g. "Instance_07","Instance_02")

    I’ve searched extensively to find something that I can understand and replicate but I've had zero luck getting anything to work due to my limited knowledge with this.

    I’ve attached a tab separated text file with separate sections containing example data for the 5 files mentioned above (file names are above each set of data).

    I’d appreciate any assistance with working this out.

    Many thanks


    Darls15

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    I have VBA to extract from Windows compression (zip) folder, however, I doubt the code will work for gz.

    Instead of providing a text file, why not a gz file?

    Some of the data has inconsistent structure, data is not lining up in columns.

    Would this be a recurring process?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    darls15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8
    Hi June7

    I didn't attach the sample gz files as they weren't an accepted file format and I didn't realise I could just zip them. I have now converted the text files to gz file format and attached them as a zipped folder.

    The files in all 11 "instance" folders will be completely replaced with updated files on a regular basis to refresh the data.

    Please let me know if you need anything else.

    Thanks
    Darls15
    Attached Files Attached Files

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

  5. #5
    darls15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8
    CJ_London, thanks this looks very promising. I'm not able to test it today but will tomorrow and let you know how it goes 😊

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code in that link requires install of WinZip.
    Presume you have since you have so many gz files.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    darls15 is offline Novice
    Windows 10 Access 2016
    Join Date
    Aug 2019
    Posts
    8
    Hi June7 & CJ_London

    I've tried the code on that link with my directory where all my "instance" folders are and it doesn't work. I don't have WinZip, however I do have 7Zip. Would this be the issue why it doesn't work?

    Sub extractAllFiles()

    Dim MyObj As Object, MySource As Object, file As Variant
    Dim shellStr As String

    file = Dir("C:\Users\user104\App_Data")
    While (file <> "")
    If InStr(1, file, ".gz") > 0 Then
    shellStr = "C:\Program Files\7-Zip\7zFM.exe -e C:\Users\user104\App_Data" & file & " C:\Users\user104\App_Data"
    Call Shell(shellStr, vbHide)
    End If
    file = Dir
    Wend

    End Sub

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Code shows a missing backslash in the file path concatenation. You probably have it in your actual code but pasting into post dropped it because you did not enclose in CODE tags. Use forum CODE tags to keep your code intact.

    Apparently -e is not a valid switch for 7-Zip but there is a command e
    Review https://www.dotnetperls.com/7-zip-examples
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 07-21-2017, 01:07 PM
  2. Replies: 4
    Last Post: 06-23-2017, 05:17 PM
  3. Extracting number of files found by module
    By JeroenMioch in forum Access
    Replies: 4
    Last Post: 01-21-2016, 11:43 AM
  4. extracting pdf files question
    By slimjen in forum Programming
    Replies: 3
    Last Post: 03-11-2015, 08:45 AM
  5. Replies: 8
    Last Post: 12-21-2011, 05:50 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums