Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • Last Modified:

Excel and Phyton script

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
Sean
Asked:
Sean
  • 13
  • 7
  • 2
1 Solution
 
gbanikCommented:
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
 
gbanikCommented:
See attached file for reference.
ReadTxtFile.xlsm
0
 
SeanAuthor Commented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
SeanAuthor Commented:
qbanik.

nice. if I cant get what I want I could certainly use your code.
Thank you
S
0
 
Michael FowlerSolutions ConsultantCommented:
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
 
SeanAuthor Commented:
Michael,
please see there snapshot:

Thanks jpg
0
 
Michael FowlerSolutions ConsultantCommented:
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
 
SeanAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
SeanAuthor Commented:
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
 
SeanAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
SeanAuthor Commented:
Michael,
Here is the list. Of course this list will be updated from time to time.
Much appreciated.
S
list.xls
0
 
SeanAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
SeanAuthor Commented:
Thanks Michael. I will put in to use and let you know.

Sean
0
 
SeanAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
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
 
SeanAuthor Commented:
Thanks Michael. Looks great let me give it a try.
S
0
 
SeanAuthor Commented:
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
 
Michael FowlerSolutions ConsultantCommented:
No problem
Book1M-1.xls
0
 
SeanAuthor Commented:
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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