Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I need to insert a script that loads multiple files.
What I have now is:
LOAD
"Submit date",
"Lead ID",
'Alex' & "Lead ID" as "ID"
FROM [lib://Sales Funnel/Output\Alex Gerdts\Output_Alex.xlsx]
(ooxml, embedded labels, table is Deliverables);
LOAD
"Submit date",
"Lead ID",
'Fred' & "Lead ID" as "ID"
FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output_Fred.xlsx]
(ooxml, embedded labels, table is Deliverables);
I would like to have 2 arrays:
1st with the file names: Output_Alex.xlsx, Output_Fred.xlsx
2nd with the values to load: 'Alex','Fred'
Then I would like to change the above code into something similar to this:
for i=1 to Array1Lenght
LOAD
"Submit date",
"Lead ID",
Array2 & "Lead ID" as "ID"
FROM Array1
(ooxml, embedded labels, table is Deliverables);
next i
Any ideas on how to do this?
Thank you.
You can learn more about "For Each Next" in the help here:
I understand what you want to do, and two FOR statements won't do it. The second is nested in the first, which means you will run the nickname twice for each name, for a total of 4 iterations:
Alex, A
Alex, F
Fred, A
Fred, F
What you want is to run the loop only twice but have both the name and nickname available. You can combine the two as a single string and then separate using the SubField() function.
FOR EACH combinedName IN 'Alex-A', 'Fred-F'
LET name = SubField('$(combinedName)', '-', 1);
LET nickname = SubField('$(combinedName)', '-', 2);
LOAD
"Submit date",
"Lead ID",
'$(nickname)' & "Lead ID" as "ID"
FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output$(name).xlsx]
(ooxml, embedded labels, table is Deliverables);
NEXT name
-Rob
FOR EACH name IN 'Alex', 'Fred'
LOAD
"Submit date",
"Lead ID",
'$(name)' & "Lead ID" as "ID"
FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output_$(name).xlsx]
(ooxml, embedded labels, table is Deliverables);
NEXT name
-Rob
Hi Rob,
That worked nice!
How do I do in order to have 2 different arrays, such as:
FOR EACH name IN 'Alex', 'Fred'
FOR EACH nickname IN 'A','F'
LOAD
"Submit date",
"Lead ID",
'$(nickname)' & "Lead ID" as "ID"
FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output_$(name).xlsx]
(ooxml, embedded labels, table is Deliverables);
NEXT name
NEXT nickname
You can learn more about "For Each Next" in the help here:
I understand what you want to do, and two FOR statements won't do it. The second is nested in the first, which means you will run the nickname twice for each name, for a total of 4 iterations:
Alex, A
Alex, F
Fred, A
Fred, F
What you want is to run the loop only twice but have both the name and nickname available. You can combine the two as a single string and then separate using the SubField() function.
FOR EACH combinedName IN 'Alex-A', 'Fred-F'
LET name = SubField('$(combinedName)', '-', 1);
LET nickname = SubField('$(combinedName)', '-', 2);
LOAD
"Submit date",
"Lead ID",
'$(nickname)' & "Lead ID" as "ID"
FROM [lib://Sales Funnel/Output\Alexandre Cardoso\Output$(name).xlsx]
(ooxml, embedded labels, table is Deliverables);
NEXT name
-Rob