VB Scripting issue with multiple text files

Posted on 2013-11-14
Medium Priority
Last Modified: 2013-11-14
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?
Question by:JP_TechGroup
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
LVL 25

Expert Comment

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

Open in new window

fp = fso.GetFileName(Fil)

Open in new window


Author Comment

ID: 39649848
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.
LVL 25

Assisted Solution

chaau earned 668 total points
ID: 39649863
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 65

Assisted Solution

RobSampson earned 1332 total points
ID: 39649910
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.


Author Comment

ID: 39649924
Of Course! We are closer!
Now it runs every file except the first one in the folder. Weird!
LVL 57

Expert Comment

by:Bill Prew
ID: 39649925
About the only thing I see is the change Rob mentioned...

LVL 57

Expert Comment

by:Bill Prew
ID: 39649934
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.

LVL 65

Accepted Solution

RobSampson earned 1332 total points
ID: 39649941
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.


Author Closing Comment

ID: 39650241
The combination of the close file and adding the absolute path did the trick.
Thank you all for your help. Good night.

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month8 days, 1 hour left to enroll

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question