Solved

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

Posted on 2012-04-02
5
366 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

772 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

13 Experts available now in Live!

Get 1:1 Help Now