Extracting Data points from several thousand zip files

[Charlesalhin]

The Australia Electricity market makes data available on sent out generation for each power plant for each 5 minute period. Given that this is very much data. This data is public and can be found here www.nemweb.com.au - /REPORTS/ARCHIVE/Dispatch_SCADA/ - one zip file for each day. Within each one day zip file are 288 zip files - one for each 5minute period. Each 5 minute file lists all generators by code and the amount of energy sent out for that 5min period. I need a full year of data for one of the generators so will need to access 105,120 files - one for each 5minute period in a year. Naming conventions for the zip files are consistent and could be automatically updated based on the date and 5min period to scroll through the list. 5min file looks like below with DUID giving the code for each generator.

Any ideas on approach other than manual is greatly appreciated.

C	NEMP.WORLD	NEXT_DAY_ACTUAL_GEN	AEMO	PUBLIC	2/02/2022​	4:10:32​	357157376​	NEXT_DAY_ACTUAL_GEN	3.57E+08​
I	METER_DATA	GEN_DUID	1​	INTERVAL_DATETIME	DUID	MWH_READING	LASTCHANGED
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	BARCSF1	0​	1/02/2022 4:00​
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	BUTLERSG	8.799998​	1/02/2022 4:00​
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	CAPTL_WF	0​	1/02/2022 4:00​
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	CHALLHWF	3.1​	1/02/2022 4:00​
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	CLOVER	11.38​	1/02/2022 4:00​
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	CLUNY	5.962463​	1/02/2022 4:00​
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	CULLRGWF	-0.04​	1/02/2022 4:00​
D	METER_DATA	GEN_DUID	1​	1/02/2022 4:05​	DIAPURWF1	3.9​	1/02/2022 4:00​

 

[sijpie]

If I understand you correctly,

1. all the 105,120 unzipped files will be in one directory
2. the macro should request a generator name
3. then produce one output sheet with all the entries in each of the 105,120 files for the particular generator.

@1: I am not sure if it will be simple to extract zip files using vba...