Solved

Excel and Phyton script

Posted on 2010-11-17
22
286 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
[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
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
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
 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Dictionaries contain key:value pairs. Which means a collection of tuples with an attribute name and an assigned value to it. The semicolon present in between each key and values and attribute with values are delimited with a comma.  In python we can…
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

738 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