Solved

VB Scripting issue with multiple text files

Posted on 2013-11-14
9
303 Views
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=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?
0
Comment
Question by:JP_TechGroup
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 24

Expert Comment

by:chaau
ID: 39649553
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
 

Author Comment

by:JP_TechGroup
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.
0
 
LVL 24

Assisted Solution

by:chaau
chaau earned 167 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

0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 333 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.

Rob.
0
 

Author Comment

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

Expert Comment

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

~bp
0
 
LVL 54

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.

~bp
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 333 total points
ID: 39649941
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
 

Author Closing Comment

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

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

Suggested Solutions

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/A_266-VBScri…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

821 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