Phone Records to Excel: Call Logs in a Criminal Case

To get phone records to Excel in a criminal case, ask first for the carrier's own spreadsheet file, because a native file needs no conversion. When all you have is a printout or a scan, run it through OCR, export the call log table to a workbook, store number columns as text, and check every row against the page.

The conversion is the short part. The work that protects the client is in the columns: a time read in the wrong zone, a dialed number that lost a digit, a tower code that a spreadsheet silently rewrote. This post goes through the page, the conversion, and the checks, and ends with a way to set the calls beside the times in the police reports.

Give the carrier cover pages their own pass once the rows are in Excel. Search them for how the printout describes this electronically stored information and for the certificate you will use on authentication. Keep the source page beside every row so the chain of custody of the paper stays clear. Treat the rows as documentary evidence, and send any doubtful number back to the scan before you cite the row under the best evidence rule.

What do subpoenaed phone records look like on the page?

Subpoenaed phone records on paper are a wide table with one row per call or message and a column for each recorded fact, alongside a few pages of account information. The layout, column names and abbreviations are the carrier's own, so start by reading your set from first page to last.

The federal statute that governs what a provider hands to the government gives a rough inventory of the account pages. 18 U.S.C. § 2703(c)(2) lists what a provider must disclose in response to a subpoena: the subscriber's "name", "address", "local and long distance telephone connection records, or records of session times and durations", "length of service (including start date) and types of service utilized", "telephone or instrument number or other subscriber number or identity, including any temporarily assigned network address", and "means and source of payment for such service (including any credit card or bank account number)". Location columns are a separate matter. In Carpenter v. United States the Supreme Court held that "the Government must generally obtain a warrant supported by probable cause before acquiring" historical cell-site records. Which process produced your set, and whether it was the right one, is a legal question for the case. For conversion purposes, what matters is which of these parts are in the file.

Go through the set and mark each page as one of these:

A set may lack some of these. If there is no legend, do not guess what a column means. Ask the party that produced the records, or the carrier's custodian, for the key that goes with that report format, and write down where the answer came from.

Two features of the page decide how well it converts. The first is width. A report with twenty columns printed on letter paper has small type and narrow gaps between columns, and narrow gaps are where two values run together. The second is repetition. Each page may repeat the header row, a page number, and a confidentiality line, all of which will land in your spreadsheet as rows that are not calls.

In what form do call detail records reach defense counsel?

Call detail records reach defense counsel in one of three forms: the carrier's native spreadsheet or delimited text file, a PDF made from that file, or a scan of a paper printout. Only the third needs OCR. Find out which one you have before you convert anything, and ask for the native file if you were not given it.

In a federal case the route is discovery or your own subpoena. Federal Rule of Criminal Procedure 16(a)(1)(E) requires the government, on request, to permit the defendant "to inspect and to copy or photograph books, papers, documents, data, photographs, tangible objects, buildings or places, or copies or portions of any of these items" that meet one of the rule's three conditions. The word "data" is in that list. The paragraph does not name a file format. Rule 17(c)(1) says a subpoena "may order the witness to produce any books, papers, documents, data, or other objects the subpoena designates." State criminal discovery and subpoena rules are separate and differ, so read the rule and any standing order in your own court before you rely on either quotation.

Telling the three forms apart

• A native file opens in a spreadsheet program with one value per cell. It has an extension such as .xlsx, .csv or .txt. Nothing needs recognizing. The checks on times, numbers and tower codes still apply to it in full.
• A PDF made from the file shows crisp type at any zoom, and you can select a row with the cursor. The characters are already in the file. Copying the table out may still scramble the columns, so compare a copied page with a converted page and use whichever keeps the columns in place.
• A scan shows soft or speckled type when you zoom in, and the cursor selects nothing. Every character has to be recognized, and every character can be wrong.

The post on why you cannot copy text from a PDF lists more ways to tell whether a PDF has real text.

What to ask for

If you hold a printout, write to the producing party and ask whether the carrier returned an electronic file, and ask for that file in the form the carrier sent it. A native file removes the recognition step and the errors that come with it. Ask for the legend and the tower list at the same time, in the same form.

