Load columns in a condition

[luckystar]

Hi everyone, I want to load some columns which contain "This Year" from an excel file. I have about 100 columns in this excel sheet. Not all the columns have "This Year", but I don't want to manually select them, is there any way to do it automatically? Many thanks. 

Xin

LOAD
[Score (This Year):B1],
[YOY Change:B1],
[Sector Gap:B1],
[Score (This Year):B1.1],
[YOY Change:B1.1],
[Sector Gap:B1.1],
[Score (This Year):B1.2 ],
[YOY Change:B1.2],
[Sector Gap:B1.2 ],
[Score (This Year)],
.......

FROM
[C:\Data.xlsx]
(ooxml, embedded labels);

[stevejoyce · Accepted solution]

I had to do something similar before, this was what I did:

Load all columns with load * (limit load as only concerned about column header).  Then loop through each field in the table and evaluate if it meets your condition (contains "This Year").  If it does store the field name into a variable.  Then use that variable in your load script.

Like this:

let vTableName = 'tempData';

let vListOfFieldsToLoad = ;

$(vTableName):
First 1 LOAD
*
FROM [lib://AttachedFiles/Book3.xlsx]
(ooxml, embedded labels, table is Sheet1);

for field = 0 to NoOfFields('$(vTableName)')-1

let vFieldName = '[' & FieldName($(field),'$(vTableName)') & ']';
Trace Evaluating field $(vFieldName);

if wildmatch(upper('$(vFieldName)'), '*THIS YEAR*') then

Trace Found match! field: $(vFieldName);

//handle first value vs appending

if len('$(vListOfFieldsToLoad)') = 0 then

let vListOfFieldsToLoad = '$(vFieldName)';

else

let vListOfFieldsToLoad = '$(vListOfFieldsToLoad)' & ',' & '$(vFieldName)';

End if

End If

next field


Trace vListOfFieldsToLoad: $(vListOfFieldsToLoad);



drop table $(vTableName);


Data:
LOAD
$(vListOfFieldsToLoad)
FROM [lib://AttachedFiles/Book3.xlsx]
(ooxml, embedded labels, table is Sheet1);

[luckystar]

Thank you so much. Your codes worked well. 

Just wondering what does "Trace" do in this code? 

[stevejoyce]

Trace just writes to the script log which can be seen when reloading the app.

Please mark post as solution.