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

[quddus_mohiuddi]

Hi Eelco,

Is it compulsory that we can not maintain excel data first ?

[cdss-developer]

Hi Quddus,

It's not entirely clear to me what you mean. Do you mean storing the data in the memory?

It will be ± 30 of these files that I have to read, so I think it is not that bad in terms of memory.

[quddus_mohiuddi]

Hi Eelco,

I meant that can't we do some alteration in excel file before loading it?

[cdss-developer]

I would preferably change as little as possible to the file. First I want to see if it is possible to write a script here. If it does not work, then there is nothing else, then I will indeed have to adjust the source files.

[marcus_sommer · Accepted solution]

I think it's a quite easy task and could be handled with a single-load statement for all data-parts and all files by using something like this:

set vPath = '.\*.xls';

set vCompany = 'Test-Company';

set vCountry = 'UK';

set vCurrency = 'Euro';

set vPeriod = 'JAN-JUN 2018';

MapRecNo:

mapping load * inline [

RecNo, Part

6, Restaurants

7, Restaurants

8, Restaurants

...

];

[Data]:

crosstable(Employee, Value, 9) LOAD

RecNo(),

RoWNo(),

'$(vCompany)' as Company,

'$(vCountry)' as Country,

'$(vCurrency)' as Currency,

'$(vPeriod)' as Period,

//'CREDITCARD' as Type,

//'Restaurants' as Part,

pick(match(recno(), 6,7,8, ...), 'Restaurants', 'Restaurants', 'Restaurants', ....) as Part,

applymap('MapRecNo', recno(), '#NV') as Part2,

@1 as Detail,

     @4 AS Employee1,

     @5 AS Employee2,

     @6 AS Employee3,

     @7 AS Employee4,

     @8 AS Employee5,

     @9 AS Employee6,

     @10 AS Employee7,

     @11 AS Employee8,

     @12 AS Employee9,

     @13 AS Employee10

FROM $(vPath) (biff, no labels, table is Total$)

WHERE

// RecNo()>5 and RecNo()<9 and

len(@1)>0;

This means using a wildcard * within the FilePath to load all files from the folder and matching the different data-parts to Part and Type categorizing with a pick(match()) or probably even better with a mapping-approach instead of repeating the load multiple times and controlling them per where-clause. The transformation from the Employee columns into rows is easily made with a crosstable-prefix.

- Marcus

[cdss-developer]

Hi Marcus,

Thanks for your response.

I get some errors when copying and running the code.

Can you upload a working example (qvw) for me?

[marcus_sommer]

Which errors?

[cdss-developer]

Sorry, my mistake, The code is working now ...

Thank you very much.

I will immediately check if this is going to work for me / solves my problem.

[cdss-developer]

Marcus, this works perfectly thanks.

Can you also show me how to display the employee names instead of 'Employee1', 'Employee2', etc.?