VB Scripting issue with multiple text files

I have a script I use to parse large, tab-delimited text files and insert them into a MySQL table. The script works fine. Today I needed to add a few more lines to the script such that it would read every file in a folder instead of the single file itself. What I have is below and to my knowledge should work. It completes without error but appears to be skipping the entire read/write operation.

Dim cn, rs, str
Dim sf, fso, fp
Dim arry()
Dim strSQL, oldStr
Dim strFolder, FLD, Fil, FSF
'Folder location for logs goes here
strFolder = "\\cpsosrv03\ttime"
Set fso = WScript.CreateObject("Scripting.Filesystemobject")

set FLD = fso.GetFolder(strFolder)
'now loop through every file in that folder and import them to MySQL database trigger_time
for each FIL in FLD.Files
fp = Fil.name

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")
Set sf = fso.OpenTextFile(fp, 1, True, -1) ' opens text files in Unicode mode which is how the HMI software exports it's logs
'connection string to mysql host on local machine
conn = "Driver={MySQL ODBC 5.1 Driver};Server=; Database=trigger_time; User=root; Password=0p3rat0r; Port=4306;"

Do Until sf.AtEndOfStream
'reads each text line
temp = sf.ReadLine

'cleans all the quotation marks out of the line it's reading

if inStr(temp, "Log_Hours_") then ' only imports log lines and skips everything else
'break the tab delimited columns into an array
str = Split (temp, vbTab)
'here's the query to write the data to the table
strSQL = "INSERT INTO triggers (var_name, timestring, varvalue, validity, time_ms) VALUES('"& str(0) &"', '" & str(1) _
& "', " & str(2) & ", " & str(3) & ", " & str(4) & ")"

cn.Open conn
cn.Execute strSQL
end if

loop ' do the next line until we're out of lines
' keep going until all files are read

wscript.echo "End of program"

Open in new window

What am I missing here?
Who is Participating?

Improve company productivity with a Business Account.Sign Up

RobSampsonConnect With a Mentor Commented:
Also, after the loop ends here
Loop ' do the next line until we're out of lines


to close the file. Maybe that would help.

I think you need to replace this line:
fp = Fil.name

Open in new window

fp = fso.GetFileName(Fil)

Open in new window

JP_TechGroupAuthor Commented:
No, it produces the names without issue. Lines 21-41 fail to do any work.
It's like the addition of multiple files confuses the the routine on the opened file.
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

chaauConnect With a Mentor Commented:
OK, I see. You need to clear the variables you have used for the objects. Right before your line with "next" statement add the following lines:
Set cn = Nothing
Set rs = Nothing
Set sf = Nothing

Open in new window

RobSampsonConnect With a Mentor Commented:
Change this
fp = Fil.name

to this
fp = Fil.path

It looks like it was only trying to open the file by name, instead of the full path, so it may not find any files.

JP_TechGroupAuthor Commented:
Of Course! We are closer!
Now it runs every file except the first one in the folder. Weird!
Bill PrewCommented:
About the only thing I see is the change Rob mentioned...

Bill PrewCommented:
I don't see any reason it wouldn't process all the files in the folder.  Could the one file not being processed be empty?

If not, toss a few Wscript.Echo statements in the loop to see what file it's processing, and if it gets to all the logic you expect it to.

JP_TechGroupAuthor Commented:
The combination of the close file and adding the absolute path did the trick.
Thank you all for your help. Good night.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.