Solved

VB Scripting issue with multiple text files

Posted on 2013-11-14
9
300 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

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 51

Expert Comment

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

~bp
0
 
LVL 51

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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Welcome to part one of a multi-part tutorial series, VBScript for Windows System Administrators.  The goal of this series is to teach non-programmers how to write useful VBS code to automate their environment, and perform tasks faster, and in a more…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

757 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now