Keep the custodian's certification with whatever you receive. Federal Rules of Evidence 902(11) and 902(13) deal with certified records of a regularly conducted activity and certified records "generated by an electronic process or system", and both carry a notice requirement. Whether and how those rules apply to your records is for the lawyer trying the case. The practical point is that the certification describes one specific set of records, so your workbook should say which set it was built from.

Converting a printed call log to spreadsheet rows

Convert a printed call log by scanning or uploading it as it arrived, exporting the recognized table to Excel, stacking the per-page pieces into one sheet, and adding a column that ties each row to its source page. Then format the number columns as text and count the rows before anyone sorts or filters.

1. Lock the original. Save the file you received in a folder nobody edits. Record its page count. Every later question about what the records say is answered from this copy.
2. Read the legend. Write the column names, their meanings and the stated time zone at the top of a notes sheet in your workbook.
3. Split if you must. Check the file against the size and page limits of the tool you use. Schema Law takes a PDF, PNG or JPEG of up to 20 MB, and the page limit per document depends on the plan, as the pricing page shows. Split at page boundaries and put the page range in each file name, for example calls 0001-0200.pdf.
4. Convert and export. Upload the file, look at the parsed pages on screen, and export Excel. The Excel export puts each table on its own sheet and the rest of the text on another, and it is on every plan. The full list is under export formats on the homepage.
5. Stack the sheets. A log that runs for 60 pages may come out as many tables. Copy them into one sheet in page order. Before you paste each one, type its source page number into a new first column so every row carries it. The Excel conversion post has the full routine for joining tables that span several pages.
6. Remove what is not a call. Delete repeated header rows, page footers and confidentiality lines from the stacked sheet only. Leave the per-table sheets untouched so the removal can be traced.
7. Set the formats. Format the calling number, called number, device identifier and every tower or sector column as Text. Do this before you paste values in, because a format applied afterwards does not bring back characters already dropped.
8. Count. If the printout numbers its rows or states a total, your stacked sheet must match it. If it does not, count the rows on five pages by hand and compare.

Step 7 exists because of how Excel treats digits. Microsoft's support article on keeping leading zeros and large numbers says that "Excel automatically removes leading zeros, and converts large numbers to scientific notation, like 1.23E+15", and that Excel "has a maximum precision of 15 significant digits", so digits past the fifteenth "are rounded down to zero." A phone number or device identifier that goes through that conversion is no longer the value on the page.

If an analyst or an expert will load the records into their own software, ask what file they want. The post on converting a scanned PDF to CSV covers delimiters, quoting and what a CSV does and does not keep.

Nothing in the converted workbook marks a cell as doubtful. The Schema Law homepage says recognition is strongest on clean print and that nothing marks a passage as uncertain, so the row-by-row comparison with the page is yours to do. For a long log, check every row that the defense theory touches, plus a fixed sample of the rest, such as the first and last row of every page. Record who checked and when in a column of the sheet.

Time zones, call durations and number formats in phone records

Three fields in phone records mislead after conversion: the time, because the page may not be in local time; the duration, because its unit is not always stated in the column; and the phone number, because a spreadsheet treats it as a quantity. Settle each from the legend before you draw any conclusion from a row.

Time zones

Find the sentence in the legend or cover letter that states the time zone of the report. It may say local time at the switch, a fixed zone used across the carrier's network, or Coordinated Universal Time. If the set does not say, that is a question for the custodian, and the answer belongs in your notes sheet with its source.

Never overwrite the time on the page. Keep the original column as text and add a second column for the converted local time. Suppose a fictional log, stated in UTC, shows a call at 03:12:40 on March 9 and the events took place in a city on Eastern Standard Time, five hours behind UTC. The local time is 10:12:40 PM on March 8. The date changed as well as the hour. A reader who took the row at face value would place the call on the wrong day.

Daylight saving time moves the offset by an hour on two dates a year. If the period in the records crosses one of those dates, convert the rows on each side separately and note the offset you used on each.

Durations

A duration column may hold seconds, minutes, or minutes and seconds. 65 and 1:05 can mean the same call. Excel reads a typed 1:05 as a time of day, five past one in the morning, and will then add and sort it as such. Bring the column in as text, read the unit from the legend, and compute a clean seconds column beside it.

Check also which moment the timestamp records: the start of the call, the end, or the moment the network set it up. A long call that starts before an event and ends after it reads very differently depending on the answer.

Number formats

