Reading a List from Excel and Setting as a Variable Value

[hannahnoelle797]

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?

[KGalloway]

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

ab

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.

[hannahnoelle797 · Accepted solution]

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.