i would like a vba xls 2007 script to parse a text file into seperate worksheets. a simple user interface with the input file path and output file path.
Please write in vba excel 2007 (please comment code so I can learn too!) ??" outputting xls files (not xlsx but files that can be opened by xls 2003 also) some code to parse the sample input text files I have attached.
The data is from traffic light intersections. Each intersection with traffic lights have detectors in each lane that collect car count data, some intersection have more or less dectectors so this needs to be variable when written to the output xls. The data shows 5min counts and hour total counts for each dectector at that intersection (the 2001, 2017, 2019 are intersection names). I only need the hourly total written into the output file not the 5 min data.
Attached are 4 sample input data txt files (2001 traffic [url removed, login to view], 2017 traffic [url removed, login to view] etc etc). These need to be parsed into the output xls files similar to [url removed, login to view]( have a look at this for the layout format. each input file will be parsed into its own output file, ie 2001 traffic [url removed, login to view] to [url removed, login to view], 2017 traffic data to [url removed, login to view] etc etc.
[url removed, login to view] contains the kind of layout I would like (you should make a form similar to the mocked up one I created please). This xls file will be the master with all the code in that is used to process the input and output files from.
Scats [url removed, login to view] contains a sample piece of an input text file outlining in red the data that I want read out of the input files and parsed to the output file. The red boxes indicate the values I want. The rest of the red values of the Scat [url removed, login to view] match to those in the cells in [url removed, login to view] sample I have made.
Each input file will have a similar data format, they will all be for Tuesday, Wednesday and Thursday, No need to check for any other days of the week. Some intersections have more detectors than others so be aware of that. However many detectors there are I want them all written to the output file please. Ie if 2001 traffic data has 13 dectector write 13 rows to the xls, if 2047 has 6 detectors then write 6 rows to its output file.
The only times I want data for are 07:, 08:, 12:, 13:,15:,17:,18: oclock, hence the 7 worksheets in the [url removed, login to view] sample.
Sometimes the dectectors fail in collecting data, this results in a DA value in the input txt file ( look at 2047 traffic [url removed, login to view] approach 4, dectector 4 for example). If there are **any** “DA?? values in the 5mins data (ie the :05,10:,15:,20:,25:,30:,35:,40:,45:,50:,55:60: columns) data for 7,8,12,13,15,17 or 18 hours then please write “DA?? to that cell in the output file. Even if there is a value in the hourly total column. ( basically if there is “DA?? in any 5mins I cant use that whole hour). This is the only special case.