Solved

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

Posted on 2012-04-02
5
436 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

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!

Question has a verified solution.

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

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…
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

623 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