Wholesale SIP Trunking - How to Import CDR Files Into An Excel Spreadsheet
For easy viewing of Call Data Records, import the data into an Excel Spreadsheet. For those who are unfamiliar with how to access and view CDRs click here. Please note this guide is for Office 2016. Other versions may be different, but the steps taken here should only vary slightly.
To import the file into a spreadsheet, first save the file to the computer. From the index page of the file, right-click and save the file to the computer. It is important to save the file with the .CDR extension; to do so, utilize the 'All Files' option when saving the file. (Screen shots are provided of this entire process for reference.)
Once the file is saved to the computer data can be imported. Open Excel and click 'Open' (note* The Open button may need enabled depending on how the Excel program is configured or which version is used). This can be accomplished by clicking Ctrl+O.
Browse to the .CDR file that was saved and click 'Open' (the 'All Files' option might be needed again to make the file visible). Once the file is located, click 'Open', a Wizard should open in Excel. Ensure the radio button is set to 'Delimited' and click 'Next'. Under the 'Delimiters' options, ensure only 'Semicolon' is checked and click 'Finish'. The data should now be visible in the spreadsheet.
One thing to note is that Excel does not maintain the date/time format, but this can be changed. To do so, highlight the 'StartTime' column, right-click and select 'Format Cells'. On the resulting window, select 'Custom' and under 'Type' enter yyyy:mm:dd hh:mm:ss to format the column to display the year/month/day/hour/minute/second correctly. This can also be done for the StopTime column.
Below are screenshots of the entire process.
Right-click and choose 'Save Link As'
Ensure file is saved with .CDR extension
Add the Open option to Excel and navigate to browseto the file destination
Browse to file utilizing 'All Files'
Open the file in Excel
Highlight the first column
Click the 'Text to Columns' button
Set the file type as 'Delimited'
Set the Delimiter as Semicolon
Highlight the StartTime Column
Right-click and choose 'Format Cells...'
Choose Custom and enter yyyy/d/m hh:mm:ss
Cells are properly formatted