Phone records that came as a PDF
S.A.M.M. imports phone records from a spreadsheet saved as CSV. Most carriers send one, but some send a PDF. Here is how to turn the PDF into that spreadsheet. Keep the PDF: it is the record, and the spreadsheet is a working copy of it.
1. Let Excel read the PDF
In Excel for Microsoft 365, choose Data > Get Data > From File > From PDF and pick the PDF. Excel lists the tables it found. Select the one with the records and click Load. When the records run over many pages, Excel may list one table per page: load each one and paste them one under another.
Adobe Acrobat Pro can do the same: Export PDF > Spreadsheet > Microsoft Excel Workbook.
If you can't select the text in the PDF, it is a scan (pictures of pages) and neither will read it. Type its rows into the template instead, and have someone else check them.
2. Fill in the template
Open the template and copy each column from the carrier's sheet under its heading. Only Date Time, Latitude and Longitude are required; leave any other column empty when the carrier didn't give it. Keep the headings as they are: S.A.M.M. recognizes them and lines every column up by itself.
| Template heading | What goes in it | Carriers often call it |
|---|---|---|
| Date Time | When the call, text or data session started. | Date/Time, Start Time, Call Date |
| Latitude, Longitude | Where the tower is, in decimal degrees, such as 40.30412 and -74.06518. West and south are negative. | Lat / Long, Tower Lat / Tower Long |
| Azimuth | The way the antenna faces, 0 to 359 degrees. | Azimuth, Sector Azimuth, AZ |
| Beam Width | How wide the sector is, in degrees. | Beam Width, HBW |
| Cell ID | The tower and sector. | Cell ID, CGI, ECGI, Site ID |
| Record Type | Call, text or data. | Call Type, Usage Type, Event Type |
| Direction | Incoming or outgoing. | Direction, Call Direction |
| Duration (seconds) | How long it lasted, in seconds (1:34 is 94). | Duration, Elapsed |
| Calling Number | Who placed the call. | Calling Number, A Number, Originating |
| Called Number | The number dialed. | Called Number, Dialed, B Number |
| Notes | Anything else worth keeping. |
3. When the towers are on a separate list
Some carriers put each call's cell ID on the call rows, and each tower's position and azimuth in a separate cell site list. Bring the tower details onto the call rows by matching the Cell ID. With the cell site list on a sheet named Towers (cell IDs in column A, latitude in B, longitude in C, azimuth in D) and the Cell ID in column F of the template, this fills a call's Latitude; drag it down the column, and do the same for Longitude and Azimuth with columns C and D:
=XLOOKUP(F2, Towers!A:A, Towers!B:B, "")A blank result means that cell ID isn't on the list: look it up in the PDF before you import.
4. Leave the times as written
Copy dates and times exactly as the carrier wrote them, and note the time zone the PDF names (carriers often use UTC). Don't convert them: you tell S.A.M.M. the time zone when you import, and it shows both times. If the PDF has the date and the time in separate columns, replace Date Time with two columns headed Date and Time; S.A.M.M. recognizes those too.
5. Save it as CSV
Choose File > Save As and the type CSV UTF-8 (Comma delimited). If Excel warns that some features will be lost, keep the CSV format.
6. Import it and check it
- In S.A.M.M., open Phone data and click Import phone data, then pick the CSV.
- Check the columns S.A.M.M. lined up, set the time zone the PDF named, and import.
- Compare the first record, the last and a few others with the PDF: the time, the tower and the numbers. A sheet typed by hand needs a second person to check it.
- Import the PDF into the case as evidence too, so the original stays with the case.
In S.A.M.M., Records came as a PDF? in the Phone data panel has these steps and saves the template for you. Stuck on a carrier's layout? Email [email protected] (leave out any case details).