Productions received are enigmas, cloaked in mystery, and smothered in secret sauce. But we can reveal metadata insights for eDiscovery with ChatGPT.

Where Do You Start with Productions Received?

When a production arrives from the other side, everybody has questions:

1. How many documents and images per custodian?
2. What content does the production contain?
3. Did we receive the agreed-upon metadata fields and values?

The most common way to answer these questions is to load the data and start reviewing.

But that’s not the only way…

Metadata Insights with ChatGPT-Created Tools

Here’s what I recommend. After reformatting a DAT file and importing it into Excel, fire up ChatGPT and ask it to create the Visual Basic code for an Excel Macro.

_____________________________________________________________________________________________________

How to Load a DAT File into Excel

1. Open Excel – Start with a blank workbook.
2. Go to the “Data” Tab – Click on Get Data → From Text/CSV (or From Text in older versions).
3. Select the DAT File – Navigate to the file location and open it.
4. Choose Delimited data type.
5. Set the Delimiter – Excel will prompt you to choose a delimiter. Select Other and copy-paste this character þ.
6. Verify Data Formatting – The metadata fields should appear as separate columns in the preview pane.
7. Click “Load” – The data imports into Excel, where you can analyze, filter, and apply formulas.

Note: This process can leave empty columns between the metadata columns. Delete the empty columns before proceeding.

_____________________________________________________________________________________________________

This macro creates a new worksheet. Then it lists all metadata fields from the header row and counts how many records are populated for each field.

The initial prompting is surprisingly basic. After a few rounds of trial and error, I had what I wanted. The macro transformed 1,997 lines of DAT file data into clear, actionable insights.

_____________________________________________________________________________________________________

The Exact ChatGPT Prompt I Used:

“Write a Visual Basic for Applications (VBA) macro for Excel. The macro should:

1. Extract the values from the header row (row 1) of the active worksheet.
2. Create a new worksheet and name it ‘Results’ (or overwrite an existing ‘Results’ sheet if it exists).
3. In the ‘Results’ worksheet, create a table starting at cell A1.
4. Populate column 1 of the table with the header values from the source worksheet.
5. Populate column 2 of the table with the count of non-empty cells in each corresponding column of the source worksheet.

Ensure the code is efficient and includes comments to explain key steps.”

_____________________________________________________________________________________________________

In the accompanying table you can see the metadata snapshot I wanted. Here’s why…

What The Tally Table Reveals – Missing Fields

If parties negotiated an ESI protocol that laid out agreed-upon metadata fields, then it’s worth confirming that those fields have been produced, at a minimum.

In my experience, the difficulty in getting missing metadata produced is directly correlated to the amount of time it took to ask for it. The longer it took, the harder it is to get it.

So, catch missing metadata fields early!

What The Tally Table Also Reveals – Missing Metadata

Let’s do some analysis! We know there are 1,996 records in the production from the row count in the Excel. Which means any metadata field with a value of 1,996 means every record has this field populated. (Close readers will notice that the Excel had 1,997 rows; Row 1 is the header row.)

So, we can limit our analysis to the fields with less than 1,996.

First, we know that there should be 260 native files, since that’s the value next to “Link To Native.” Do we have that many native files in the production volume? Let’s check!

Also, the ‘To’ field is populated for 1,000 records, but the ‘From’ field has 1,030. This could indicate drafts or emails sent with BCCs and empty “To” fields. Another item worth investigating.

But wait! “EmailDateSent” and “EmailTimeSent” are also 1,030. Maybe they aren’t all drafts – maybe they are emails sent to a BCC list where the “To” field was left empty, or maybe a mix of both. Since “FilePath” is present, it should be easy to check whether any are in the “Drafts” v. “Sent” folder.

Speaking of “FilePath,” why do only 1,629 records have this populated? Are these scanned files, or is something else going on? Another issue flagged for checking before the data are even loaded.

This is proactive discovery analysis, birddogging issues before they blindside us.

Ready to Give It a Try?

With just a DAT file, Excel, and a little help from ChatGPT, you can set yourself up for metadata success. Want the macro? Give me a follow and DM me on LinkedIn: https://www.linkedin.com/in/aarondpatton/

Create and Run the Macro in three Pictures:

Step 1:

Step 2

Step 3 & 4

End Result

Disclaimer: The views represented herein are exclusively the views of the author, and do not necessarily represent the views held by my employer, my partners or my clients. eDiscovery Today is made available solely for educational purposes to provide general information about general eDiscovery principles and not to provide specific legal advice applicable to any particular circumstance. eDiscovery Today should not be used as a substitute for competent legal advice from a lawyer you have retained and who has agreed to represent you.