Solved

Excel and Phyton script

Posted on 2010-11-17
22
278 Views
Last Modified: 2012-05-10
Hi,
I have this Phyton script (phyton.txt) that runs every night and creates this list (recording.txt) basically it looks in a folder/drive  and counts the number of files based on certain criteria and provides total number of files for each extension.
Here is a sample of what the contents of the drive/folder would look like (recordings.jpg).
What I need is to be able to do this in excel. as a macro if possible.
Click on a button and I would have the recording.txt results in the sheet  like this (book22.xls).
(It does not have to be a phyton script that does the counting)

Thanks
S

Book22.xls
phyton-script.txt
Recordings.txt
recodings.jpg
0
Comment
Question by:Sean
  • 13
  • 7
  • 2
22 Comments
 
LVL 13

Expert Comment

by:gbanik
ID: 34160455
Here is your code.... set the file path the variable "sFilePath" and add Microsoft Scripting Runtime as a reference to your project.
Option Explicit

Dim fso As New Scripting.FileSystemObject
Private Const sFilePath As String = "G:\Documents and Settings\Gautam K Banik\My Documents\Downloads\Recordings.txt"

Public Sub ReadFile()
Dim txt As TextStream, sText As String, vSpl As Variant, nCtr As Integer

Application.Cells.ClearContents

'Open the txt file for reading
Set txt = fso.OpenTextFile(sFilePath, ForReading)
sText = txt.ReadAll
txt.Close
Set txt = Nothing
vSpl = Split(sText, vbCrLf)

'This is to stop excel from refreshing everytime we write data... it speeds up operations
Application.ScreenUpdating = False
Application.EnableEvents = False

For nCtr = 0 To UBound(vSpl)
    If InStr(1, vSpl(nCtr), "-") > 0 Then
        Application.Range("A1").Offset(nCtr).Value = Trim(Split(vSpl(nCtr), "-")(0))
        Application.Range("A1").Offset(nCtr, 1).Value = Trim(Split(Split(vSpl(nCtr), "-")(1), " ")(0))
        Application.Range("A1").Offset(nCtr, 2).Value = Trim(Split(Split(vSpl(nCtr), "-")(1), " ")(1))
    ElseIf InStr(1, vSpl(nCtr), ":") > 0 Then
        Application.Range("A1").Offset(nCtr).Value = Trim(Replace(Split(vSpl(nCtr), ":")(0), Chr(9), ""))
        Application.Range("A1").Offset(nCtr, 1).Value = Trim(Replace(Split(vSpl(nCtr), ":")(1), Chr(9), ""))
    Else
        Application.Range("A1").Offset(nCtr).Value = Trim(vSpl(nCtr))
    End If
Next


'Reset the screen updating after the operations
Application.ScreenUpdating = True
Application.EnableEvents = True

End Sub

Open in new window

0
 
LVL 13

Expert Comment

by:gbanik
ID: 34160463
See attached file for reference.
ReadTxtFile.xlsm
0
 
LVL 1

Author Comment

by:Sean
ID: 34160508
Hi qbanik,
sorry for the confusion. I am trying to by pass the creation of the recording.txt file.
Another words, I am looking for a process that runs in excel.
 Counts the files, sorts them out and outputs the results just as it is in recording,txt that is created by the phyton script  .

Thanks
S
0
 
LVL 1

Author Comment

by:Sean
ID: 34160537
qbanik.

nice. if I cant get what I want I could certainly use your code.
Thank you
S
0
 
LVL 23

Expert Comment

by:Michael74
ID: 34161496
it this what you are after
Option Explicit



