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