Creating a smart loop for reading Excel sheets with (almost) the same code

[cdss-developer]

Hi everyone,

I need your help.

I'm trying to read various xls files. They all have the same layout. I 'succeeded' to read one file, see attachment.

Problem / questions:

# 1. I would like to tilt the table so that every employee gets a separate row with values

# 2. Including showing the name of the employee, in stead of only Employee, Employee, etcetera.

# 3. I want to avoid having to copy more than 500 lines of code for each file. How can I read multiple file from the same folder? (for example by applying some kind of loop).

# 4. I suspect that this can be done smarter, by repeating each block (again by means of a kind of loop), whereby only the RecNo's have to be changed (since the rest remains more or less the same).

I would appreciate if you can edit and upload an example, working in the same (see example as attached) qvw.

Kind regards,

Eelco

[marcus_sommer]

You could within another load add/replace the generic employee-fields with an ID or a Name with the same mapping-approach like used for matching Part and Type whereby I assume that your different files contain different employees so you will need to combine Employee and the source to match them correctly. For this you could add filebasename() as Source within the first load (by adjusting the crosstable column-offset from 9 to 10 because there would be now one more field).

- Marcus