CDR CVS help needed

[thinkloko]

I found this code online which works great along another one that also works but I like this one as it looks cleaner. I am using this to edit a CDR file from our phone system at work. I have a few more things I would like to do to the file. I would like to:
Change time from epoch "*********x" to time "dd/mm/yy hh:mm:ss"
Autofit Column Width
Change the column names from "dateTimeOrigination" to "Date Time" etc.
Apply this automatically to any CDR dump file I open.


I am using Excel 2016.Thanks!!!!

PS. I tried most of this stuff manually and it took me almost all workday we get countless calls a day here. Your help is truly appreciated.



Sub deleteIrrelevantColumns()
Dim keepColumn As Boolean
Dim currentColumn As Integer
Dim columnHeading As String


currentColumn = 1
While currentColumn <= ActiveSheet.UsedRange.Columns.Count
columnHeading = ActiveSheet.UsedRange.Cells(1, currentColumn).Value


'CHECK WHETHER TO KEEP THE COLUMN
keepColumn = False
If columnHeading = "dateTimeOrigination" Then keepColumn = True
If columnHeading = "callingPartyNumber" Then keepColumn = True
If columnHeading = "originalCalledPartyNumber" Then keepColumn = True
If columnHeading = "finalCalledPartyNumber" Then keepColumn = True
If columnHeading = "dateTimeConnect" Then keepColumn = True
If columnHeading = "dateTimeDisconnect" Then keepColumn = True
If columnHeading = "lastRedirectDn" Then keepColumn = True
If columnHeading = "duration" Then keepColumn = True




If keepColumn Then
'IF YES THEN SKIP TO THE NEXT COLUMN,
currentColumn = currentColumn + 1
Else
'IF NO DELETE THE COLUMN
ActiveSheet.Columns(currentColumn).Delete
End If


'LASTLY AN ESCAPE IN CASE THE SHEET HAS NO COLUMNS LEFT
If (ActiveSheet.UsedRange.Address = "$A$1") And (ActiveSheet.Range("$A$1").Text = "") Then Exit Sub
Wend


End Sub

[Paul_Hossler]

