topic Reading a List from Excel and Setting as a Variable Value in App Development

Reading a List from Excel and Setting as a Variable Value

hannahnoelle797 — Fri, 06 Oct 2023 18:43:50 GMT

I am trying to read a list of values from an excel sheet where the name of the variable is in column A and the value of the variable is column B

Name	Value
ListA	['abc','def','ghi','jkl']
ListB	['mno','pqr','stu']

I am loading the data in from the file and then setting up my variables as such

LET vNROWS = NoOfRows('Variable'); FOR i=0 to (vNROWS-1) LET vVariable_Name = Peek('Variable_Name', i, 'Variable'); [$(vVariable_Name)] = Peek('Variable_Value', i, 'Variable'); next

So when it finishes looping through I expect to have two list variables: $(ListA) and $(ListB) with the values

$(ListA) = ['abc','def','ghi','jkl']

$(ListB) = ['mno','pqr','stu']

I then want to use the value in an if statement of another data load

LOAD Letters, if(match(Letters, $(ListA)), 'List A', if(match(Letter, $(ListB)), 'List B', 'No List')) as List_Title FROM other_file.QVD (qvd);

However, I always get an error on one of the commas or apostrophes, etc.

Is there a way to read a string that is a list and convert it to a list value so that it can be used later on in my code?

Re: Reading a List from Excel and Setting as a Variable Value

KGalloway — Fri, 06 Oct 2023 19:04:42 GMT

This seemed to work for me:

Variable:

LOAD

Name as Variable_Name,

Value2 as Variable_Value

FROM [lib://MyFolder/ScriptTesting.xlsx]

(ooxml, embedded labels, table is Sheet1);

LET vNROWS = NoOfRows('Variable');

FOR i=0 to (vNROWS-1)

LET vVariable_Name = Peek('Variable_Name', i, 'Variable');

v$(vVariable_Name) = Peek('Variable_Value', i, 'Variable');

next i;

table:

load

Letters,

if(wildmatch('$(vListA)', '*|'&Letters&'|*'), 'List A',

if(wildmatch('$(vListB)', '*|'&Letters&'|*'), 'List B', 'No List')) as List_Title

inline [

Letters

abc

def

ghi

kjl

jkl

mno

pqr

stu

abd

stuu

]

;

Notably, I changed the list variable to use Value2 instead of Value. Value 2 used the pipe character, |, as a separator and a leading and ending character so that wildmatch only finds exact strings.

Re: Reading a List from Excel and Setting as a Variable Value

hannahnoelle797 — Fri, 06 Oct 2023 19:22:18 GMT

Update: What I have above works just fine. Another piece of my load statement wasn't correct which was causing it to appear to not work.