Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

VB Scripting issue with multiple text files

Posted on 2013-11-14
9
Medium Priority
?
311 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 25

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
 
LVL 1

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 25

Assisted Solution

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

0
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

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

Rob.
0
 
LVL 1

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 59

Expert Comment

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

~bp
0
 
LVL 59

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 1332 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
 
LVL 1

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

I met Paul Devereux (@pdevereux) today when I responded to his tweet asking “Anybody know how to automate adding files from disk to a folder in #outlook  ?”.  I replied back and told Paul that using automation, in this case scripting, to add files t…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
Screencast - Getting to Know the Pipeline
Kernel Data Recovery is a renowned Data Recovery solution provider which offers wide range of softwares for both enterprise and home users with its cost-effective solutions. Let's have a quick overview of the journey and data recovery tools range he…

571 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