Need help with a VBS script

joehalford01

I'm totally a beginner here, this is the most complicated script I've attempted beyond simple batch files. I'm going to post this on some vbs forums but I'm hoping someone here might be able to help too. I'm trying to read multiple text files grabbed by extension and write out a specific record from each one to a separate text file. I keep running into issues with line 23; it says there is no object specified and I can't see where I need to add the additional object. Any vbs gurus in the house?

'Grab any file with a .DAT extension
'Search file for record 100
'Output record 100 to another file

set fso = CreateObject("Scripting.FileSystemObject")

dim fso
dim infolder
dim outfolder
dim infile
dim outfile

set infolder = fso.GetFolder("c:\Scripts\test files")
Set outfolder = fso.GetFolder("c:\Scripts\ready files")
set outfile = fso.OpenTextFile("c:\Scripts\ready files\totals.txt")

for each file in fso.GetFolder(infolder).Files
  if (fso.GetExtensionName(file.name)) = "DAT" then
    basename = fso.GetBaseName(file.name)
    infile = fso.BuildPath(infolder, basename & ".DAT")
    'readin = fso.OpenTextFile(infile, 1, True)
    set stream = fso.OpenTextFile(infile, 1)
    Do Until infile.AtEndOfStream
  'line = infile.ReadLine
  'If Mid(line, 1, 3) = "100" Then _
    'outfile.WriteLine line
loop
end if
Next
stream.close
Wscript.quit

-Foxer-

I'm not sure how to help with VBS, I'm not the best at it (plus the code isn't displaying right on my phone), but have you thought about using powershell instead? For me powershell is much easier to use than VBS.

RobertKaucher

I'm not totally certain what the full objective is, but I would also do this in PowerShell. This will print the file name, the line number and the content of th line where the text is found to a file called total.txt. I assume you will understand it enough to change it to fit your needs... As you will notice the PoSh script is shorter and easier to read.

$inFolder = "C:\dell\input\"
$outFolder = "C:\path\out\"
$outFile = $outFolder+"totals.txt"
Get-ChildItem $inFolder | Where-Object{ $_ -match ".dat" } | Select-String "100" | Add-Content-Path $outFile

joehalford01

Wow, that is pretty simple. One of the reasons it is so complicated is because you can't use wildcards with the opentextfile object, so I'm trying to use buildpath to create a variable constant (I hope i'm making sense . I'll try out powershell. thanks!

RobertKaucher

What are you trying to accomplish with the wild card and the path? We are always willing to lend a hand here...

joehalford01

Essentially, I'm trying to create a script that pulls the trailer record out of a data file and places that record into another text file. When our client uploads their file to me, I want it to extract the trailer record and email it to them; showing sucessful upload and the amount's assigned. Since they can upload one, two, or more files randomly at any given time, I need it to simply poll the folder every day at specified time and process any new files. The email part with VBS was easy, and I had a simple script done in no time that did one file at a time, it's the multiple file thing that is messing me up. I want it to grab files based on the .DAT extension, so *.dat is what I was thinking. This is the batch scripting I'm thinking with; simple but limited. Hopefully I can find some time today to play with powershell and the code you dropped me, it looks promising. Keep the ideas coming if you have them, thanks!

Everyone

If you're going to do just a daily report via e-mail, simple addition to Robert's example can do that using the Send-MailMessage command.

Once you have it tested and working, just set the script to run as a scheduled task.

joehalford01

I can't believe how much more sense Powershell makes. I'm focusing my scripting skills on this unless I need VBS for something specific that I can't do in powershell. I'm already finished after a couple of hours and no prior powershell experience. Check out the final code; I think there are some things that could have been done a little better but it works and does exactly what I want with no issues. Thanks for the help, seriously!

# This script checks the client folder for new files and sends a verification email to the client and IT team
# with an attachment containing the trailer record from each new file sent.  The file is then copied to archive
# and moved to the ready folder for processing.
$inFolder = "directory"
 $outFolder = "directory"
 $outFile = $outFolder+"totals.txt"
 Get-ChildItem $inFolder | Where-Object{ $_ -match ".dat" } | select-string -pattern ^[1] | Add-Content $outFile
send-mailmessage -from "" -to "", "", "", "" -subject "Your file uploaded sucessfully!" -Attachment $outfile -smtpServer mailserver
 Remove-Item $outfile
  Copy-Item directory\*.DAT directory\Archive
  Move-Item directory\*.DAT directory

RobertKaucher

joehalford01 wrote: »

I can't believe how much more sense Powershell makes. I'm focusing my scripting skills on this unless I need VBS for something specific that I can't do in powershell. I'm already finished after a couple of hours and no prior powershell experience. Check out the final code; I think there are some things that could have been done a little better but it works and does exactly what I want with no issues. Thanks for the help, seriously!

# This script checks the client folder for new files and sends a verification email to the client and IT team
# with an attachment containing the trailer record from each new file sent.  The file is then copied to archive
# and moved to the ready folder for processing.
$inFolder = "directory"
 $outFolder = "directory"
 $outFile = $outFolder+"totals.txt"
 Get-ChildItem $inFolder | Where-Object{ $_ -match ".dat" } | select-string -pattern ^[1] | Add-Content $outFile
send-mailmessage -from "" -to "", "", "", "" -subject "Your file uploaded sucessfully!" -Attachment $outfile -smtpServer mailserver
 Remove-Item $outfile
  Copy-Item directory\*.DAT directory\Archive
  Move-Item directory\*.DAT directory

Another convert to my army of PowerShell Junkies!