Microsoft Clipchamp is a user-friendly video editing tool that allows you to create and edit videos with ease. It’s designed for those who may not have advanced technical skills, offering simple features like trimming, adding text, and applying filters to enhance your videos. Clipchamp is available on Windows 10 and Windows 11 through the Microsoft Store, or you can also run it from https://clipchamp.com (Chrome or Microsoft Edge only). Note: You will need a Microsoft or Google email address to use Clipchamp. However, for FDO users, your fd.org address will NOT work due to our enterprise license with Microsoft. You will have to use a personal Microsoft email address such as a @hotmail.com or @Outlook.com. Both the app version and the online version look the same and offers the same features.
My favorite feature is the trimming feature since it’s very intuitive and simple to use. The home screen looks like this:
To start a project, click on the “Create a new video” box. This will open Clipchamp.
After Clipchamp is open, the first step is to bring in the media or video that you would like to trim by clicking on Import Media. You can either drag and drop the media or click on the Import Media box to manually browse to the media file you want to trim. After you import your media file, you then want to drag the media file to the editing\trimming area.
Once the video is in the trimming area, you will see two green handles each at the beginning and at the end of the video. You can click and drag these handles if you want to shorten the video from either end. Below I have shrunk the video from 11:23 minutes to 7:11 minutes. Once I’m happy with the result, I can hit Export and select the video quality I desire. Notice that it will save the video as an MP4 file.
But what if you want to trim a portion in the middle of the video? For this, you drag the white marker to the desired area you want to cut the video (or the area you want the new video to start) and then click on the scissors (cut) icon. As shown below, I’ve made a cut at around 2:27 of the video. I also made a cut around 5:18 which is the end portion I’m looking to trim. Now, I have three video pieces. I only want the video in the middle or video 2 from the diagram below. Now I can delete parts 1 and 3 of the video. All I do is, right-click on each 1 and 3 and select Delete.
Once I Delete part 1 and 3 from the video I’m left with the video I want which is the center portion of the video.
Once you’re done trimming, move the video all the way to the left as well as the white marker so you can play it to make sure you got the part of the video you want before exporting it. NOTE: You are NOT distorting or changing the original file.
Once you click on Export, Clipchamp starts producing your video. You can click on the pencil to give your video a file name. By default, Clipchamp will save your file in the Windows Download folder. And that’s it! You have now trimmed your video!
Although Clipchamp offers a premium version, the free version offers plenty of great features including video editing, screen recordings, text-to-speech, auto caption, and much more.
This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.
You might get a Microsoft Excel spreadsheet in your discovery. Things like bank records, call detail records, cell tower data, or tax documents are often produced in this format, as Excel (.XLSX) or .CSV files. This is good! The benefit to getting data in an Excel file, as opposed to a PDF, is that Excel will let you easily sort and filter data. For example, if you get telephone call logs in Excel format, you can easily sort the calls by date and time. You can filter that information to select specific telephone numbers and review just those calls quickly. This would be difficult to do with a PDF and would take much longer. [https://nlsblog.org/category/software-guides/excel/ ].
This blog post takes this one step further. The pivot tables function in Microsoft Excel lets you rotate and combine data otherwise confined to static columns. This allows you to manipulate existing data in order to see relationships that might not immediately be obvious in the spreadsheet. For example, if you have a spreadsheet of bank records in fraud case, you can use pivot tables to discover how much money is sent by each victim and how much money is transferred to a certain bank, by date. You can also use pivot tables to support more intricate conclusions—for example, that the government had no evidence about a particular defendant’s involvement in a fraud scheme before his arrest and subsequent search. This was a definitive issue in a real case and using pivot tables led to a successful Fourth Amendment challenge. Below we will first set out our fact pattern based on a real timeshare resale fraud scheme. Then we will show you how pivot tables can be used to understand data and answer questions.
Fact Pattern: Timeshare Resale Fraud Scheme
Our client is Ike Kando. He is indicted in a Belizian time share fraud conspiracy, along with over ten (10) codefendants. He is not the first name on the indictment but is further down—his role in the case appears to be that of a money mule. He receives checks made payable to him in the mail, deposits the checks into his own bank account, keeps a cut for himself, and sends the rest via Western Union transfer to a bank account in Belize.
Through viewing discovery, we piece together that the general scheme is this—one of the codefendants finds timeshare owners. He contacts them and offers them a deal too good to be true—that a buyer in Belize wants to buy their timeshare at a price high above market value. But! In order for the timeshare owner to take advantage of this amazing opportunity, they first have to send the Belize buyer a percentage of the purchase price to satisfy local Belize rules. The timeshare owner is told to send the money to one of several money mules used by the fraudster. The money mules deposit the checks into their own bank accounts and then wire the money to a bank controlled by the fraudsters, who really are in Belize. (Figure 1).
Figure 1.
Relevant Timeline:
In August 2002 a fraudster contacts Victim 5 and tells them to send a check to Luke Brownson, one of the money mules. After he sends the check, Victim 5 becomes suspicious and contacts the FBI. DOJ investigates and subpoenas Brownson’s bank records.
In October 2002, Ike Kando is pulled over for a traffic violation. The local police detain him and search his car. They find suspicious bank records and take Ike Kando to the local station, where he is questioned. The next day, Ike Kando is also interviewed by an FBI agent. Law enforcement gets a search warrant for Ike Kando’s house and search it. There they find more documents that implicate him in the timeshare resale fraud scheme.
Because of the bank records and documents in Ike Kando’s car and house the FBI issue more subpoenas for the banking information of other money mules, Brownson and Ted Romanowski. Ike Kando and the other money mules are indicted.
Spreadsheet Introduction:
One piece of discovery we receive from the government is a spreadsheet created by the main case agent. It contains the bank records of the money mules in our case. Each column contains data related to different aspects of each transaction. For example, Row No. 14 tells us that on 8/22/2002, Victim 1, based in in Alabama, sent a check for $12,000 to Ike Kando in Sioux Falls, North Dakota, and Row No. 17 shows that on 8/24/2002, Ike Kando sent a wire transfer for that same amount to the bank account in Belize controlled by the fraudsters:
This spreadsheet, as is, does allow us to filter and analyze some information. We could, for example, find out which transactions are associated with certain money mules by filtering in Column E, the money mule Column and selecting only individual mules, such as Ike Kando. We could also add another filter in Colum C, the victim’s column, and further filter transactions that way. The limitation of only filtering on this spreadsheet is that the data stays tied to the columns and rows they are currently in.
Pivot Table Introduction:
Pivot tables allow you to move data out of its position in the original worksheet and create new tables using existing information. Using a pivot table on a different worksheet, you can select data relevant to your query and arrange it so you can see relationships that might not be immediately obvious on the original worksheet. You can also use other tools, such as slicers, timelines, charts, and dashboards to further filter, manipulate, and view data. The benefits to pivot tables include maintaining an original worksheet, creating and preserving different worksheets that correspond to different questions, and creating visual tools that can be used by other team members (who don’t need to understand the underlying tables themselves). Dashboards and pivot graphs based on filtered, rearranged information can also be used as demonstratives.
Video – Pivot Table Introduction
Questions of interest that cannot easily be answered by the spreadsheet, as is:
The answers to these questions could help us argue our client had a minor role, that their loss amount should be smaller, and that the evidence being used against them was the product of an unlawful search and thus should be suppressed. They are answers not easily answered by the spreadsheet as is, but with the use of pivot tables become clear and simple:
How much money was sent by each victim to the money mules, by date?
How much money was transferred by each money mule to the Bank of Belize, by date?
What did the government know about our client’s involvement in the fraud scheme prior to his arrest?
How much of the information obtained by the government about the fraud scheme was derivative of the subpoenas issued to our client’s bank after the arrest?
How to Answer Question 1: How much money was sent by each victim to the money mules, by date?
If you tried to answer this question only using the filter and sort functions of the existing spreadsheet, you could only calculate a single answer at a time. For example, you could filter by a specific victim, and then show their transactions with each of the money mules, and then sort by date. This might look like this, for money sent by Victim 1 to each of the money mules:
You could then run this kind of filter for each individual victim and save the results—essentially running six (6) different filters.
Or– you could answer this question more efficiently using a pivot table.
To create a pivot table, use the “insert” tab on the main menu, and select the entire table as the source of information. Use the default settings and create a pivot table in a new worksheet. The blank pivot table will look like this:
It is a new sheet in your Excel workbook. The columns from the spreadsheet are now free-floating pivot table fields. These can be rearranged from rows to columns and vice versa by moving the fields between the filters, rows, columns, and values areas.
We are interested in the money mules, so we can select that to go into the column area:
Because we want to see the relationship between the money mules and the victims, we put the victims data into the rows area:
The next piece of data we are interested in is the sum of the deposits related to the victims and money mules, so we drag the deposits column to the values area:
We also want to include date information, so let’s add that to the rows area:
Now, we can answer the question without further filters– “How much money was sent by each victim to the money mules, by date?” You can see, in this existing pivot table, that Victim 1 sent Ike Kando $12,000 in August, Luke Brownson $7,700 and Ted Romanowski $12,500 in September, etc. etc.
You can manipulate this data even further, making it simpler to read, using a type of filter called a “slicer.” A slicer lets you run one or more filters on the existing pivot table. For example, we can create a slicer to filter what we see on the worksheet by victim, money mule, or another field selected for the table.
To add a slicer, we go to the “insert” tab and select “slicer.” When the slicer dialog box pops up we select the victim and mule categories. This will create a mini-window for each category—victim in one and mule in the other:
By default it will show us all the data. We can toggle on and off the option to select individual or multiple fields. Here we de-select victims 1,2, 4, 6 and Blank in the victim slicer, and de-select Romanowski in the money mule slicer:
Now we can see only what we selected—the transactions between Romanowski and Victims 3 and 5, by date. By using the toggle and filter capabilities of the victim and money mule slicer, you can see how you can answer any questions about these relationships as broadly or narrowly as possible.
Pivot tables also allow us to use a timeline slicer to manipulate data by date. Go to the insert tab and select timeline. Select the date option and see how you can filter data by month:
Video – Money Sent by Victims
How to Answer Question 2: How much money was transferred by each money mule to the Bank of Belize, by date?
To answer this question, make a new pivot table worksheet using the data from the primary sheet. Change the information that goes into the pivot fields according to the question—because we are predominately concerned with the money mule data and with money moving to the Bank of Belize, by date, we can put the money mule data and wire transfer location in the rows section and the date data in the columns section. The withdrawals go into the values section.
To isolate the data of wire transfers to Belize, make a slicer for wire transfer location and then selected only Belize, eliminating from view the data with a blank in the wire transfer location field.
We can now easily see, from this very basic pivot table, the answer to our questions. Ike Kando transferred $12,000 to the Bank of Belize on August 24, $14,400 on August 27, etc. etc.
To make the pivot table more comprehensive, add more fields into the report. To track how much money was transferred by each money mule to the bank of Belize and when, and which victim was the source of that money, put the money mule data and months data in the row section, and the victim data in the column section. Make slicers for the money mules and victims and a timeline to filter by date. Insert a pivot chart, leading to a worksheet that looks like this:
A benefit to creating a more comprehensive worksheet with slicers, charts, and pivot tables is that you can then use the pivot tables, with their pre-filtered data, to make dashboards. Dashboards allow you to combine data already isolated in different worksheets, which means you can view pre-filtered data to answer different questions simultaneously.
Video – Transfers by Money Mule
How to Answer Question 3: What did the government know about our client’s involvement in the fraud scheme prior to his arrest?
How to Answer Question 4: How much of the information obtained by the government about the fraud scheme was derivative of the subpoenas issued to our client’s bank after the arrest?
To answer these questions, we will create a dashboard.
Dashboards
To create a dashboard, start with a blank worksheet within the same Excel file. Copy over each chart, the slicers, and timelines from the two worksheets we just created. Since the slicers and timelines are just filters, only one set of the slicers and timelines needs to be copied. It does not matter which of the two worksheets the slicers are copied from. The graphical information in the Dashboard depends on the data that has already been processed using the pivot tables within each worksheet. However, because we have only copied over one set of slicers and filters, we need to make sure that the single set of slicers and timelines are linked to the data in each of the two worksheets. If we don’t link the slicers to both worksheets, only one data set will respond.
To connect the slicers to the other worksheet so that we get responsiveness from both charts, we right click on the slicer and go to “report connections.” We can see here that the data from the other worksheet is referenced but not connected. Check off the other worksheet, so that the slicer is accessing that worksheet as well. When we return to the dashboard all the slicers or filters should be responsive across all the data on the dashboard. Do this for all the slicers and timelines on the new worksheet.
Video – Creating a Dashboard
Once the dashboard is created utilizing elements from the various worksheets, we can run broader queries across all our data. Furthermore, the dashboard can be used by other members of the case team as a reporting tool. Even if they are not familiar with building or manipulating the underlying Pivot Tables, other team members can use dashboards to easily analyze and gain insights into the data. Dashboards can also be useful during team or client meetings, and dashboard screenshots can be used as possible exhibits or demonstratives for motions, trials, or hearings.
Timeline Filters
To answer questions three and four, we will use the two timeline filters. The one entitled “Date,” tracks the dates certain transactions occurred. The one below it entitled ” Date Government Obtained Info,” tracks the dates when the government received information based on subpoenas they issued.
Using the “date” timeline, we can filter by selecting one month, or more than one month by using our shift key. As we do that the visual representation of the data in charts will revise accordingly.
We know from our case timeline that our client was arrested in October of 2002, and we can see by filtering our dashboard for Ike Kando, that he had received money from several victims in August and September and during that same time period he had also wired money to Belize.
However, the question is what did the government know and when did it obtain that information? For insight we can use our “Date Government Obtained Info” timeline. When we filter for the month of July, we see that the government had obtained no information about any aspect of the fraud scheme in July. When we filter for August we still get nothing on our dashboard. We know that the government received a tip from one of the victims in August, however, the dashboard is still blank for that month because they received no banking information based that tip off until September. When we filter for the month of September, we see that is when they received Luke Brownson’s bank records with information about his involvement in the scheme. However, they still had no information about our client, Ike Kando. It is only when we get to October that we see that the government has obtained information about Ike Kando’s involvement in the scheme.
We can therefore assert that the government did not know about our client’s involvement in the fraud scheme before the traffic stop in October and that all information the government obtained about our client’s involvement in the scheme was derivative of the documents found during the illegal traffic stop. We had a client involved in a similar fact pattern as the case we used in this hypothetical. In that case, because we could show that the case against our client was all derivative from the illegal traffic stop’s poisonous tree fruits, we ended up with a favorable negotiated agreement for our client.
Video – Using the Dashboard to Answer Questions 3 & 4
Conclusion
If you have made it through this entire blog post and the videos and run into trouble replicating our results or with your own pivot tables and dashboards, please contact us! Excel is a powerful tool but it is also easy to make a misstep that can cause your worksheets to not work as you intend. We have tried with this post and videos to give starting instructions paired with an actual case scenario, but we did not include every single step and decision here. We are happy to look at your work with you and troubleshoot, and hope that you find pivot tables as helpful as we do.
This is a brief guide on the text recognition feature in Adobe Acrobat1. OCR, which stands for Optical Character Recognition – is a process which adds an invisible text layer to scanned paper documents or screenshots to help make them text searchable. While OCR can be very helpful in terms of search, it is not perfect. The computer is interpreting pictures of letters and characters in documents and attempting to turn them into text. Sometimes, those translations are incorrect (Figures 1 and 2).
Figure 1.Figure 2.
The quality of the OCR text depends on many factors including the accuracy of the source document, its complexity and structure, font and language variations and the sharpness of the scan. For example, a document with clear, large print font (Figure 3) will generally OCR better than a fax copy with blurry text or handwriting (Figure 4).
Figure 3.Figure 4.
With newer iterations of Adobe Acrobat, the OCR text accuracy has improved. When working with sets of scanned paper documents that were processed with older OCR engines, some people will spot check the accuracy of the OCR by running simple searches. Time permitting, they may then choose to re-OCR the documents. This can lead to more accurate searchable text.
A good practice for dealing with scanned paper PDF documents we want to work with is to first make a copy of the documents. For example, if we received a flash drive or a download from USAfx of scanned paper PDF files it’s a good idea to first copy the files to a location on a computer or a network drive. This way we can work with the documents and add OCR text when needed, while still maintaining a set of the original files.
With a copy of one documents open, the next step would be to see if document already is already searchable. When we open a PDF file, we are looking at an image of the document. Since the OCR text layer is invisible, we will not know whether it is searchable just by looking at it. There are a few things we can do to see if OCR is present.
If we go to the ‘Edit’ menu and choose ‘Select All’, or the keyboard shortcut ‘Control A’, (Figure 5) and we get a no text characters warning message (Figure 6), this indicates that there is no searchable text. Alternatively, if we use our mouse and single click in a blank area of the page, if the entire page turns blue, it also means there is no searchable text layer.
Figure 5.Figure 6.
We can also try to find a word on the page using one of the search features in Acrobat. For example, when we run a find for the word ‘memo’ we get the same no text characters warning that we got by going to the Edit menu and choosing ‘select all. (Figure 7).
Figure 7.
Starting the Text Recognition Process
To add an OCR text layer to a document, go to the tools menu and click on the ‘Scan & OCR’ button (Figure 8). When you activate this tool in Acrobat an additional menu bar will appear at the top of the page. Choose the ‘In This File’ option (Figure 9). In most circumstances we will go with the ‘All pages’ default. Click on the blue ‘Recognize Text’ button to begin the process (Figure 10). A progress indicator will appear on the bottom of the bottom right-hand side as it processes each page Adobe will also automatically rotate pages, based on the optimal rotation for the text on that page (Figure 11).
Figure 8.Figure 9.Figure 10.Figure 11.
While the speed at which Acrobat can OCR documents can vary depending on the complexity of the documents and the type of computer being used, a good general estimate is about 1000 pages per hour. With particularly large OCR jobs, you might want to wait until the end of the day to begin the process. Some offices have also set up a spare computer, dedicated to running various processes such as OCR, so nobody’s computer is tied up.
When the Text Recognition Process is Complete
When the OCR process is complete, we can now go back to the first page to make sure the document is now searchable. If we go back to the ‘Edit’ menu and choose ‘Select All’, the text on the document will now be highlighted in blue while the blank areas surrounding the text will remain white. (Figure 12). A single click in the blank area no longer turns whole page blue. If we search for the word ‘Memo’ again, using the find option, we will get a set of search results with the first hit on the first page of the document highlighted in blue (Figure 13).
Figure 12.Figure 13.
Since we have now changed the document by adding an OCR layer to it, save the file so we lose none of the work we have just done.
Text Recognition in Multiple Files
We can also run the OCR process across multiple documents, by going to our OCR tool menu (Figure 14) and selecting ‘Or recognize text in multiple files’ (Figure 15). This is a handy option, as we often receive batches of documents that might need to be OCR’d.
Figure 14.Figure 15.
You can choose to OCR an entire set of PDF files in a folder by selecting ‘Add Folder’ (Figure 16) and then navigating to where that folder is on your computer or on the server. By default, Acrobat will include all PDFs and subfolders within the selected folder (Figure 17).
Figure 16.Figure 17.
When running the OCR process on multiple files, we are prompted to choose an option as to where to save the files before you run the OCR. Most users choose to save the files in the same folder selected at the start with the original file names (Figure 18). Acrobat will also launch a progress bar for this process (Figure 19).
Figure 18.Figure 19.
Estimate the page volume and run the process at a break or at the end of the day, if it is a large amount of information. The Acrobat help guide (https://helpx.adobe.com/acrobat/user-guide.html ) is a great resource if you are interested in discovering more about the OCR process.
The free Adobe Acrobat Reader software does not include the ability to OCR documents. ↩︎
This is a brief guide on the fundamentals of searching PDFs using Adobe Acrobat Pro. We will review how to run searches within a single PDF and across multiple PDFs. Searches can even be run on an entire folder of documents such as one that contains all the discovery you receive in a case. Searching in Acrobat will be useful only if the PDF files have searchable text. For scanned paper, you must make sure that they have been OCR’d first. OCR stands for ‘Optical Character Recognition’, and it is a process that reads pictures and turns them in letters and words so that they can be searched.
You can search not only the text of a document, but also any Adobe comments and bookmarks made on it. Searches can be run using either the “Find” or “Advanced Search” options. The way in which search results are displayed and what additional features may be available depend on the search tool chosen. The ‘Find’ tool can perform a quick search. With a PDF file open, display the ‘Find’ toolbar by choosing ‘Find’ from the main menu (Figure 1). There is also a ‘Find’ toolbar in the upper right-hand corner of the document which can be activated by clicking on the magnifying glass icon or by pressing ‘Control+F’ on your keyboard (Figure 2).
Figure 1.Figure 2.
To perform a find, type a search term – for example, the term ‘Memo’. Acrobat will provide a preview of the number of hits for that word in document (Figure 3). After you hit enter on your keyboard, the search results will be shown highlighted in blue (Figure 4). If the term appears multiple times within a document, we can use the ‘Next’ and ‘Previous’ buttons to move from hit to hit. As we navigate through the search results, note that Acrobat highlights not only the word ‘Memo’ each time it appears, but also highlights any word which includes the letters ‘M-E-M-O’, such as ‘memoranda’ and ‘memorandum’.
Figure 3.Figure 4.
There is a drop-down button next to the search term where we can select ‘Whole Words Only’, ‘Case Sensitive’, or choose to include bookmarks or comments (Figure 5). We will go over these features in greater detail when we look at the ‘Advanced Search’ tool below.
Figure 5.
If we run a new find for the term ‘Xanadu’, a message appears letting us know that no hits were found ‘No results found’ (Figure 6). We will only get this message if the document is searchable and the word appears nowhere in the document. But, if we get a scanned alert page message, Acrobat is letting us know there is no searchable text associated with the document. For you to run your search, you will first have to OCR the document. If you need guidance on how to OCR the document, refer to the Acrobat OCR tutorial on this website.
Figure 6.
Searching Comments
To search through just the comments, we can use the find tool in the comments list menu. To access this feature, select the ‘Comments’ icon in the upper right corner of the document (Figure 7). In this particular example we have 48 comments (Figure 8). Entering the term memo in the ‘Search’ box filters the list down to 4 comments and highlights the results (Figure 9). You can also sort and filter the list based on certain criteria such as comment author and comment type. To access this feature, click on the ellipses icon to the right of the search tool. (Figure 10). This opens an ‘Options’ menu. The ‘sort comments’ menu is the first level down is (Figure 11).
Figure 7.Figure 8.Figure 9.Figure 10.Figure 11.
Advanced Search
While the find tool is quick, easy, and useful, the ‘Advanced Search’ tool has more features and is the preferred means of searching by many people. To open the advanced search window, select ‘Advanced Search’ from the main menu, or use the ‘Shift+Control+F’ keyboard shortcut (Figure 12). Acrobat will launch a new ‘Advanced Search’ dialog box (Figure 13). To automatically adjust the sizing of this window to fit nicely alongside the one showing your document click on the ‘Arrange Windows’ button.
Figure 12.Figure 13.
When we run an Advanced Search for the term ‘Memo’, Acrobat generates a list of the results with some context. We can navigate between the results by clicking on them. Acrobat will go to the page and highlight the result in blue (Figure 14).
Figure 14.
If we click on ‘New Search’ we can now either re-run the ‘Memo’ search or type in a new search term. This time, before we click the search button, let’s consider some of the additional features that we saw earlier when we used the find tool (see Figure 5 above). The ‘Whole Words Only’ and ‘Case Sensitive’ tools will limit the search based on the criteria selected (Figure 15).
Figure 15.
When we re-run the search now, with these options checked, we will only get ‘Memo’ with a capital ‘M’ because we typed in the term in the search box with that exact capitalization. Other words containing the letters ‘Memo’ like ‘Memorandum’ are excluded because we also limited our search to only the whole word, ‘Memo’ and not any words containing those letters (Figure 16).
Figure 16.
While ‘Case Sensitive’ and ‘Whole words only’ will restrict the search, ‘Include Bookmarks’ and ‘Include Comments’ will expand it. When we run the ‘Memo’ search again with these options selected, any comments or bookmarks with the term will be included in the list of results (Figure 17). We can tell if the result is a comment or a bookmark by looking at the icon next to it. Just like with text results, we can navigate to a bookmark or comment result by clicking on it (Figure 18). These features can be used in various combinations to further define your search.
Figure 17.Figure 18.
If we click on ‘New Search’ again, options we previously selected are still marked (Figure 19). Acrobat retains the selections you make in the ‘Advanced Search’ window until you change them. Review these options before each search, as the number and type of search results can vary greatly depending on what options are marked.
Figure 19.
Advanced Search – Multiple PDFs
Advanced search also enables you to look for a search term in multiple PDFs. This is helpful as often we receive multiple files we want to search through. Without this option, each file would need to be opened and searched separately. From the ‘Advanced Search’ window, select ‘All PDF Documents in’ (Figure 20) and then use the down arrow to choose a location. There are ‘Desktop’, ‘My Documents’ options and drive letter options, as well as a ‘Browse for Location’ option. We recommend that you choose the ‘Browse for Location’ option and navigate to a specific folder. This option will include the PDF files within the folder and any subfolders in that location (Figure 21).
Figure 20.Figure 21.
Let’s choose a location where we have our discovery materials and run a search for ‘Memo’ again (Figure 22). The results appear in page order, nested under the name of each document (Figure 23). You can expand or collapse the list of results in a specific file by alternately clicking on the small arrow next to each result. As before, each search result includes some context (Figure 24).
Figure 22.Figure 23.Figure 24.
This time, when we click on a hit on the results list Acrobat will open the document, showing exactly where the search term appears (Figure 25).
Figure 25.
Next to the ‘New Search’ button at the top, there is a ‘Save results to file’ button (Figure 26). Selecting this button allows us to create a report in either PDF or CSV format. We recommend saving the results to PDF as this creates a nice summary of the results with links to the documents (Figure 27).
We are constantly on the lookout for new tools to keep in our litigation support toolbox and Shutter Encoder is quickly becoming one of our new favorites. It’s a publicly available (a.k.a. “free”) media converter program that is based on standards and functions within the open-source FFmpeg software project. Currently there are both PC and Mac versions available for download from their website: https://www.shutterencoder.com/en/.
Here are a few of our favorite things we like to do with Shutter Encoder:
Convert file formats
Media files come in lots of different formats (ex: .asf, .avi, .mpg, .mov, .wmv, .amr, .m4a, .wav, .wma, etc…). It can sometimes be a struggle to consistently get them all to play. Some files are only compatible with specific programs, operating systems, and devices while others may also require that special “codec” packages be installed. Shutter Encoder can be used to convert media files using “standard” output codecs that work with a wide variety of environments.
For most video files the output codec standard we recommend is an MP4 file using the H.264 standard. This format is compatible with many modern devices, operating systems and video playback applications.
For example, we might have some .ASF video files that play in VLC Media Player, but will not work in Trial Director. When we add these files to Shutter Encoder, choose the H.264 output codec, then select the “Start Function” button, we produce new MP4 copies of the files that will play in both programs.
With audio files, we like to use the MP3 standard when converting as it works seamlessly with most media players. When choosing an MP3 output, you can further select from a list of different audio “bitrates” (in kb/s), which will affect the quality and file size. In most situations, we choose the 128 bitrate as that offers a nice balance between the two.
Cut, Remove and Split
There are many times when only a portion of a media file is needed. To produce a clip, or remove an unwanted section, Shutter Encoder includes Cut, Remove and Split options. We can access these features from a playback screen when converting file formats (ex: after adding a video file and choosing the H.264 Output codec).
The playback screen includes a timeline band (with a waveform graphic overlay), a play control button set, and a Mode selector. To select a portion of the media being converted, click and drag from the left and/or right sides of the timeline band. Alternatively, there are Begin and End buttons within the playback button set that can be used. The Mode dropdown in the lower right includes the following options:
Cut converts the selected portion of the media (and ignores unselected parts).
Remove takes out the selected portion (and converts unselected parts).
Split (less commonly used) divides media into separate files based on a user defined number of seconds.
We can preview a Cut or Remove section in a separate window by using the “Preview” button located to the right of the Mode dropdown. The actual Cut, Remove or Split will begin once the “Start Function” button is pressed and will only affect the output file (not the original).
Reduce video file size
Larger video files can often be difficult to work with. They can take a long time to open, use up valuable drive space, and prove challenging to share with others.
In addition to the Cut, Remove and Split features listed above, there are “bitrate” adjustments that can be made to video files that affect the size of the output file during conversion. Bitrate settings affect things like the video smoothness and sound clarity. Though the default options work well in most scenarios, it is possible to adjust these settings to create a smaller sized output file. Generally speaking, the following settings allow for a more compact output file size without any noticeable quality losses:
Video bitrate: 2500 kb/s
Audio bitrate: 128 kb/s
Adjust video rotation
If certain videos (especially those recorded on mobile devices) are not playing back in the correct orientation, we can re-orient them within Shutter by 90 degree increments. When the conversion function is chosen, Image Rotation options appear above the bitrate adjustments on the right panel. The preview window shows how the new orientation will appear in the output file when adjusting these settings.
Download web video(s)
Another useful function within Shutter is Download Web Video. This will attempt to download available videos from specified webpage addresses (URLs). Though it may not work with all videos on all webpages (due to page design or video protection protocols), it seems to work well with many common sources including Youtube, Vimeo and FaceBook video posts. We can copy and paste one or more URLs and the program will attempt to download the videos to the desktop. We can even point it to a Youtube channel page and to attempt to download multiple videos (if there are a lot of videos on the channel, be mindful that it might take a long time and a lot of hard drive space).
Below is a brief video demonstrating some of the functions mentioned in this post:
This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.
CJA panel attorneys and federal defenders frequently receive some of their discovery in spreadsheet or Excel format. Call detail records and indices listing information regarding discovery productions (often called document indexes) are two examples. Having the files in Excel format instead of PDF is advantageous as spreadsheets are designed to sort and filter information, either by a single or multiple criteria. With voluminous information, this ability to sort and filter by multiple criteria can speed up review and allow you to identify the information you are interested in. For example, if you have telephone call records in Excel format, it is easy to filter by a number of phone numbers and quickly narrow the entries to review with several clicks. If the same information is in PDF format, it would have to be done manual and take much, much more time.
For those experienced with Excel, it is a welcome sight to see the data in Excel format. However, for the neophyte Excel user who only reviews PDF files, it can be frustrating to navigate Excel and review the data. This post provides quick and easy formatting options that are available within Excel that can save you review time.
Let’s look at a mock discovery document index as an example. Often the document index starts as a simple list of files with some basic information. As these lists become longer and include more document details (frequently in the form of additional columns), they can be hard to read and work with unless formats are applied. The video below demonstrates quick and simple options within Excel that can help to transform a basic list into a better looking, more functional table that include easy to use sort and filter features.
Video:
Some of the topics covered in the video above are:
Column Width It often helps to be able to adjust the width of columns to better fit important information on your screen. To adjust the width of a column:
Move the cursor in between the column headers until it becomes a black line with two arrows.
To manually adjust the width of a column left-click then drag the black line to the right or left.
To automatically adjust a column, double left click and the column width will become as wide as the longest text entry in that column.
Multiple columns can be adjusted at the same time by selecting them before making a manual or automatic adjustment.
Wrap Text Automatically resizing row heights helps to make the words within longer text cells visible. Select the cells, rows or columns to be adjusted then choose the “Wrap Text” button from the Home menu.
Cell Alignment Adjusting cell alignments can sometime help make items more uniform and easier to view. By default, Microsoft Excel aligns numbers to the “Bottom-Right” of cells and text to the “Bottom-Left”. A common adjustment is to change cells that are “Bottom” aligned to become “Top” aligned, as that is generally easier to read. To do this: select the cells, rows or columns to be adjusted then choose the “Top Align” button from the Home menu. “Right” aligned cells can be adjusted to “Left” alignment through a similar process.
Freeze Panes Selecting certain columns and rows to always be visible greatly increases the readability of longer lists. To freeze panes:
Left click on the cell to the right of, and below the rows and columns you wish to always be visible.
From the View menu, click on the “Freeze Panes” button and select the “Freeze Panes” option.
Data Filter Data filtering is a powerful formatting option. It unlocks the ability to easily sort, filter and search within columns. To turn on filtering:
Select all of the data including any column names.
From the Data menu, select the “Filter” button.
Once data filtering has been enabled, items can be sorted, searched and filtered on by choosing the filter button from the columns.
Format as Table Alternatively, data can be filtered by selecting a “Format as Table” style. “Table” styles are a quick way to make the data visually pleasing and they automatically include the data filtering feature. To turn a list into a “Table” style:
Select all of data including any column names.
From the Home menu, click on the “Format as Table” button and select your desired style (I like the “Medium” styles personally).
From the “Create Table” dialog box select the “My Table has headers” option then click the “OK” button.
Note: Be aware that these format changes are modifications to the original Excel file. If preserving an original copy of the file is important make sure to choose the “Save As” option when saving changes.
Preparing exhibits for trial or court hearings, though not glamorous, is an essential task in the practice of courtroom litigation. Depending on the volume and type of exhibits, this necessary task can quickly turn tedious if you must print each exhibit, affix a physical sticker, fill out the exhibit and case information by hand, then scan and submit the stickered exhibit. In the heat of trial where last minute changes take place frequently, it is easy to make mistakes. However, with the right type of technology, such as Adobe Acrobat Pro (or Standard), this process can be done more smoothly, help reduce opportunities for making errors, and done more quickly than the old school method of stickers and paper If you have Adobe Acrobat*, we suggest considering using digital (electronic) exhibit stickers for your next case.
*Acrobat Standard or Pro, not the free “Reader” version.
This post will walk you through how you can create digital exhibits on your own, including the process of installing a sticker that takes the form of a custom Acrobat stamp. The stamp will allow you to quickly fill in the exhibit and case numbers for your case, and will automatically remember your previous entries the next time you use it.
First, follow the instructions below to install the electronic exhibit sticker.
Installation
Download and copy the exhibit_stickers.pdf file to a location that is easily accessible, such as your Desktop. (NOTE: You can delete this PDF file once we are finished with the installation.)
Open Acrobat and press CTRL-K to open the Preferences menu. Scroll down on the left to “Security (Enhanced)”. Click the “Add File” button, which will open a file explorer window.
Type %appdata% into the address bar and press enter.
This will open a new folder. Open the “Adobe” folder, then the “Acrobat” folder. You may see folders for the different versions that have been installed like a “2017”, “2020” or a “DC” folder. Open the “DC” folder if you have that, or else the highest folder year you have. Open the “Stamps” folder. Find the “exhibit_stamps.pdf” file you saved and drag or copy and paste it into the Stamps folder. Select the file and click “Open.”
This will take you back to the Preferences screen. Verify that exhibit_stamps.pdf is listed inside the box. If the file is there, click “OK”. Then close out of all Acrobat windows.
Usage
Open the PDF that needs an exhibit sticker. Select the “Comment” tool from the list along the right side of the screen.
This will open a new toolbar. Click on the Stamp tool icon, navigate to the “Exhibit Sticker” menu, then click on the Exhibit sticker image.
The first time you use the sticker, it will pop up this window. Check “Don’t show again” and click “Complete.” There is no need to enter any information.
Your cursor will now become a floating exhibit sticker. Click where you would like to place the sticker. Do not worry if the initial placement is not perfect; you can move the sticker to a different part of the page and even resize the sticker after you have placed it.
When you click to place the stamp, a window will pop up asking you to enter an Exhibit Number. Enter the Exhibit number in the box and press OK.
Next, a window will pop up asking you for a Case number. Enter the Case number and press OK.
This will place an exhibit sticker on your PDF that contains the Exhibit Number and Case Number. You can move and resize the sticker if needed. If you need remove or change any of the information on the sticker, you can right click on the sticker, select “Delete” and create a new sticker.
To permanently affix the sticker to the document, you will need to print the document to a new PDF. Go to the File menu and select Print. Now change your printer to “Adobe PDF”, change the “Comments & Forms” selection to “Document and Stamps”, then press print and save your new copy to the location of your choosing.
That’s it. You will now have a permanently stamped PDF document. The next time you want to stamp a document, Acrobat will pre-fill your last enter Exhibit Number and Case Number, so it will be easier to keep track of your exhibits if you are marking multiple documents in one sitting, and you will not have to re-enter the case number each time.
If you need any assistance with installation, you can contact me at carl_adams@fd.org.
This post is part of an ongoing series of videos on how Microsoft Excel can help CJA practitioners (including attorneys, paralegals, investigators, and mitigation specialists) in their CJA cases.
Today’s Post: Filename Lists
When working with discovery, investigative documents, or other case-related materials, it is often helpful to have a list of filenames in an Excel table.
There are times when the government produces to defense counsel digital files where the name of the file indicates something about the file content without a user having to open each file individually.
For example, the government may produce a list of investigative reports in PDF format which, as part of the file name, has the date of the report, the type of report (e.g. FBI 302) and the author. In those instances, it can be beneficial to create a spreadsheet of the filenames and information about the files for later review and organization. Even in instances where the filename is only the Bates number of the file, it can be useful to have a spreadsheet of those numbers.
Microsoft Excel is a useful tool for generating such a filename list. When properly setup, Excel allows users to sort, filter and search for specific files based on different criteria. Fields can be created and associated such as comments, document type, review status, dates and related issues. Additionally, hyperlinks to a specific file or folder can be created for quick and easy access to an item. We will examine these functions in greater detail in future videos.
This video will demonstrate how lists can quickly be created and recommendations to follow when setting up a file list. The video looks at three methods for creating filename lists:
Method 1: Creating a query table by running the “Get Folder Data” process that is currently available in the newer “Office 365” version of Excel.
Method 2: Using the “Copy Path” process available in Windows File Explorer.
Method 3: Using a “File List Program” specifically designed for creating a list of files in Excel format (ex: Directory List and Print).
PDFs (portable document format files) are a common file format in federal criminal discovery. But are all PDFs created equal? As you all have experienced, the answer is no, they are not.
Think about PDFs in three distinct categories:
True PDFs;
Image-based PDFs; and
Made-searchable PDFs.
For discovery review, these distinctions are important because it impacts whether the PDF is searchable and the accuracy of your text searches within the PDF file. With voluminous discovery, the ability to search and review PDFs is critical for organizing and reviewing it.
True PDFs (also known as text-based or digitally created PDFs). These PDFs are created using software such as Microsoft Word, Excel, or using the “print to PDF” function in those programs. They consist of both text and images. We should think about these PDFs having two layers – one layer is the image and a second layer is the text. The image layer shows what the document will look like if it is printed to paper. The text layer is searchable text that is carried over from the original Word file into the new PDF file (the technical term for this layer is “extracted text”). There is no need to make it searchable and the new PDF will have the same text as the original Word file. An example of True PDFs that federal defenders and CJA panel attorneys will be familiar with are the pleadings filed in CM/ECF. The pleading is originally created in Word, but then the attorney either saves it as PDF or prints to PDF and they file that PDF document with the court. Using either process, there is now a PDF file created with an image layer plus text layer. In terms of usability, this is the best type of PDF to receive in discovery as it will have the closest to text searchability of the original file. Click here to see an example of a True PDF.
Image-based PDFs (also known as image-only PDFs). Image-based PDFs are typically created through scanning paper in a copier, taking photographs or taking screenshots. To a computer, they are images. Though we humans can see text in the image, the file only consists of the image layer but not the searchable text layer that True PDFs contain. As a result, we cannot use a computer to search the text we see in the image as that text layer is missing. There are times when discovery is produced, it will be in an image-based PDF format. When you come across image-based PDFs, ask the U.S. Attorney’s Office in what format was that file originally. Second, ask if they have it in a searchable format and specifically if they have it in a digitally created, True, Text-based PDF format. They may not, as they often receive PDFs from other sources before they provide them to you, but you will want to know what is the format in which they have it in, and what is the original format of the file (as far as they know). Click here to see an example of an Image-based PDF.
Made-searchable PDFs (also known as “OCRed” PDFs). Image-based PDFs can be made text searchable by applying optical character recognition (OCR). CJA panel attorneys frequently use Adobe Acrobat Pro (or other PDF editor software) to make image-based PDFs searchable. During the OCR process, the software program interprets each character on the image as text and adds a text layer to the image layer. Made-searchable PDFs are like True PDFs, but the searchability of the OCRed document will depend on the quality of the image, or the recognizability of the writing. They are often not 100% accurate when you do keyword searches of the text. Click here to see an example of a Made-searchable PDF.
The ESI Protocol (formally known as the Recommendations for Electronically Stored Information (ESI) Discovery Production in Federal Criminal Cases) noted the limitations of OCR process on scanned paper.
“Generally speaking, OCR does not handle handwritten text or text in graphics well. OCR conversion rates can range from 50 to 98% accuracy depending on the underlying document. A full page of text is estimated to contain 2,000 characters, so OCR software with even 90% accuracy would create a page of text with approximately 200 errors.”
People ask how accurate software programs are in the OCR conversion. That is important, but the biggest factor for how searchable your OCR PDF will become is the underlying quality of the scanned image. A clean copy of a pleading will have high accuracy; a twice photocopied school paper record from the 1950s will be less accurate.
A quick way to see what the quality of the text is compared to the image is to select the text in question in a PDF file (you can use Control + A in Windows or Command + A in Mac to copy all the text on a page), and then copy and paste the text into a Word document. Put the two files side by side and visually compare them.
Whether it is on media (CD Rom, USB drive, or hard drive) or through the internet (email or USAfx) it is becoming common practice that discovery files will be “encrypted.” Encryption adds a layer of protection by scrambling the data, so files cannot be seen unless a digital “key” (password) is provided. The goal is to protect the data while it is being shipped in case it is lost or stolen.
“Decryption” is the process of unscrambling an encrypted file so it is readable. The first step you should take when you receive encrypted files is to create a decrypted copy of the files. The decrypted copies will allow you to search, review and work with them on your computer that the encrypted files will not, and you will not need to enter a password each time to open them.
When receiving encrypted case related materials:
Look for cover letters and associated correspondence that mention password protection or encryption. Often the sender will tell you that the files are encrypted and provide instructions on how to obtain the key (password). If the media contains encrypted files you cannot work with them unless you have that password.
Use a Windows computer. Most decryption programs included on the media are designed to work with Windows computers. Sometimes decryption can be done on Mac computers, but often it requires additional software not included with the media.
Insert the media and look for either a “password” prompt or a decryption program. Certain encryption programs (like Microsoft “Bitlocker“) will automatically prompt for a password when the media is inserted. Other times the media will include Windows-based software programs that needs to be run.
Create decrypted copies of the files. When you open a file that is encrypted a computer will typically only temporarily decrypt it. The file may be in a “read-only” mode that will not work well with most software programs and will continue to need a password when reopening. Making a decrypted copy of the file will allow it to be correctly recognized by the programs on your computer and will no longer need a password when opening the copy.
McAfee Removable Media Protection
“McAfee Removable Media Protection” is a common encryption program used by the USA’s when delivering discovery on thumb drives and CD/DVD discs. The media usually includes an executable file that when run will allow users to make decrypted copies of the files. To create decrypted copies:
Create a destination. Open File Explorer (the file browser on your computer) and navigate to a destination on your computer (or external drive) with enough room to hold a copy of the files. Create a folder that will keep the decrypted copy of the files.
Open McAfee. Insert the media and look for a McAfee program executable file (the file is usually called “MfeEERM” and will have the “.exe” extension).
Run the executable and look for a dialog window prompting for a password. Enter the password and click “OK”.
Copy the files or folders. From within McAfee:
Select the “Top Level” folder from the left-hand navigation pane.
From the main window (on the right side), select all the files and folders listed, right-click on them and choose “Copy”.
Paste the copies into the destination. Switch back to File Explorer. Right-click on an empty space within the destination location and choose “Paste”. For larger sets of data (over 10,000 files/folders), try dividing the copy process into smaller batches of about 1,000 files / folders each. Verify the copied files can be opened by closing McAfee and opening a few of the copied files.
Here is a quick video demonstration of the process:
