• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

Deleting Files????????

I am not sure deleting these files is what I need to do but it will also accomplish the task... If you can think of another way to do this then that would help too... Ok here is what I am trying to do...

the fname selects all the files in the directory that the first 11 characters are so and so and then it updates my database (I did not include the updating of the database part) well in those files there are some files that I do NOT want to update the database with... those files have the last 5 characters of "inj-r" , "ros-r" , "log-r".... SO I need to build an IF statement that deletes the files before it goes through them to update it or to find away to disregard them in the process. Can someone give me an idea of how to do this or point me in the right direction with some examples so I can figure this out. I am very new at this whole vb script thing.


========================
CODE
========================
Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles\")
For Each file in objFolder.Files
Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)
 fname = left(file.name,11)
  if fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then
      Do While Not objTextStream.AtEndOfStream
            a1=objTextStream.ReadLine
            j=instr(trim(a1),"TEXT.dtd")
            x=instr(trim(a1),".dtd")
          if j > 0 then
                  y=cint(x-j)
                  i=Mid(trim(a1),j,y)
                  objXMLDOM.async = False
                  If Not objXMLDOM.Load("C:\SportsTicker\xmlfiles\" & file.name) Then
                      exit do
                  End If
0
jsuttor
Asked:
jsuttor
  • 10
  • 8
  • 2
1 Solution
 
Carl TawnSystems and Integration DeveloperCommented:
You could change your loop to something like:

For Each file in objFolder.Files
    fname = Mid(file.Name, InStrRev(file.Name, ".") - 5, 5)
    If fname <> "inj-r" And fname <> "ros-r" And fname <> "log-r" Then
        Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)
       
        '// Code to open and process file

    End If
Next


This would then exclude any file with the sets of 5 digits (excluding the file extension) you mentioned.
0
 
jsuttorAuthor Commented:
Yeah though my problem with that is I need to ONLY capture the other files (fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS") and exclude the ones with inj-r , ros-r , log-r. If I did it without the fname and only those files I would get 4000 database updates per day that would be for something completely different then I need it for.

J
0
 
Carl TawnSystems and Integration DeveloperCommented:
I'm not quite sure what it is you are after.

You said you wanted to process all the files in the folder that don't have "inj-r", "ros-r" or "log-r" at the end, which is what the code in my last post will do. If this is NOT correct then could you explain in a little more detail ?

Just to break it down, this is what the code in my post does:

1) Loops through each file in the specified folder
2) Checks the last 5 digits in the file name
3) If the last 5 digits are "inj-r", "ros-r" or "log-r" then the file is skipped
4) Otherwise the file is opened and processed
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!

 
EDDYKTCommented:
then use


