Need Help with Report Generation Toolkit (Excel)

[Bob_Schor]

I think I might see what you are trying to do (but maybe not ...).  See if this is close.

You have (a lot of) .csv files, each of which contains a lot of extraneous information, but can be parsed to extract a few data points (maybe only a single point per file, with the rest of the file's contents discarded).  You need to parse the entire folder's worth of files in order to accumulate the series of points that you now want to organize into a 2-D Table and output as an Excel WorkSheet.

Is this close?  If so, where are you having conceptual difficulties?  It is pretty simple to write a function that will look at a folder, get a list of all of the files in the folder, then open the files one-at-a-time.  Once a file is open, it should be pretty simple to read lines, look for patterns that help you identify the data elements you want, and create "data points".  Depending on how your data are distributed among the files, you can construct the final Table in either one or two passes.

If you don't know in advance the rows and columns of your final table, a two-pass algorithm might be simpler.  On the first pass, read your folder-of-files and build an intermediate file that has the (unsorted, as-you-encounter-them) set of points that you will eventually organize into your table.  Once you've finished getting all of the data into the intermediate file, close and reopen it, parse it to figure out how many unique "rows" and "columns" you have, then build your 2-D Table.  For example, suppose your files were Mouse data, consisting of Mouse ID, Age, and Weight.  After the first pass, you have a file that looks like this:  M32, 23, 43g, M32, 45, 56g, M24, 23, 39g, M56, 45, 61g.  During Pass 2, you see there are three mice (M32, M24, and M56) with weight data at two time points (23 and 45, days, I'd presume).  Your resulting 2-D table would look like this:

             23           45

M32      43g         56g

M24      39g

M56                     61g

"Missing data" show up as blank cell entries in the table.

Is this what you are trying to do?

BS

[mark.eckhoff]

Bob - 

Yes, Thank you for the help. The first part of what you guessed was spot on. (Already finished with that portion). It was the figuring the algorithm for the second part that was getting me messed up. This is where my unfamiliarity in some of the array and other functions that are slowing me down. But yes by what you described, the concept is exactly what is needed to fill my requirements.