Solved

VB Scripting issue with multiple text files

Posted on 2013-11-14
9
302 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
DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

 
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 53

Expert Comment

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

~bp
0
 
LVL 53

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Script to copy or move mouse-selected collection of files plus targets referenced by shortcuts (.lnk) The purpose of this article is to help illuminate the real challenges and options available (where they may exist) for utilizing simple scriptin…
In this article we want to have a look at the directory attributes which are used by Microsoft to store the so called Security Identifiers (SID). These SIDs plays an important role in delegating and granting permissions and in authentication of trus…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

831 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