CDRs -Formatting

[the Q]

I downloaded 300,000 CDRs from CAR, and loaded them into Excel. 

IPs and Dates are all screwed up.  Maybe other stuff too.  I created a formula to convert the date, but now I have IPs and who knows what else.

Is there an easier way to get a report of all calls for a certain user with two phones over two months?  This is such a waste of time.  I know I've seen nice neat lists of calls per extension in the past at other places.... Do I have to buy software for this?

[seanvaid]

There is software that will do it, sure. But cdr's are relatively straight forward once you figure out the forumlas for date/time.

What specifically are you having issues with? I recommend doing an initial cleanup to get rid of rows and columns you don't need. 

[the Q]

Straight forward? ... Ha.  No. ... No it's not. 

To start with you are usually pushing the limits of Excel and it can take 15 minutes just to load the CSV.  And you may need to do this several times to get it right.

After the date thing is re-figured out again, the numbers starting with zero have the leading zeros dropped, and some numbers get turned into exponential notation, so I have to start over and import it as text. 

Oh! but now the date doesn't work now.  Dick around with that for an hour to get that fixed. Or try importing only that column as 'general'. 

Yeah, that worked but now I see there A SECOND DATE FIELD.  So wipe my modifications and import both columns as general, or try to convert the column.  Nope, that doesn't work. 

Lunch time. 

Now there seem to be 25 columns of calling and called numbers - redirected final transferred terminated answered numbers, or whatever.  They mostly match, ... but they don't match. 

And overall 250-ish columns to plow through is kind of humorous. 

.

I understand the "give them every value" idea, but to the request "can you get me a quick CDR report of xyz?" I have to say "No!  There's no such thing as a quick CDR report.  It takes hours, or all day."

You sound like my ex-boss.  "you just download a spreadsheet and filter it, don't you?  Why does that take more than 10 minutes." 

[Jaime Valencia · Accepted solution]

If you're exporting the flat files, then you need to do all the processing to get the data as you want, otherwise, the built-in option is to use CAR and see if some of the canned reports meet your needs.

Otherwise, yes, you'd need to buy a 3rd party billing server which will receive the flat files, process them, and give you the reports you need.

[the Q]

Thanks Jaime