VBScript using two For Next in the same code

Question

I need to insert six txt files into MySQL tables. I want to insert the file txt10.txt into table D1, the file txt20.txt into table D2, the file txt30.txt into table D3, etc. But all text files are inserted into all MySQL tables.

My code below:

Arr1 = Array("txt10", "txt20", "txt30", "txt40", "txt50", "txt60") 
Arr2 = Array("D1", "D2", "D3", "D4", "D5", "D6")    

For K = 0 To UBound(Arr1)
    For I = 0 To UBound(Arr2)    
        SQL = " FLUSH TABLE `tbl_" & Arr2(I) & "_" & Year(Date()) & "`; "
        cn.Execute(SQL)   

        SQL = " LOAD DATA LOCAL INFILE 'D:\\nOpen\\" & Arr1(K) & ".txt' "
        SQL = SQL & " INTO TABLE `tbl_" & Arr2(I) & "_" & Year(Date()) & "` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
        cn.Execute(SQL) 
    Next
Next

EDIT #1

In your code I have error:

Array index out of range

On this line:

tbl = Arr2(n)

New code:

Arr1 = Array("txt10", "txt20", "txt30", "txt40", "txt50", "txt60") 
Arr2 = Array("D1", "D2", "D3", "D4", "D5", "D6")    

For n=0 To UBound(Arr1)
    txt = Arr1(n)
    tbl = Arr2(n)  

    SQL = " FLUSH TABLE `tbl_" & tbl & "_" & Year(Date()) & "`; "
    cn.Execute(SQL)   

    SQL = " LOAD DATA LOCAL INFILE 'D:\\nOpen\\" & txt & ".txt' "
    SQL = SQL & " INTO TABLE `tbl_" & tbl & "_" & Year(Date()) & "` FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' IGNORE 1 LINES;"
    cn.Execute(SQL) 
Next

If you get an "index out of range" error you don't have the same number of elements in both arrays. The sample code does not raise this error. — Ansgar Wiechers

Ansgar Wiechers Ansgar Wiechers · Accepted Answer · 2019-01-11T10:12:19

Well, yes. Running two For loops the way you do will give you all possible combinations of the elements of your two input arrays.

What you actually want is use a single loop and pick the corresponding elements of each array:

For n=0 To UBound(Arr1)
    txt = Arr1(n)
    tbl = Arr2(n)
    'now insert txt into tbl
Next

Alternatively you could build a mapping of files to tables (or vice versa), so that you don't have to rely on both arrays being the same size:

Set map = CreateObject("Scripting.Dictionary")
map.Add "txt10", "D1"
map.Add "txt20", "D2"
...

For Each txt In map.Keys
    tbl = map(txt)
    'now insert txt into tbl
Next

Regardless of which way you choose, DO NOT build your SQL queries by string concatenation. It will open your code to SQL injection. Use prepared statements instead.

VBScript using two For Next in the same code

1 Answers