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