The same telephone number may appear as ten digits, as eleven digits with a leading 1, or with a plus sign and a country code, and one report can mix the three. Keep the column exactly as printed. Add a normalized column beside it, for example the last ten digits, and run your matching and counting on that.

Recognition errors in digit strings are covered in the post on why OCR gets numbers wrong. For a call log the cheap test is length: add a column that counts the characters in each number, and filter for anything that is not the length you expect. A nine-digit or twelve-digit entry is a row to open against the page.

Tower and sector columns: keeping location codes intact

Tower and sector columns are identifiers, so the only correct conversion is the one that leaves every character as printed. Store them as text, never let a spreadsheet reformat them, and do not merge them with the tower list by hand. An expert will need the codes exactly as the carrier wrote them.

The Supreme Court's opinion in Carpenter v. United States describes the underlying system in plain terms: "Cell sites typically have several directional antennas that divide the covered area into sectors." It also records the testimony of the government's expert in that case that "each time a cell phone taps into the wireless network, the carrier logs a time-stamped record of the cell site and particular sector that were used." Those two facts are why a location report has a column for the site and another for the sector. Some layouts carry one pair for the start of a call and another for the end, which the legend will tell you.

What goes wrong with these columns in a spreadsheet:

• Leading zeros vanish. Sector 01 becomes 1, and a site code 004417 becomes 4417.
• Codes with a letter E turn into numbers. Microsoft's support article on keeping leading zeros and large numbers lists the conversion of numerical data surrounding the letter E to scientific notation among Excel's automatic conversions, so test what your copy of Excel does with a code such as 12E4 before you paste a column of them.
• Coordinates are rounded or lose their sign. A longitude in the western hemisphere is negative. A dropped minus sign moves the point to another continent, and a coordinate cut to two decimal places can move it by several hundred metres.
• Paired columns slide. When a row has no ending tower, recognition may shift the next value left into the empty cell.

The defences are the same each time. Format the columns as Text before pasting. Compare the count of filled cells in each location column with the page. Sort a copy of the sheet by tower code and look for values that appear only once, because a code that occurs a single time in a month of records deserves a look at the page before anyone relies on it.

Keep the tower list as its own sheet with its own source-page column. Joining it to the call rows is the analyst's job, done by lookup on the exact code, and it is easy to audit when both tables are intact. A join done by typing addresses into the call sheet cannot be audited at all.

One limit belongs in your notes. A row shows which site and sector handled a connection according to the carrier's record. What that says about where a phone was is opinion evidence for a qualified witness, and nothing in a converted workbook answers it.

How do you line up call log rows with the times in the police reports?

Line up call log rows with the police reports by putting both on one clock in one sheet: each call and each reported event becomes a row with a local time, a source and a page. Sort by time, and read the places where a call falls inside, just before or just after something an officer or witness describes.

Build the report side first. The method for pulling events and times out of converted reports, with a column layout, is in the post on building a timeline from converted police reports. Then add the calls using the converted local-time column, never the raw one.

A short fictional example, with the log already converted from UTC to local time:

The last two rows are the ones to study. The report puts the stop at 22:14 and the log shows a call of more than three minutes beginning within the same minute. That may be consistent with the report or may not be, but it is a question nobody could ask until both rows sat in one sheet.

Three cautions keep the comparison honest:

• The clocks are different clocks. Nothing guarantees that the carrier's network time, the dispatch system's time and an officer's watch agree. Record the clock behind each row, and treat a gap of a minute or two as unexplained until someone with knowledge accounts for it.
• Precision differs. A report that says "approximately 2215 hours" is not the same kind of statement as a log entry to the second. Keep the original wording in a column so the difference stays visible.
• A row is not a person. The log shows activity on an account or device. Who was holding the phone is a separate question, and the sheet should not assume the answer in its labels. Write "outgoing call from the 0142 number" and leave the name out.

If the file also holds printouts of the messages themselves, or screenshots from a handset, those are a different kind of page with headers, timestamps and threads. The post on OCR for employment records explains what conversion does with printed emails and chat exports, and the same cautions apply to message printouts in a criminal file.

Before the sheet is used for anything that will be filed or shown to a witness, have a second person read each relied-on row against the page image and initial it. The workbook is a finding aid. The exhibit is still the record the custodian certified.