1. I added CODE tags around your macro to format it - you can use the [#] icon to do it

2. There was no sample file, and the requirements were a little fuzzy, but a little restructuring might make it easier for you to get the way you want



Option Explicit

Sub deleteIrrelevantColumns()
Dim rUsed As Range
Dim iCol As Long

Set rUsed = ActiveSheet.UsedRange

With rUsed
For iCol = .Columns.Count To 1 Step -1
Select Case .Cells(1, iCol).Value
Case "dateTimeOrigination"
.Cells(1, iCol).Value = "Origination"
.Columns(iCol).NumberFormat = "dd/mm/yy hh:mm:ss"

Case "callingPartyNumber"
.Cells(1, iCol).Value = "Number"

Case "originalCalledPartyNumber"
.Cells(1, iCol).Value = "Original"

Case "finalCalledPartyNumber"
.Cells(1, iCol).Value = "Final"

Case "dateTimeConnect"
.Cells(1, iCol).Value = "Connected"
.Columns(iCol).NumberFormat = "dd/mm/yy hh:mm:ss"

Case "dateTimeDisconnect"
.Cells(1, iCol).Value = "Disconnected"
.Columns(iCol).NumberFormat = "dd/mm/yy hh:mm:ss"

Case "lastRedirectDn"
.Cells(1, iCol).Value = "Redirection"

Case "duration"
.Cells(1, iCol).Value = "Duration"
Case Else
ActiveSheet.Columns(iCol).Delete
End Select

Next iCol
End With

ActiveSheet.Cells(1, 1).EntireColumn.AutoFit

End Sub

[thinkloko]

1. I added CODE tags around your macro to format it - you can use the [#] icon to do it

2. There was no sample file, and the requirements were a little fuzzy, but a little restructuring might make it easier for you to get the way you want



Option Explicit

Sub deleteIrrelevantColumns()
Dim rUsed As Range
Dim iCol As Long

Set rUsed = ActiveSheet.UsedRange

With rUsed
For iCol = .Columns.Count To 1 Step -1
Select Case .Cells(1, iCol).Value
Case "dateTimeOrigination"
.Cells(1, iCol).Value = "Origination"
.Columns(iCol).NumberFormat = "dd/mm/yy hh:mm:ss"

Case "callingPartyNumber"
.Cells(1, iCol).Value = "Number"

Case "originalCalledPartyNumber"
.Cells(1, iCol).Value = "Original"

Case "finalCalledPartyNumber"
.Cells(1, iCol).Value = "Final"

Case "dateTimeConnect"
.Cells(1, iCol).Value = "Connected"
.Columns(iCol).NumberFormat = "dd/mm/yy hh:mm:ss"

Case "dateTimeDisconnect"
.Cells(1, iCol).Value = "Disconnected"
.Columns(iCol).NumberFormat = "dd/mm/yy hh:mm:ss"

Case "lastRedirectDn"
.Cells(1, iCol).Value = "Redirection"

Case "duration"
.Cells(1, iCol).Value = "Duration"
Case Else
ActiveSheet.Columns(iCol).Delete
End Select

Next iCol
End With

ActiveSheet.Cells(1, 1).EntireColumn.AutoFit

End Sub



This worked perfect but the time is still not converted. The example epoch time would be 1507525449 it would have to be converted to dd/mm/yy hh:mm:ss. Also is there a way to do this automatically on different CSV files? Thanks a lot for your help it is much appreciated.

[Paul_Hossler]

Try this then




Option Explicit

Dim rUsed As Range

Sub deleteIrrelevantColumns()
Dim iCol As Long

Set rUsed = ActiveSheet.UsedRange

With rUsed
For iCol = .Columns.Count To 1 Step -1
Select Case .Cells(1, iCol).Value
Case "dateTimeOrigination"
.Cells(1, iCol).Value = "Origination"
Call pvtEpochToExcel(iCol)

Case "callingPartyNumber"
.Cells(1, iCol).Value = "Number"

Case "originalCalledPartyNumber"
.Cells(1, iCol).Value = "Original"

Case "finalCalledPartyNumber"
.Cells(1, iCol).Value = "Final"

Case "dateTimeConnect"
.Cells(1, iCol).Value = "Connected"
Call pvtEpochToExcel(iCol)

Case "dateTimeDisconnect"
.Cells(1, iCol).Value = "Disconnected"
Call pvtEpochToExcel(iCol)

Case "lastRedirectDn"
.Cells(1, iCol).Value = "Redirection"

Case "duration"
.Cells(1, iCol).Value = "Duration"
Case Else
ActiveSheet.Columns(iCol).Delete
End Select

Next iCol
End With

ActiveSheet.Cells(1, 1).EntireColumn.AutoFit

End Sub

'https://www.extendoffice.com/documents/excel/2473-excel-timestamp-to-date.html
Private Sub pvtEpochToExcel(ColNum As Long)
Const c01Jan1970 As Long = 25569
Dim iRow As Long, ET As Long
With rUsed

For iRow = 2 To .Rows.Count
If IsNumeric(.Cells(iRow, ColNum).Value) Then
ET = CLng(.Cells(iRow, ColNum).Value)
.Cells(iRow, ColNum).Value = (((ET / 60#) / 60#) / 24#) + c01Jan1970
End If
Next iRow
.Columns(ColNum).NumberFormat = "dd/mm/yy hh:mm:ss"

End With
End Sub




Your 1507525449 converts to 10/9/2017 5:04:09 AM using the formula I googled in the referenced link


?cdate((((1507525449/60)/60)/24)+25569)
10/9/2017 5:04:09 AM

[thinkloko]

Thank you! That is amazing work. I found this formula to do the epoch conversion on excel manually (A1/86400)+25569+(n/24) I replaced the n with a -6 for my time zone and the content of A1 would be the same 1507525449 which gave me 10/8/17 11:04 PM instead of 10/9/2017 5:04:09 AM. Why is there such a huge difference in time?

[Paul_Hossler]

I'd guess that it's an adjustment for GMT to local time

[thinkloko]

I'd guess that it's an adjustment for GMT to local time

I read that epoch/UTC time is ahead 6 hours of central time which is the time I need. Would it be possible to modify the code so it can subtract 6 hours after it converts it? Thanks!

[Paul_Hossler]

Try changing the line to this



.Cells(iRow, ColNum).Value = (((ET / 60#) / 60#) / 24#) + c01Jan1970 - 0.25

[thinkloko]

Try changing the line to this



.Cells(iRow, ColNum).Value = (((ET / 60#) / 60#) / 24#) + c01Jan1970 - 0.25



Amazing!!! Thank you!!!