Solved

ActiveX Script - Locate a part of the File and Rename the file based on that

Posted on 2012-04-02
5
399 Views
Last Modified: 2012-04-09
I am looking to parse through a file with certain information.  I am looking for ST*(this information).

Example.  ST*441*1223123

I am looking for the 441.  The files are text that I am searching through and I want to replace the file name with 441 and the current date.  I need to go through all the files and I am going to set up folders to handle each type of ST* I will be receiving.  The ST is usually the 3 line down in the file, but not always.

Thanks.
0
Comment
Question by:GPCDIADMIN
  • 2
  • 2
5 Comments
 

Author Comment

by:GPCDIADMIN
ID: 37798045
I have done some searching and I am still having the same issue.  I need to be able to pick up a pattern within the text file and rename it so I can import into the database via a data source within my DTS.  


'  Visual Basic ActiveX Script
'************************************************************************
Function Main()        
saveTo = "\\server\Ready to be processed\"
writeTo = "" 
headingPattern = "ST*441"
     
dim fso,fileFrom,regex, sFileName      
'flat file datasource'      
sFilename =  "\\server\Ready to be processed\Test.txt"
set fso = CreateObject("Scripting.FileSystemObject")      
set fileFrom = fso.OpenTextFile(sFilename)
set regex = new RegExp
     
     with regex
       .Pattern = headingPattern
       .IgnoreCase = True
       .Global = True
     end with
     
     while fileFrom.AtEndOfStream <> true
       line = fileFrom.ReadLine
       set matches = regex.Execute(line)

       
       if matches.Count > 0 then
         writeTo = saveTo & matches(0).SubMatches(0) & ".txt"
         set fileTo = fso.CreateTextFile(writeTo)
       else
          fileTo =fileto.WriteLine(line)
       end if
     wend
     
     set fileFrom = nothing
     set fileTo = nothing
     set fso = nothing
     set regex = nothing

Main = DTSTaskExecResult_Success

End Function


----- Error... Object Required fileto.  I have used the above to handle splitting text files based off a pattern.  What am I doing wrong here?  Any better way of handling this.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 37804350
First of all, since you have posted VBScript, I am assuming that you are familiar with it and can use it.

You are going about it the wrong way:  You do not want to re-write the whole file, that is exactly the problem that you are hitting (you are trying to write to fileto when the filename is not known).  All you have to do is search the file for the text, when you find it, skip out and rename the file or if you cannot do that then just copy the file with the new name.  It does not get easier than that.
0
 

Author Comment

by:GPCDIADMIN
ID: 37804388
Can you direct me to some useful script to search for the text.  I was attempting to do just that, but my strength isn't in VBScript.

Thanks....
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 250 total points
ID: 37804512
I have modified your VbScript code to just return the file name, but I have not tested it:

'  Visual Basic ActiveX Script
'************************************************************************
Function Main()        
Const saveTo = "\\server\Ready to be processed\", _
	headingPattern = "ST*441"

Dim fso,fileFrom, regex, sFileName, writeTo, Found
'flat file datasource'      
sFilename =  "\\server\Ready to be processed\Test.txt" 
Set fso = CreateObject("Scripting.FileSystemObject")      
Set fileFrom = fso.OpenTextFile(sFilename) 

Set regex = new RegExp 
With regex 
	.Pattern = headingPattern 
	.IgnoreCase = True
	.Global = True 
End with 

Found = False
While Not found And Not fileFrom.AtEndOfStream
	line = fileFrom.ReadLine 
	Set matches = regex.Execute(line) 

	If matches.Count > 0 then 
		writeTo = saveTo & matches(0).SubMatches(0) & ".txt" 
		Found = True
	End If 
Wend 

MsgBox writeTo

set fileFrom = nothing 
set fileTo = nothing

set fso = nothing 
set regex = nothing 

Main = DTSTaskExecResult_Success

End Function

Open in new window


It should display the new file name in a message box.
0
 
LVL 32

Accepted Solution

by:
Robberbaron (robr) earned 250 total points
ID: 37805465
see if this gets you any further.  I have created 2 different files with codes embeded. Note i match any three digit string ST*nnn*

running from command prompt displays the input file names, with output.  I have commented out the actual rename at this point.
C:\ee>cscript Q_27658731.vbs
Microsoft (R) Windows Script Host Version 5.7
Copyright (C) Microsoft Corporation. All rights reserved.

FILE-in Log.txt Text Document
FILE-out c:\ee\test2\442-20120404
FILE-in qweqdas.txt Text Document
FILE-out c:\ee\test2\323-20120404



dim objRegExp , inputPath , outputpath, fso, fsFolder, fsFile
dim code

set objRegExp = New RegExp
With objRegExp
  .IgnoreCase = True
  .Pattern = "ST\*([0-9]{3})\*"
End With

InputPath = "c:\ee\test2"
OutputPath = "c:\ee\test2"

' OpenTextFile Method needs a Const value
Const ForAppending = 8, ForReading = 1, ForWriting = 2

'get searchfolder
'for each file in folder
' find the ST\*([0-9]{3})\* pattern
' if found, build new filename
' rename file
'next file

   Set fso = CreateObject("Scripting.FileSystemObject")
   Set fsFolder = fso.GetFolder(inputpath)

     For Each fsFile In fsFolder.Files
       wscript.echo "FILE-in",fsFile.Name, fsFile.Type
       'code = F1(fsFile)
       code = FindCode(fsFile)
       outputname = outputpath & "\" & code & "-" & ShortDate & ".txt"
       wscript.echo "FILE-out",outputname
       'fsFile.Move (outputname)  'rename the input file
     next

Wscript.Quit


Function FindCode(flx)
  'flx is a File object
  Set tsFile = flx.OpenAsTextStream(ForReading)
  Found = False
  Do While Not found And Not tsFile.AtEndOfStream
    sLine = tsFile.ReadLine 
    Set matches = objRegExp.Execute(sLine)
    
    If matches.Count > 0 then 
      set ma = matches(0)
      FindCode = ma.SubMatches(0)
      Found = True
      Exit Do
    End If 
  Loop
  tsFile.Close
  
  if Not Found then
    FindCode = ""
  end if
  
End Function

Function Shortdate
  Dim strNow, strDD, strMM, strYYYY, strFulldate
  strYYYY = DatePart("yyyy",Now())
  strMM = Right("0" & DatePart("m",Now()),2)
  strDD = Right("0" & DatePart("d",Now()),2)
  fulldate = strYYYY & strMM & strDD
  ShortDate = fulldate
End Function

Open in new window

0

Featured Post

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.

Question has a verified solution.

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

Suggested Solutions

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …

839 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