Whats the best approach working with data, Arrays/Collections/other?

Hi All,

At this time, I'm looking for guidance rather than solving an issue with specific code.

I am developing using arrays, but am just checking if this is the best approach or whether I should use another (eg collections, temp tables etc). The limitations, basic info and thoughts as to how to approach this is as follows:

Limitations:
1. I must use Excel to convert a system report (which always has the same structure) into an csv file to upload into another system. I cannot used MS Access etc as the end users only have Excel.
2. I have no access to the source system and no control over the structure of the source report.
3. I have no control over the structure of the final csv output, as is used to create invoices etc in the destination system.

Basic Info:
1. The source report has a Site ID in column 1. On separate rows below but in other columns there are dates 8 totals (4 Quantities & 4 values), with a subtotal for the site in the last row. (this would all make 1 document)
2. There are 3 excel tables (report parameters, site parameters & VAT parameters) which the user maintains and determine what is required.
3. The CSV output has 12 columns, some of which are of mixed data types, but are the same by line/row type.
4. There are 4 different line/row types that can be created (Header, text, invoice, collections), which are created as follows:
Headers - 1 row for each Site ID
Text - 3 rows for each Site ID
Invoice - 1 to 4 rows based on the 8 totals (1 qty & 1 value make 1 item), where value <> 0.
Collections - 0 to 4 rows based on whether cash has been collected for the relevant invoice (identified from Site Parameters)
5. There are several other possible variations on what happens based on the parameters (eg 1 or multiple invoices per document, itemised or consolidated totals) etc, which makes this more complex but no need to go into detail as hopefully this is enough to understand the concept.

My thoughts to approach this are with arrays:
1. 3 to store the parameters
2. 1 to store headers (with a doc id to link across arrays)
3. 1 to store text lines (with a doc id to link across arrays)
4. 1 temp array to build site totals by the 4 itemised (1 = quantity & value) subtotals (not needed by week dates)
5. loop through temp array, do stuff as per parameters, then store invoices in 1 array & collections in another.
6. loop through headers, pull in related texts, invoices then collections (output must be in that order) into final array
7. Output final array to CSV (via application.transpose).

I'm relatively happy doing the above, though not an expect so my code won't be the most efficient and may be longer than needed (I'll end up using 1 x 1D array and 8 x multi-dimensional arrays). So I am looking to see if there are any suggestions for a better approach (rather than 'oh yeah that will work', as would like to find the best approach & learn to improve my knowledge). A simple pointer will suffice so I can then follow up and learn from there.

Thanks in advance

Tooley

Thanks dflak. Unfortunately, whilst using a relational database like Access would have been my preferred option, this option was refused by my employer as is against policy.

I've completed now. In summary, I populated a single string array with the data needed from the source data together with the relevant site parameters. I copied report parameters to global dimensions rather than another array, simply to make the code easier to read. From there, the code loops through the array, performs the necessary actions and calculations passing results to a second string array then transposes the array to a csv file.

Thanks again