Sub filecount()



   Dim fso As Object, allFiles As Object, file As Object, dict As Object

   Dim currentrow As Long, count As Long

   keyStr As String



   On Error GoTo EarlyExit

   

   Set fso = CreateObject("Scripting.FileSystemObject")

   Set allFiles = fso.GetFolder("Z:\").files

   Set dict = CreateObject("Scripting.Dictionary")

    

   For Each file In allFiles

      If UCase(Right(file.Path, (Len(file.Path) - InStrRev(file.Path, ".")))) = UCase("WAV") Then

         keyStr = Mid(file.Path, 4, 9) & " " & Mid(file.Path, 41, 3)

         If Not dict.exists(keyStr) Then

            dict.Add keyStr, 1

         Else

            dict.Item(keyStr) = dict.Item(keyStr) + 1

         End If

      End If

   

   currentrow = 1

   count = 0

   Application.Sheets.Add

   For Each keyStr In dict.keys

      Range("A" & currentrow).Value = keyStr

      count = count + dict.items(keyStr)

      currentrow = currentrow + 1

   Next

   

   currentrow = currentrow + 1

   Range("A" & currentrow).Value = "Agents: " & dict.count

   currentrow = currentrow + 1

   Range("A" & currentrow).Value = "Recordings: " & count



EarlyExit:

    On Error Resume Next

    Set file = Nothing

    Set files = Nothing

    Set fso = Nothing

    Set dict = Nothing

    On Error GoTo 0



End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Sean
ID: 34161538
Michael,
please see there snapshot:

Thanks jpg
0
 
LVL 23

Expert Comment

by:Michael74
ID: 34161968
sorry try this. I did not get time to test and it had some simple errors



Dim fso As Object, allFiles As Object, file As Object, dict As Object
   Dim currentrow As Long, count As Long
   Dim keyStr As Variant

   On Error GoTo EarlyExit
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   Set allFiles = fso.GetFolder("E:\test").Files
   Set dict = CreateObject("Scripting.Dictionary")
    
   For Each file In allFiles
      If UCase(Right(file.Path, (Len(file.Path) - InStrRev(file.Path, ".")))) = UCase("WAV") Then
         keyStr = Mid(file.Path, 4, 9) & " " & Mid(file.Path, 41, 3)
         If Not dict.exists(keyStr) Then
            dict.Add keyStr, 1
         Else
            dict.Item(keyStr) = dict.Item(keyStr) + 1
         End If
      End If
   Next
   
   currentrow = 1
   count = 0
   Application.Sheets.Add
   For Each keyStr In dict.keys
      Range("A" & currentrow).Value = keyStr
      count = count + dict.items(keyStr)
      currentrow = currentrow + 1
   Next
   
   currentrow = currentrow + 1
   Range("A" & currentrow).Value = "Agents: " & dict.count
   currentrow = currentrow + 1
   Range("A" & currentrow).Value = "Recordings: " & count

EarlyExit:
    On Error Resume Next
    Set file = Nothing
    Set allFiles = Nothing
    Set fso = Nothing
    Set dict = Nothing
    On Error GoTo 0

End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Sean
ID: 34167402
Michael,
Please see attached book23.xls.
as you can see your code results on sheet4 . Sheet1 of the workbook contain the correct info.
Thanks
S
Book23.xls
0
 
LVL 23

Expert Comment

by:Michael74
ID: 34167749
Slimlcd101

Based on your example I assumed that each extension is based on the 4 digit number provided eg 4026

The code below should give the output you requested.

If I have made the wrong assumption regarding how you determine each extension please advise with an example

Note - The code creates a new tab and names it with the current date in the format "dd-mm-yyy". If you do not like this format you can change it in the line
ActiveSheet.Name = Format(Now(), "dd-mm-yyyy")
Private Sub Workbook_Open()

Dim fso As Object, allFiles As Object, file As Object, dict As Object

   Dim currentrow As Long

   Dim keyStr As Variant



   On Error GoTo EarlyExit

     

   Set fso = CreateObject("Scripting.FileSystemObject")

   Set allFiles = fso.GetFolder("Z:\").Files

   Set dict = CreateObject("Scripting.Dictionary")

    

   For Each file In allFiles

      If UCase(Right(file.Path, (Len(file.Path) - InStrRev(file.Path, ".")))) = UCase("WAV") Then

         keyStr = Mid(file.Path, 9, 4)

         If Not dict.exists(keyStr) Then

            dict.Add keyStr, 1

         Else

            dict.Item(keyStr) = dict.Item(keyStr) + 1

         End If

      End If

   Next

   

   currentrow = 2

   Worksheets.Add

   ActiveSheet.Name = Format(Now(), "dd-mm-yyyy")

   Range("A1").Value = "Extension"

   Range("B1").Value = "Call Count"

   

   For Each keyStr In dict.keys

      Range("A" & currentrow).Value = keyStr

      Range("B" & currentrow).Value = dict.Item(keyStr)

      currentrow = currentrow + 1

   Next



EarlyExit:

    On Error Resume Next

    Set file = Nothing

    Set allFiles = Nothing

    Set fso = Nothing

    Set dict = Nothing

    On Error GoTo 0



End Sub

Open in new window

0
 
LVL 1

Author Comment

by:Sean
ID: 34168425
Micheal,
No your assumption is correct. with respect to the 4 digit extension number.
the last code works just fine. Wonderful.
Now I would need to put names next to the extensions but I think that would require posting another question.
Thanks.
0
 
LVL 1

Author Comment

by:Sean
ID: 34168720
Michael,
forgive the question. I am new to excel.
I have been able to set this up to when I open the workbook it files in the data.
How do I set it up as a macro so that I can run the macro when I need to?

Thanks
S
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
LVL 23

Expert Comment

by:Michael74
ID: 34169007
slimlcd101

The macro has been placed into the Private Sub Workbook_Open() event

This event fires when the workbook opens and immediately runs the macro

I have attached the script so that it will run on the short cut CRTL+Z. You can also run the macro by selecting ALT+F8 and selecting the macro processFiles
If you prefer a different shortcut key have a look at
http://msdn.microsoft.com/en-us/library/aa195807%28office.11%29.aspx

To put in a name for each extension is easy. You can either hard code it into the script with a select(switch) statement or use a formula in the sheet and a VLookup table. Please send the list and I will put it into the file for you
0
 
LVL 1

Author Comment

by:Sean
ID: 34169066
Michael,
Here is the list. Of course this list will be updated from time to time.
Much appreciated.
S
list.xls
0
 
LVL 1

Author Comment

by:Sean
ID: 34169748
Micheal,
would you please do me one last favor and have the info sorted according to the extension #.
i.e  :
4000
4001
4002

Thanks
Sean
0
 
LVL 23

Expert Comment

by:Michael74
ID: 34169914
Here you go. You can change the users list at anytime but you cannot have empty cells before the end. Note if there are any duplicates it will match the first entry Book1.xls
0
 
LVL 1

Author Comment

by:Sean
ID: 34170052
Thanks Michael. I will put in to use and let you know.

Sean
0
 
LVL 1

Author Comment

by:Sean
ID: 34170524
Michael,
Please see attached. The names do not match the correct extensions. I think the sorting process is the problem but you know best.
The sheet is exactly what I needed.
Thank YOU
S
Book1M.xls
0
 
LVL 23

Expert Comment

by:Michael74
ID: 34170864
sorry, problem solved and tested.

Quick Note: If you need to make changes
The file location is in the line
 Set allFiles = fso.GetFolder("Z:\").Files

If you change this then you may need to change the line
keyStr = Mid(file.Path, 9, 4)
This line extracts the user extension from the string based on its location in the full path name, so it takes the string starting at position 9 (first char is 1) and gets 4 characters. So if the file naming convention or the path changes you may need to change this line of code.

 Book1M-1.xls
0
 
LVL 1

Author Comment

by:Sean
ID: 34176676
Thanks Michael. Looks great let me give it a try.
S
0
 
LVL 1

Author Comment

by:Sean
ID: 34176802
Michael,
Great. One last request. If its not too much trouble would it be possible to get the total recordings on the sheet too?

Thanks
S
0
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
ID: 34177468
No problem
Book1M-1.xls
0
 
LVL 1

Author Comment

by:Sean
ID: 34177895
Michael you are the best thank you.
Please take a look at question # 26628159. You might be able to answer that one too.
S
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Installing Python 2.7.3 version on Windows operating system For installing Python first we need to download Python's latest version from URL" www.python.org " You can also get information on Python scripting language from the above mentioned we…
Strings in Python are the set of characters that, once defined, cannot be changed by any other method like replace. Even if we use the replace method it still does not modify the original string that we use, but just copies the string and then modif…
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…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

746 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

12 Experts available now in Live!

Get 1:1 Help Now