Loop within Load script

[Sharbel]

Hello,

I am trying to load salary data from excel files on monthly basis. Each excel file has data regarding  specific month as seen in sample tables below :

May 2023:

June 2023:

Since the number of columns vary from month to month , I used loop within the Load script as follows:

Table_temp:

LOAD

*

FROM [may2023.xlsx];

Table:

Let NumberOfColumns = NoOfFields('Table_temp');

For i = 7 to NumberOfColumns

Let FieldName = FieldName(i,'Table_temp');

Load

emp_no,

Depatment,

Salary_Date,

$(FieldName)  

Resident Table_temp;

Next;

drop Table Table_temp;

The above script is supposed to  load the columns emp_no, Department,Salary_Date and all other columns starting from column number 7 to the number of columns in each table (that vary from month to month).

When i ran the above script (for example in month of May 2023), what i actually got was several tables (4 tables) , each table has the same first 3 columns (i.e emp_no, Department,Salary_Date) and another column from the table of May2023, and that's not the desired outcome that i wanted in the first place.

The desired outcome for May2033 would be a single table consists of the following columns: 

Any ideas on how to fix the above script ?

Many thanks,

Sharbel

[marcus_sommer]

Only tables with the identically data-structure would be auto-concatenated into a single table. To enforce different structures form multiple loads into a single table you may use the load-prefix NoConcatenate.

Personally I wouldn't do it in your case else transforming the crosstable-structures into normal records, maybe with a logic like:

for each file in filelist()
   t: crosstable(Type, Value, 6) load * from Source;
next

[Sharbel]

Apologies for not  explaining my self enough, the issue here is not how to concatenate  two tables since each subsequent table (excel file) contains all the data of the previous tables .

The purpose of the script is to load specific columns (emp_no, Department, Salary Date) and all other columns starting from column number 7 to column number N.

[marcus_sommer · Accepted solution]

I suggest just to drop the unwanted 3 fields after load-loop because it's the most simple approach and avoids any complexity of accessing the files in beforehand of the real data-load to get the information which fields are there.

Further I would assume that loading all fields and removing the unwanted ones afterwards isn't less performant against the extra efforts to get the field-information in beforehand. Also I suggest to transform the crosstable because it simplifies the handling significantly.