Compare the contents of an .its export folder against your LENA recording file list using Excel

This article details one way to verify that you have exported all of the .its files you intended to for a group of LENA participants.  It applies to a situation where you have exported the group's ITS files to a single folder, and uses a command prompt to generate a list of this folder's contents for comparison against a LENA CSV export.  ADEX can generate the same list - alternate instructions for this method are included in blue.

Similar steps can be used to verify other types of exports, but you will have to make adjustments to 1) compare only one export type at a time against your CSV export (e.g., only .upl files, or only .wav files), and 2) remove file extensions so that the comparison tool works.

1. Use a Command Prompt to list the contents of the export folder.

For more information on this step, see here.

Alternatively, create the same list using ADEX by adding the folder's contents to ADEX and exporting at the Time Interval - Entire Recording level.  Variables do not matter for this purpose - you can deselect all to keep the export small.

2. Convert the list of folder contents to an Excel column.

In the ADEX method, you've already got an Excel column, so open the ADEX export and skip to Step 2.3, working with the File_Name column.

2.1. Open the file in Notepad and copy the text.

2.2. Paste into a blank Excel spreadsheet.

This is just a temporary working spreadsheet.

The entire contents will paste into a single column.

2.3. Highlight the column, and convert text to columns.

  1. On the Data tab, click Text to Columns to open the Wizard.
  2. Choose Delimited, and then click Next.
  3. Under Delimiters, select the Other check box, and specify e as the delimiter.  (This will cut the "e" off of the exported filename in the resulting column.)  Clear the other check boxes, and then click Next.
  4. You'll see a sample of how the delimited data will appear.  You only need the column with .its file filenames.  You can eliminate the extraneous information here.  Select each unneeded column and choose Do not import column (skip) until only the .its file list remains.  (You can also eliminate the extraneous info later in Excel, if preferred.)
  5. Click finish.

The result should be a single column of ITS filenames in format YYYYMMDD_HHMMSS_[DLPNum].its

3. Create an Excel column list of filenames from you LENA data.

3.1. Export the CSV.

Instructions for the Daily CSV export are here - modify as follows:  

  • Select only the group or specific children and the date range for files that should be in the export folder you are checking against.
  • If your data may include consecutive day recordings or overlapping files, you should export at the hourly level to ensure you capture all filenames.  
    • The daily level will display only one file per recording date - the file with the earliest recording timestamp on that date.  So, if file A goes past midnight, and file B is from the next day, file B will not be listed in the daily export, but it will show in the hourly export.

3.2. Remove duplicate filenames in the CSV export.

This step is crucial to ensuring that the comparison step gives accurate information.

  1. Highlight all columns.
  2. On the Data tab, click Remove Duplicates.
  3. Select the check box for My data has headers, unselect all columns, then select ProcessingFile.

The ProcessingFile column now contains only unique filenames and can be compared against the folder contents list.

4. Compare in Excel.

4.1. Copy the column of unique filenames from the CSV into the spreadsheet of folder contents created in Step 2.

4.2. Conditionally format to highlight duplicates.

  1. Highlight both columns - the CSV filenames and the folder filenames.
  2. On the Home tab, choose Conditional Formatting > Highlight Cells Rules > Duplicate Values, then click OK.

Remember, this step will only identify duplicates correctly if:

  • each column contains only one instance of each filename - because duplicate filenames within a column would also be highlighted! This is the reason for completing Step 3.2.

AND

  • both columns contain data formatted exactly the same way (filename only, or filename with matching extension) - this is why we removed the "e" on the folder contents list.  
    • Note:  If you are trying to compare a list of .upl files to your CSV, you'll have to remove the extensions first on both lists, using either an Excel formula or a second round of converting text to columns, with "." as the delimiter.

4.3. Check to see that all filenames are highlighted.

One way to do this is through filtering.  

  1. Select both columns.
  2. Turn on filters.
  3. For each column (one at a time), filter by color = "No fill."

Any filename that is not highlighted does not appear in the other list and so needs to be investigated.