Dim wshShell, objEnvironment
Set wshShell = CreateObject("WScript.Shell")
Set objEnvironment = wshShell.Environment("System")
wshShell.Run objEnvironment.Item("COMSPEC") & " /c del /S /F /Q C:\SportsTicker\xmlfiles\*inj-r",, true
wshShell.Run objEnvironment.Item("COMSPEC") & " /c del /S /F /Q C:\SportsTicker\xmlfiles\*ros-r",, true
wshShell.Run objEnvironment.Item("COMSPEC") & " /c del /S /F /Q C:\SportsTicker\xmlfiles\*log-r",, true


Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles\")
For Each file in objFolder.Files
Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)
 fname = left(file.name,11)
  if fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then
     Do While Not objTextStream.AtEndOfStream
          a1=objTextStream.ReadLine
          j=instr(trim(a1),"TEXT.dtd")
          x=instr(trim(a1),".dtd")
         if j > 0 then
               y=cint(x-j)
               i=Mid(trim(a1),j,y)
               objXMLDOM.async = False
               If Not objXMLDOM.Load("C:\SportsTicker\xmlfiles\" & file.name) Then
                   exit do
               End If



or

Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles\")
For Each file in objFolder.Files

 fname = left(file.name,11)
fnamelog = right(file.name,5)
 if fnamelog = "inj-r" or fnamelog = "log-r" or fnamelog = "ros-r" then

set demofile = filesys.GetFile(objFolder & "\" & file.Name)
demofile.Delete true


  else if fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then
Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)
     Do While Not objTextStream.AtEndOfStream
          a1=objTextStream.ReadLine
          j=instr(trim(a1),"TEXT.dtd")
          x=instr(trim(a1),".dtd")
         if j > 0 then
               y=cint(x-j)
               i=Mid(trim(a1),j,y)
               objXMLDOM.async = False
               If Not objXMLDOM.Load("C:\SportsTicker\xmlfiles\" & file.name) Then
                   exit do
               End If


0
 
jsuttorAuthor Commented:
Ok then let me better explain... I get 25,000 xml files per day.... In the original code is sorts out all the FNAME files. Well that is not good enough.. I need to sort the files from the FNAME files and not include the ones that have inj-r ,ros-r and log-r.... If I were to just sort through all the files and just not use the one with inj-r, ros-r and log-r I would update the database with about 4000 things a day that have nothing to do with what the database is related too. Hope that helps.
0
 
jsuttorAuthor Commented:
EDDYKT I would like to try to use the second method so I don't have popup windows every 30 seconds on the screen. SO here is my code... I get an expected end of statement error at line 23 which is

 else fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then

Can you tell me what I did wrong?

J


==============
CODE
==============
Dim objNodes
Dim objBookNode
Dim objADORS
Dim objADOCnn
dim XTIME , flagIN,sc
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles")
Set objXMLDOM = CreateObject("Msxml2.DOMDocument.3.0")
Set objADORS1 =  CreateObject("ADODB.Recordset")
Set objADOCnn = CreateObject("ADODB.Connection")
objADOCnn.Open "PROVIDER=SQLOLEDB;SERVER=rotodata1.rotoamerica.com;UID=jsuttor_testuser;PWD=test123;DATABASE=jsuttor_XML_FEED_TABLES;"
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''      
''''              Import to Articles Database / TEXT.DTD
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''            
Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles\")
For Each file in objFolder.Files
Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)
fname = left(file.name,11)
fnamelog = right(file.name,5)
if fnamelog = "inj-r" or fnamelog = "log-r" or fnamelog = "ros-r" then
set demofile = filesys.GetFile(objFolder & "\" & file.Name)
demofile.Delete true
  else fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then
      Do While Not objTextStream.AtEndOfStream
            a1=objTextStream.ReadLine
            j=instr(trim(a1),"TEXT.dtd")
            x=instr(trim(a1),".dtd")
          if j > 0 then
                  y=cint(x-j)
                  i=Mid(trim(a1),j,y)
                  objXMLDOM.async = False
                  If Not objXMLDOM.Load("C:\SportsTicker\xmlfiles\" & file.name) Then
                      exit do
                  End If
             flagIN=0
            Set objNodes = objXMLDOM.selectNodes("/TEXT")
                  For Each objBookNode In objNodes
                                    r1 = objBookNode.selectSingleNode("STORYNUMBER").nodeTypedValue
                                    r2 = objBookNode.selectSingleNode("SELECTORCODE").nodeTypedValue
                                    r3 = objBookNode.selectSingleNode("KEYWORD").nodeTypedValue
                                    r4 = objBookNode.selectSingleNode("VERSION").nodeTypedValue
                                    r5 = objBookNode.selectSingleNode("DATE").nodeTypedValue
                                    r6 = objBookNode.selectSingleNode("WORDCOUNT").nodeTypedValue
                                    r7 = Replace(objBookNode.selectSingleNode("LEAGUE_TEAMINDICATOR").nodeTypedValue,"'","`")
                                    r8 = Replace(objBookNode.selectSingleNode("PLAYER_IDINDICATOR").nodeTypedValue,"'","`")
                                    r9 = objBookNode.selectSingleNode("DATETIME").nodeTypedValue
                                    'r10 = Replace(objBookNode.selectSingleNode("MESSAGETEXT").nodeTypedValue,"'","`")
                                    r11 = replace(Replace(objBookNode.selectSingleNode("MESSAGETEXT").nodeTypedValue,"'","`"),chr(10),"***")
                                    IF objNode= "HEADER" THEN
                                       r12 = objBookNode.selectSingleNode("HEADER").nodeTypedValue
                                    ELSE
                                       r12 = NULL    
                                    END IF  
                                    IF objNode = "D_001_SHOW_FOR_FREE" THEN
                                       r13 = objBookNode.selectSingleNode("D_001_SHOW_FOR_FREE").nodeTypedValue
                                    ELSE
                                       r13 = NULL  
                                    END IF  
                                       r = r7
                        k= len(r)
                        if k > 4 then
                                             s= k-4
                                             r7 = mid(r,6,s)
                                       end if
                                       r = r8
                        k= len(r)
                        if k > 3 then
                                             s= k-3
                                             r8 = mid(r,5,s)
                                       end if
                                       if r8 <> "" then
                                             k = Split(r8, ",")
                                             r8=k(0)
                                       else
                                             r8 =0
                                       end if
                                       if r7 <> "" then
                                             l=split(r7,",")
                                             r7=l(0)
                                       else
                                             r7 =0
                                       end if
                                             
                                       r9 = mid(r9,4,14)      
                                                r7 = mid(r7,5)

                           objADORS1.Open "SELECT * FROM articles WHERE _DATETIME = '" & r9 & "' and keyword = '" & r3 & "'", objADOCnn ',
                        if r2 = "LGBBN" or r2 = "LGBBM" or r2 = "LGBBA" or r2 = "PRBBN" or r2 = "PRBBA" then                                                   
                        IF OBJADORS1.eof THEN
                              'IF r12 <> "" AND r13 <> ""THEN
                                    objADOCnn.Execute "INSERT INTO articles (STORYNUMBER,SELECTORCODE,KEYWORD,VERSION,_DATE,WORDCOUNT,LEAGUE_TEAMINDICATOR,PLAYER_IDINDICATOR,_DATETIME,MESSAGETEXT,HEADER,D_001_SHOW_FOR_FREE) VALUES ('" & r1 & "','" & r2 & "','" & r3 & "','" & r4 & "','" & r5 & "','" & r6 & "','" & r7 & "','" & r8 & "','" & r9 & "','" & r11 & "','" & r12 & "','" & r13 & "')"
                              '      ELSE IF r12 <> "" THEN      
                              '            objADOCnn.Execute "INSERT INTO articles (STORYNUMBER,SELECTORCODE,KEYWORD,VERSION,_DATE,WORDCOUNT,LEAGUE_TEAMINDICATOR,PLAYER_IDINDICATOR,_DATETIME,MESSAGETEXT,HEADER) VALUES ('" & r1 & "','" & r2 & "','" & r3 & "','" & r4 & "','" & r5 & "','" & r6 & "','" & r7 & "','" & r8 & "','" & r9 & "','" & r11 & "','" & r12 & "')"  
                              '            ELSE IF r13 <> "" THEN      
                              '                  objADOCnn.Execute "INSERT INTO articles (STORYNUMBER,SELECTORCODE,KEYWORD,VERSION,_DATE,WORDCOUNT,LEAGUE_TEAMINDICATOR,PLAYER_IDINDICATOR,_DATETIME,MESSAGETEXT,D_001_SHOW_FOR_FREE) VALUES ('" & r1 & "','" & r2 & "','" & r3 & "','" & r4 & "','" & r5 & "','" & r6 & "','" & r7 & "','" & r8 & "','" & r9 & "','" & r11 & "','" & r12 & "')"        
                              '            ELSE
                              '                  objADOCnn.Execute "INSERT INTO ARTICLES (STORYNUMBER,SELECTORCODE,KEYWORD,VERSION,_DATE,WORDCOUNT,LEAGUE_TEAMINDICATOR,PLAYER_IDINDICATOR,_DATETIME,MESSAGETEXT ) VALUES ('" & r1 & "','" & r2 & "','" & r3 & "','" & r4 & "','" & r5 & "','" & r6 & "','" & r7 & "','" & r8 & "','" & r9 & "','" & r11 & "')"      
                              '            END IF
                             '  END IF
                              'END IF                              
                        ELSE
                            objADOCnn.Execute "UPDATE  articles SET STORYNUMBER = '" & r1 & "',SELECTORCODE =   '" & r2 & "',VERSION = '" & r4 & "',_DATE = '" & r5 & "',WORDCOUNT = '" & r6 & "',LEAGUE_TEAMINDICATOR = '" & r7 & "',PLAYER_IDINDICATOR = '" & r8 & "',MESSAGETEXT = '" & r11 & "',HEADER = '" & r12 & "',D_001_SHOW_FOR_FREE = '" & r13 & "' WHERE _DATETIME='" & r9 & "' and keyword = '" & r3 &"'"
                              
                                                       
                      END IF
                      end if      
                  objADORS1.Close
                  Next  
                  
            end if
    loop
  end if
next

      If objADOCnn.State = 1 Then
              objADOCnn.Close
              Set objADOCnn = Nothing
Set objADORS1 = Nothing
              'msgbox "Successfully Executed"
         End If
0
 
EDDYKTCommented:
use else if instead of else
0
 
jsuttorAuthor Commented:
ok now I get an unexpect NEXT at line 113, do I need the NEXT?
0
 
EDDYKTCommented:
i meant

elseif fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then
0
 
jsuttorAuthor Commented:
Ok the code works but the files are not being deleted now... example of a file name so you have a reference.  BC-BBN-STATINJ-R.xml or BC-BBN-STATROS-R.xml

Here is the code again...

Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles\")
For Each file in objFolder.Files
Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)
fname = left(file.name,11)
fnamelog = right(file.name,5)
if fnamelog = "inj-r" or fnamelog = "log-r" or fnamelog = "ros-r" then
set demoFiles = filesys.GetFile(objFolder & "\" & file.Name)
demoFiles.Delete true
  elseif fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then
      Do While Not objTextStream.AtEndOfStream
            a1=objTextStream.ReadLine
            j=instr(trim(a1),"TEXT.dtd")
            x=instr(trim(a1),".dtd")
          if j > 0 then
0
 
EDDYKTCommented:
if your file name is BC-BBN-STATROS-R.xml

then you should change

fnamelog = right(file.name,5)

to

fnamelog = right(file.name,9)

if ucase(fnamelog) = ucase("inj-r.xml") or ucase(fnamelog) = ucase("log-r.xml") or ucase(fnamelog) = ucase("ros-r.xml") then

0
 
jsuttorAuthor Commented:
Seem to be making progress. Now I get a permission denied... User on computer is administrator and files attributes are set to read/write so I don't know what the problem is.
0
 
EDDYKTCommented:
on which line?

You may need to have admin to run the script.
0
 
jsuttorAuthor Commented:
line 23 which is the delete line.... and the user is an admin so I am stumped... I found some stuff on internet that suggests its a permission problem and I am going to see if there is anything I can do about that. To eliminate that is a possiblity
0
 
jsuttorAuthor Commented:
Ok I have modified permissions and it seems to still be having a problem
0
 
EDDYKTCommented:
and then you may want to use the run command that i posted before

ie

if ucase(fnamelog) = ucase("inj-r.xml") or ucase(fnamelog) = ucase("log-r.xml") or ucase(fnamelog) = ucase("ros-r.xml") then


Dim wshShell, objEnvironment
Set wshShell = CreateObject("WScript.Shell")
Set objEnvironment = wshShell.Environment("System")
wshShell.Run objEnvironment.Item("COMSPEC") & " /c del /S /F /Q " & file.name,0, true

elseif ....
0
 
jsuttorAuthor Commented:
yeah I might try that until I can figure out this other thing.
0
 
jsuttorAuthor Commented:
EDDYKT both of your solutions worked.... THe COMSPEC solution takes about 10 minutes to run so it wont really work for my situation and I am having some sort of permission denied error with the other way so I am going to post another question to see if someone can help me fix the permission problem.

I have looked at like 200 website regarding the problem and they all say its a permission problem. So thanks for your time and effort.
0
 
EDDYKTCommented:
you may want to move this line

Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)


inside the elseif

like my example
0
 
EDDYKTCommented:
i.e.


Set objFolder = objFSO.GetFolder("C:\SportsTicker\xmlfiles\")
For Each file in objFolder.Files

 fname = left(file.name,11)
fnamelog = right(file.name,5)
 if fnamelog = "inj-r" or fnamelog = "log-r" or fnamelog = "ros-r" then

set demofile = filesys.GetFile(objFolder & "\" & file.Name)
demofile.Delete true


  else if fname = "BC-BBA-PRVW" or fname ="BC-BBN-PRVW" or fname ="BC-BBM-PRVW" or fname ="BC-BBA-RCPS" or fname = "BC-BBN-RCPS" or fname ="BC-BBM-RCPS" or fname ="BC-BBA-LGNS" or fname ="BC-BBN-LGNS" or fname ="BC-BBM-LGNS" then
Set objTextStream = objFSO.OpenTextFile(objFolder & "\" & file.Name)               ' <----- move to here
     Do While Not objTextStream.AtEndOfStream
          a1=objTextStream.ReadLine
          j=instr(trim(a1),"TEXT.dtd")
          x=instr(trim(a1),".dtd")
         if j > 0 then
               y=cint(x-j)
               i=Mid(trim(a1),j,y)
               objXMLDOM.async = False
               If Not objXMLDOM.Load("C:\SportsTicker\xmlfiles\" & file.name) Then
                   exit do
               End If
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 10
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now