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=127.0.0.1; Database=trigger_time; User=root; Password=0p3rat0r; Port=4306;"

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

oldStr=chr(34)
'cleans all the quotation marks out of the line it's reading
temp=Replace(temp,oldStr,"")

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
cn.close
end if

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

wscript.echo "End of program"

Open in new window


What am I missing here?
LVL 1
JP_TechGroupAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

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

add
sf.Close

to close the file. Maybe that would help.

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

Open in new window

with
fp = fso.GetFileName(Fil)

Open in new window

0
 
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.
0
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

0
 
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.

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

~bp
0
 
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.

~bp
0
 
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.
0
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.