Coding in Excel and Uploading Metadata
General
The user may modify document coded data and tag multiple files (modify multiple fields simultaneously) or import load files by generating a custom Excel spreadsheet and uploading to a case. The custom Excel spreadsheet must be named in the following format: filename.multidocupdate.xlsx (e.g. metadata.multidocupdate.xlsx).
Cautions for Use-Support
This feature is for Power Users with advanced Excel skills. If done incorrectly, data coding may be lost.
Do not add any letters, spaces or characters to the file name extension ".multidocupdate.xlsx." If not saved exactly as noted above, the metadata will not load properly.
It is good practice to create a practice case to test this feature before using on live data.
Do a test upload in a live case of a few records (Excel rows) before uploading a large spreadsheet (many rows).
Perform a backup of records before each update and retain locally. We cannot restore data unless previous entries have been saved to an Excel.
The user is responsible for quality control to determine changes were made as intended. Data may fail to update as expected for various reasons, including: Excel version, data types, unsupported characters, system design limitations, and bugs.
Professional Services is available to assist with this procedure (standard hourly rate).
How to Use Excel to Update Metadata and Coded Fields
The Excel spreadsheet containing the metadata is uploaded as any other document from Case->Add Case Documents.
Create and Download an Excel Sheet from the Case
Open Browse or Search.
Filter on the documents to modify.
Select the columns (fields) to modify from the Columns->Show Fields section. Some Built-in Doc Fields are read-only and cannot be modified (see list below). There is no need to export these.
--Ext
--Original Extension
--SourceFile Path
--Pages
--Words
--Size
--Folder
--Date Uploaded
--IsEmailAttachment
--PlaceHolder
--IsDuplicate
--Doc Category
--Custodians
--Notes
Click on the Select Docs hyperlink and select all documents in list.
Go to the Export section and click Export Log to Excel button.
Save the Excel sheet to local computer.
Make a backup copy and save with a name that reflects the data selected i.e. 2013-05-20 Smith vs. Jones Case All Responsive documents.xlsx.
Make a Working Copy for Editing
Copy the Excel file.
Rename using the naming format: AnyFileName.multidocupdate.xlsx
The file must be in an Excel format with an XLSX extension, supported by the last two versions of Excel (2010 and 2013). Older Excel formats (e.g., XLS) and other Excel formats such as XLSM are not supported.
Working with the Excel File
The Excel file must retain the exact column titles in Row 1 of the spreadsheet that was exported from the Lexbe eDiscovery Platform (LEP).
The first column: Doc Id includes the unique document identification in LEP that will associate metadata to the respective files to process an update. This column must be retained and cannot be sorted to disassociate with the data in each row. If improperly sorted or otherwise unassociated, data corruption will occur in the case on upload.
The user may edit the Excel columns.
Do not edit read-only columns. They will not update on upload.
Remove any column that will not be updated prior to upload.
Dates are supported in a number of formats, including the following:
June 13, 2013
2013/06/13 10AM
2013/06/13 8am
06/13/2013 14:00
06/13/2013 2:00:00 PM
06-13-13
The default time format is 12:00:00 AM
Do not upload Excel formulas. Excel formulas should be converted to values. Failure to convert will result in upload failure.
When populating data to check-box fields (Custom Doc Fields), use Yes/No values. In the example, the main case section was titled Deponents and had two check-box fields titled Mark and Tobey. Remove the main case section (Deponents column) and keep only the check-box fields to apply mass tagging.
Updating Bates Numbers
Use this feature to update Bates numbers from file names that are Bates numbers as follows:
Remove the Bates attachment range fields (BatesNumberAttachment Start and BatesNumberAttachment) and keep only the headers BatesPrefix, Bates, and BatesNumberDigits.
The Bates column indicates the beginning numeric portion of the Bates designation. The user may also use text formula/functions to extract the Bates from files that are saved with Bates names. Paste values in place of formulas.
To delete a Bates number from appearing in LEP, the BatesPrefix column should be empty. The Bates column should have a 0 in each cell.
Below is an example of an Excel spreadsheet ready for upload:
Import the Metadata Log to LEP
Go to Case->Add Case Documents.
Navigate to the Upload page. Follow the steps of a normal upload. The file extension will populate the metadata instead of just reading the Excel as a regular file upload.
Fill out the Batch Title (helpful to name as a Metadata upload). Fill in the DocSource.
Click Next.
Add the Excel file. When the file is added, it will automatically begin to Upload.
Allow a couple of minutes for the metadata to be processed and uploaded.
Associating Email Families
The user can restore a parent child relationship (email families) for a production upload using a custom Excel spreadsheet (.multidocupdate.xlsx). This will associate file attachments with the emails.
Create a Load File
After the upload is complete go to Case->Add Case Documents and click on the production batch uploaded in order to find the email families.
From the Browse->Fields->Show Fields select the following:
-DocId
-Title
-Ext
Export the log to an Excel spreadsheet (Export->Export Log To Excel). Customize the load file exported adding the column headings Bates Begin and Parent Bates Begin.
Adjusting the Original Production Load File
Open the original load file that came with the production.
Depending on the original load file format, the column titles could be named as follows: Bates Begin can be called ProdBeg, Beg Bates, BEGDOC, and StartBates (see screenshot above Column A). Rename so that the title is Bates Begin.
Parent Bates Begin values require special attention. They can be simple or complex to obtain depending on the data from the original load file. Some load files will have a column designated for the ParentId containing the Bates title of the parent document of the attachment. If there is a column like that in the original load file, use it by renaming the title of the column with Parent Bates Begin. If there is not a field that is designated to the ParentId, there may have four columns that indicate where the document or the family range starts and ends. If that is the case, use Excel Logical Formulas in the original load file to generate the FamilyId and ParentId metadata before merging values, and then rename the ParentId column to Parent Bates Begin (Column H).
The user may obtain the fields required in Excel from LEP to restore the email families.
Merging Metadata Between Load Files
Copy the metadata in the Bates Begin and Parent Bates Begin columns from the original load file and paste into the same columns in the new load file to restore email families.Save the load file with the extension file name .multidocupdate.xlsx (e.g.EmailFamilies.multidocupdate.xlsx).
Upload the load file to the case.
* Here is a sample of a file mapping Excel spreadsheet including email families.
* Here is a sample of a Production ready to be uploaded. Practice with sample files to get familiar with the Upload Production/Load File process.
The Platform Load Files Specifications
Processed Natives with standard Concordance load file. See Tiff Image Dat Load File for more information.
TIFFs with standard Concordance load file. See Native Load File for more information.
Unprocessed Natives which include PSTs, MS Office, and most common file types can be loaded directly and will be indexed for review. See Supported File Types for Automated PDF and TIFF Creation for more information.
LEP automatically creates a normalized PDF. TIFFs can be created as part of a production.
Other Notes
When creating a custom list for sorting in Excel, be careful to extend selection to the DocID field in order to avoid corrupting the data.
Keep a backup copy (save to desktop) of each Excel Metadata import. This is essential to restore the database if data is accidentally imported or associated incorrectly.
Show extensions for known file types in Windows. Turn on File Extensions under the options properties dialog box from the Control Panel.
If necessary, disable Document Inspector in the Trust Center to avoid warning messages or prevent format changes in Microsoft Excel.
The user may delete the original file (e.g. metadata.multidocupdate.xlsx) from the case after the metadata has been uploaded an checked for accuracy.
LEP supports the two most recent versions of Excel for Excel integration (2010 and 2013). Depending on the Excel version used to save the spreadsheet, the format of the hyperlinks in the first column may cause the import to not recognize the Doc Id value. Avoid this issue by removing the hyperlinks from the Excel file prior to import by selecting Column A, right-clicking on the column and selecting Remove Hyperlinks.
Troubleshooting
Non-standard load files will fail standard upload procedure. If an upload has not worked as expected, consider the following:
Perform a test upload using the demo data set (in a test case).
Use the Excel template to create the load file. The Bates names in the LEP load file Excel spreadsheet and the file names in the ORIGINALS folder must match exactly or the load will fail.
Multi Doc Edit vs. Upload Metadata