Microsoft Excel Tips & Tricks for CJA Cases: Filename Lists

By Alex Roberts

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).

Dealing with Encrypted Discovery

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:

  1. 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.
  2. 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.
  3. 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.
  4. 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).McAfee
  • Run the executable and look for a dialog window prompting for a password.  Enter the password and click “OK”.
    Password
  • Copy the files or folders. From within McAfee:
    1. Select the “Top Level” folder from the left-hand navigation pane.
    2. From the main window (on the right side), select all the files and folders listed, right-click on them and choose “Copy”.
      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:

Acrobat download“Encrypted Discovery” PDF file download

dtSearch User Preferences

When you first open dtSearch the window layout and user preferences will be using the programs default settings.  We’ve found that modifying certain settings will increase the search capabilities and will make navigating and working with the program easier.  The system will remember your preferences so you only have to modify these settings once.

By default, the program is set to search document content, but not file or folder names and there are times when searching file and folder names can be helpful.  Additionally, the search results screen uses a top-bottom layout (the list of results will be on the top with a document preview on the bottom).  Since most documents have a portrait orientation, a side-by-side layout is generally easier to work with.  With Adobe Acrobat documents, there is an additional plug-in needed to be able to navigate through search results within the same document.

To change the user preferences, go to the “Options” menu and choose “Preferences”.

image1

In the Preferences window, under “Indexing Options” place a check next to “Index filenames as text” (leave “Include path information” checked as well).

image2

 

Next, go to “Search results” within the “Search Options” section and place a check next to “Checkbox” and “Type” within the “Items to include in search results” section.  Then under the “Window layout” section, select “Vertical split”.

image3

Finally, select “PDF view options” in the “Document Options” section.  Look in the “Highlighting hits in Adobe Reader” area.  If the screen reads “A plug-in is needed…” then select the “Configure Plug-in” button and follow the screen prompts to install (if the screen reads the plug-in is installed then there is nothing more you need to do).

image4

Once you have made the changes, click “OK”.  You will receive a message notifying you that the new window layout won’t appear until you close and restart dtSearch.

To see the changes, close dtSearch and re-open it.  You will see the window layout is in the side-by-side “Vertical split” view.  When you run a search, your search results will now appear on the left, with checkboxes and the document viewer on the right.  Within PDF documents you will now be able to use the hit navigation buttons.

image9

Going forward, any new indexes you create will include the ability to search file and folder names.  If you wish to add this feature to any of your existing indexes, run “Update Index” from the “Index” menu.

For additional help with dtSearch, please use the “Help” menu or visit dtSearch.com.

.

 

 

dtSearch Desktop Demonstration Video

dtSearch is a popular search and retrieval program. Here is a brief 12 minute video that demonstrates how to setup a new dtSearch index and how to run searches within an index.

As mentioned in the dtSearch Desktop post, we have been able to obtain a limited number of licenses that will be made available to CJA panel attorneys with current, active cases.  To request a license go to the dtSearch Desktop post and fill out the request form on the bottom.

Note: like most litigation software programs, this program was developed for Windows-based operating systems and does not work with Macintosh operating systems.

 

dtSearch Desktop

Limited licenses of dtSearch Desktop Available for CJA Panel Attorneys

We are pleased to announce that we are able to offer a limited number of dtSearch Desktop software licenses for CJA panel attorneys with current, active cases at no cost (a $200 value). 

*PLEASE NOTE:  dtSearch only works on Windows-based operating systems. It will not work on Mac computers unless you are running a virtual Windows operating system.*

dtSearch is a popular search and retrieval program that can be a useful tool for searching discovery and creating brief banks.  It can be helpful in viewing different file types (including non-PDF files) even if you do not have the associated program installed on your computer. dtSearch is the search engine utilized in many familiar litigation support programs including Adobe Acrobat Pro, CaseMap and Forensic Tool Kit (FTK, a computer forensic tool).

dtSearch allows users to search native files as well as scanned paper documents that are in a text searchable format. It generates a search index for each folder of materials the user designates and allows one to search the contents of an entire folder regardless of the amount of data or the number of varied file formats. As electronic discovery in federal criminal matters continues to grow in volume and in the variety of formats. dtSearch is a great resource for CJA panel attorneys faced with the daunting task of organizing and searching through their case material.

To obtain your free license of dtSearch, simply fill out the dtSearch Request Form below. Once you complete all of the fields, click on the “submit” button at the bottom of the form. This will automatically send an email with your completed form attached to Kalei Achiu, the National Litigation Support Paralegal in charge of dtSearch licensing. You will then receive an email within 5 business days with download instructions and an activation code. Each user license can be installed for that user on two machines.

You must have an active appointed case to utilize this license.  If you are no longer on the panel and don’t have an active appointed case, we request you return the license to the National Litigation Support Team (NLST) by contacting Kalei Achiu so the license can be used by other CJA panel attorneys. 

For technical support or if you have any questions regarding the utilization of dtSearch within your office, please contact either Alex Roberts or Kalei Achiu.  If you want to learn more about dtSearch, you can go to dtSearch.com.

dtSearch Desktop Request Form:

Adobe Acrobat Training Videos: Searching Fundamentals

Previous video – Text Recognition

Adobe Acrobat Pro is one of the most popular computer software programs on the market for FDO and CJA panel attorneys.  Since so much of the discovery we currently receive in criminal cases is provided in paper or scanned paper format, Acrobat Pro is an excellent tool to help you to better organize and review it.

In our team’s continued efforts to providing resource to CJA panel attorneys and FDO staff, we are creating a series of training videos. Each short video will address a specific feature in a computer software program with our first set focused on Adobe Acrobat Pro XI.

Future videos we are developing will also be posted on this blog.  Make sure to check back in or sign up to subscribe to our blog to get notices of new posts by email.

These videos do not take the place of hands-on training sessions where we can get in depth about a variety of software programs and legal strategies for addressing complex cases, but it hopefully will provide you some basic background information that can help you in your cases.

Adobe Acrobat Training Videos: Text Recognition

Next Video – Searching Fundamentals

Adobe Acrobat Pro is one of the most popular computer software programs on the market for FDO and CJA panel attorneys.  Since so much of the discovery we currently receive in criminal cases is provided in paper or scanned paper format, Acrobat Pro is an excellent tool to help you to better organize and review it.

In our team’s continued efforts to providing resource to CJA panel attorneys and FDO staff, we are creating a series of training videos. Each short video will address a specific feature in a computer software program with our first set focused on Adobe Acrobat Pro XI.

These videos do not take the place of hands-on training sessions where we can get in depth about a variety of software programs and legal strategies for addressing complex cases, but it hopefully will provide you some basic background information that can help you in your cases.

The first video (created by Kelly Scribner and Alex Roberts) gives key information to consider when using OCR text recognition with Adobe Acrobat Pro for scanned paper. Though much has been written about the incredible functionality available with Adobe Acrobat Pro, this short seven minute demonstration focuses on points that we think are most important for you to consider when using OCR in Acrobat Pro.

Future videos we are developing will also be posted on this blog.  Make sure to check back in or sign up to subscribe to our blog to get notices of new posts by email.

.