I dumped some CDR files from our phone system and I need to sort out the data in the CVS file. Doing it manually takes almost all day long to do it as we get countless calls. I found this code online which works great to delete all the columns I don't need. However, it's only doing part of what I need to do.
Other things I would like to do to the file are:
Change time from epoch "xxxxxxxxxx" to time "dd/mm/yy hh:mm:ss"
Autofit Column Width
Change the column names from "dateTimeOrigination" to "Date Time" ect.
Apply this automatically to any CDR.cvs dump file I open.
I am using Excel 2016.Thanks!!!!
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
Other things I would like to do to the file are:
Change time from epoch "xxxxxxxxxx" to time "dd/mm/yy hh:mm:ss"
Autofit Column Width
Change the column names from "dateTimeOrigination" to "Date Time" ect.
Apply this automatically to any CDR.cvs dump file I open.
I am using Excel 2016.Thanks!!!!
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