Link to home
Start Free TrialLog in
Avatar of jsuttor
jsuttor

asked on

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
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image
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.
Avatar of jsuttor
jsuttor

ASKER

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
Avatar of Carl Tawn
Carl Tawn
Flag of United Kingdom of Great Britain and Northern Ireland image
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
ASKER CERTIFIED SOLUTION
Avatar of EDDYKT
EDDYKT
Flag of Canada image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jsuttor
jsuttor

ASKER

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.
Avatar of jsuttor
jsuttor

ASKER

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
Avatar of EDDYKT
EDDYKT
Flag of Canada image
use else if instead of else
Avatar of jsuttor
jsuttor

ASKER

ok now I get an unexpect NEXT at line 113, do I need the NEXT?
Avatar of EDDYKT
EDDYKT
Flag of Canada image
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
Avatar of jsuttor
jsuttor

ASKER

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
Avatar of EDDYKT
EDDYKT
Flag of Canada image
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

Avatar of jsuttor
jsuttor

ASKER

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.
Avatar of EDDYKT
EDDYKT
Flag of Canada image
on which line?

You may need to have admin to run the script.
Avatar of jsuttor
jsuttor

ASKER

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
Avatar of jsuttor
jsuttor

ASKER

Ok I have modified permissions and it seems to still be having a problem
Avatar of EDDYKT
EDDYKT
Flag of Canada image
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 ....
Avatar of jsuttor
jsuttor

ASKER

yeah I might try that until I can figure out this other thing.
Avatar of jsuttor
jsuttor

ASKER

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.
Avatar of EDDYKT
EDDYKT
Flag of Canada image
you may want to move this line

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


inside the elseif

like my example
Avatar of EDDYKT
EDDYKT
Flag of Canada image
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