Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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);
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);
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);
Thank you so much. Your codes worked well.
Just wondering what does "Trace" do in this code?
Trace just writes to the script log which can be seen when reloading the app.
Please mark post as solution.