ETL Workflow to Export a Structured Repository File Inventory to CSV in Repertoire 202x

Repertoire 202x
, ,
1

Overview

Maintaining an inventory of repository files provides a convenient reference for reviewing file locations, types, sizes, and modification dates. This entry explains how to configure an ETL workflow in Repertoire 202x to export a structured inventory of repository files to CSV.

The workflow scans a physical repository directory, including its subfolders. It captures file metadata, derives relative folder paths, standardises file extensions, then formats modification timestamps. The records are sorted by folder and filename before export.

The resulting CSV includes the folder location, filename, extension, file size, modification timestamp, and full path. The workflow uses built-in ETL steps without requiring a datasource or custom script. Source files remain unchanged.

The CSV contains the following columns:

Column Description
FolderPath Complete folder path relative to the repository root.
FileName Original filename, including its extension.
Extension Lowercase final extension, without the dot.
SizeBytes File size in bytes.
LastModified Modification timestamp with timezone offset.
FullPath Absolute filesystem path of the file.

Note: This guide applies to a local repository.

Prerequisites

Before configuring the ETL, permit the repository directory and CSV export destination in etc/application.conf, located under the Repertoire Server installation directory.

A) Configure File Reading

Locate the elixir.data.file block. Include "./repository" in the readable list:

elixir.data.file {
  # Only files within these directories (and sub directories) are readable and writable by the File Steps.
  # You could use "/" or "C:/" or equivalent to allow access to all files and folders, but restricting the
  # scope to certain folders is preferred. Relative paths are relative to the installation root folder
  readable: ["./data/in", "./data/out", "./repository"]
  writable: ["./data/out"]
}

readable permits Directory Reader to access the repository directory, including its subfolders.

Relative paths resolve from the installation root. For example, "./repository" refers to C:/Elixir/Repertoire-202x/repository for an installation at C:/Elixir/Repertoire-202x.

Note: The directory must exist. If the repository is stored elsewhere, add its absolute path to the readable list instead.

B) Configure CSV Writing

Locate the elixir.data.csv block. Ensure that writable includes the CSV export directory:

elixir.data.csv {
  readable: ["./data/in", "./data/out"]
  writable: ["./data/out"]
}

CSV File Writer uses elixir.data.csv.writable. The writable setting under elixir.data.file does not control CSV export.

C) Apply the Configuration

Preserve existing permitted locations required by other workflows. Restrict access to the necessary directories rather than allowing an entire drive.

Save etc/application.conf. Restart Repertoire Server to load the changes before running the ETL.

Step 1: Create the ETL Chain

In ETL Management, create a chainset named Repository Files to CSV. Navigate to ETL Designer. Create or select the Main chain.

Configuration:

  • Chainset Name: Export Repository Files to CSV
  • Chain Name: Main

image
image518×242 6.43 KB

image
image398×161 5.18 KB

Step 2: JSON Record

Add a JSON Record step. Name the step Set Repository Root.

Configuration:

  • Merge Order: Keep only this
  • JSON:
{
  "RepositoryRoot": "C:/Elixir/Repertoire-202x/repository"
}

2
2497×727 17.8 KB

Explanation:

Keep only this: Outputs the configured JSON record rather than merging it with incoming fields.

RepositoryRoot contains the directory path. This step supplies the configuration record. It does not list the files yet.

Note: Use an absolute path with forward slashes and no trailing slash. Replace the example with your actual repository directory.

Step 3: Directory Reader

Add a Directory Reader step. Name the step List Repository Files Recursively.

Configuration:

  • Directory Field: RepositoryRoot
  • Recursive: Enabled

3
3496×731 14.9 KB

Explanation:

Directory Field identifies the incoming field containing the source path. Enter RepositoryRoot, not the directory path itself.

Recursive includes files in nested folders. The reader produces one record per file, with metadata such as:

file
fileName
folderName
fileLength
fileLastModified

The native folderName contains only the immediate parent folder name. The workflow derives a complete relative folder path to distinguish locations such as /Administration/Reports from /Finance/Reports.

Note: Inspect the records in the Results panel before continuing. If no records appear, check the source directory, permitted readable locations, and execution messages.

Step 4: Restructure

Add a Restructure step. Name the step Create Readable Inventory Columns.

Configuration:

  • Specification:
{
  "RelativePath": "$file",
  "FullPath": "$file",
  "FileName": "$fileName",
  "Extension": "$fileName",
  "SizeBytes": "$fileLength",
  "LastModified": "$fileLastModified"
}

4
4497×727 24.6 KB

Explanation:

Each property defines an output field. Values beginning with $ reference fields in the incoming record.

For example, "FileName": "$fileName" copies the reader’s filename into FileName.

The fields serve these purposes:

  • RelativePath: Working copy of the full path used to derive the folder location.
  • FullPath: Original absolute path retained for reference.
  • FileName: Original filename retained unchanged.
  • Extension: Working copy of the filename used to extract its extension.
  • SizeBytes: File size copied from fileLength.
  • LastModified: Timestamp copied from fileLastModified.

Note: At this stage, RelativePath is still absolute. Extension still contains the complete filename.

Step 5: Remove Prefix

Add a Remove Prefix step. Name the step Remove Repository Prefix.

Configuration:

  • Field: RelativePath
  • Value / Prefix: C:/Elixir/Repertoire-202x/repository

5
5497×730 14.6 KB

Explanation:

The configured prefix is removed from the beginning of RelativePath. The remaining value identifies the file location relative to the repository root.

Example:

Before: C:/Elixir/Repertoire-202x/repository/Administration/Reports/Monthly.rml
After:  /Administration/Reports/Monthly.rml

FullPath remains unchanged.

Note: Match the prefix exactly, including its case and slash style. Omit the trailing slash. Update this value whenever RepositoryRoot changes.

Step 6: Add Fields

Add an Add Fields step. Name the step Create Relative Folder Path.

Configuration:

  • Specification:
{
  "FolderPath": "$RelativePath"
}

6
6497×730 16.9 KB

Explanation:

This copies RelativePath into FolderPath.

Example:

RelativePath: /Administration/Reports/Monthly.rml
FolderPath:   /Administration/Reports/Monthly.rml

A separate working field allows the folder value to be transformed without changing the original file paths.

Step 7: Replace RegExp

Add a Replace RegExp step. Name the step Remove Filename from Folder Path.

Configuration:

  • Field: FolderPath
  • Regex: /[^/]+$
  • Value / Replacement: Leave empty

7
7496×727 14.8 KB

Explanation:

The expression matches the final slash followed by the filename. Replacing the match with an empty value leaves the parent-folder hierarchy.

/       = A path separator
[^/]+   = One or more characters that are not a path separator
$       = The end of the value

Example:

Before: /Administration/Reports/Monthly.rml
After:  /Administration/Reports

Note: Leave the replacement field genuinely empty. Do not enter a space or quotation marks.

Step 8: Replace RegExp

Add a Replace RegExp step. Name the step Label Repository Root Folder.

Configuration:

  • Field: FolderPath
  • Regex: ^$
  • Value / Replacement: /

8
8496×727 14.8 KB

Explanation:

A root-level file such as /Readme.txt has an empty FolderPath after filename removal.

^$ matches this empty value. The replacement / identifies the repository root.

Example:

Root-level file: FolderPath = /
Nested file:     FolderPath = /Administration/Reports

Nested folder paths remain unchanged.

Step 9: Replace RegExp

Add a Replace RegExp step. Name the step Extract File Extension.

Configuration:

  • Field: Extension
  • Value / Replacement: $1
  • Regex:
^(?:.+\.([^.]+)|.*)$

9
9497×727 15.1 KB

Explanation:

The expression captures the text after the final dot. $1 returns the captured extension without its dot.

The alternative .* also matches filenames without an extension. Their extension capture is empty, so the replacement produces a blank value.

Examples:

Filename Extracted extension
Monthly.rml rml
DATA.DS DS
archive.tar.gz gz
README Blank
.hidden Blank

Note: Enter a single backslash before the dot in the Regex field. The exported chainset JSON represents that backslash as \\.

Step 10: Lower

Add a Lower step. Name the step Normalize Extension Case.

Configuration:

  • Field: Extension

10
10497×730 12.8 KB

Explanation:

The step converts extension values to lowercase for consistent filtering.

Examples:

DS  -> ds
RML -> rml
CSV -> csv

Only Extension changes. FileName retains the original filename, including its capitalisation.

Step 11: Timestamp - Local Format

Add a Local Format step from the Timestamp category. Name the step Format Last Modified Timestamp.

Configuration:

  • Step Type: timestamp.LocalFormat
  • Source Field: LastModified
  • Format: uuuu-MM-dd HH:mm:ss XXX
  • Locale: en
  • Destination Field: LastModified

11
11496×727 17.3 KB

Explanation:

This converts the modification timestamp into a readable value using the server’s configured clock timezone.

uuuu-MM-dd = Date in year-month-day order
HH:mm:ss   = Time using the 24-hour clock
XXX        = UTC offset, such as +08:00

Example:

2026-10-02 08:30:00 +08:00

Using LastModified as both fields replaces the raw timestamp in the output record. The source file’s modification date is not changed.

Step 12: Small Sort

Add a Small Sort step. Name the step Sort by File Name.

Configuration:

  • Field: FileName
  • Descending: Disabled

12
12497×728 14.1 KB

Explanation:

The step establishes ascending filename order. This order is retained within each folder group when the folder-path sort is applied.

Note: Apply the filename sort before the folder-path sort.

Step 13: Small Sort

Add a Small Sort step. Name the step Group Files by Folder Path.

Configuration:

  • Field: FolderPath
  • Descending: Disabled

13
13497×728 14.5 KB

Explanation:

The step groups records by FolderPath while retaining the established filename order. The final inventory is ordered by folder, then by filename within each folder.

Example:

FolderPath FileName
/Finance Budget.rml
/Finance Summary.rml
/Administration PieChart.rml
/Administration PieChartData.ds

Note: Sorting is case-sensitive. Small Sort buffers records in memory. Ensure that available server memory is sufficient for the expected inventory size.

Step 14: CSV File Writer

Add a CSV File Writer step. Name the step Export Structured Inventory CSV.

Configuration:

  • Write Header: Enabled
  • Separator: ,
  • Qualifier: "
  • Newline: CR/LF
  • Add BOM: Enabled
  • Filename: data/out/repository_files_structured.csv
  • Columns: Enter the following field names, one per line.
FolderPath
FileName
Extension
SizeBytes
LastModified
FullPath

14
14497×728 23.6 KB

Explanation:

Columns: Specifies the exported fields in their required order. The internal RelativePath field is omitted.

Write Header: Adds the column names as the first CSV row.

Qualifier: Preserves values containing commas within a single field.

Newline: Uses Windows-style CR/LF line endings.

Add BOM: Helps applications recognise the UTF-8 encoding.

The CSV is written to the Repertoire Server filesystem. It is not automatically downloaded to the browser.

For the example installation, the intended destination is:

C:/Elixir/Repertoire-202x/data/out/repository_files_structured.csv

Use an absolute filename if an unambiguous output location is required. The destination directory must be permitted by elixir.data.csv.writable.

Note: Each run overwrites the selected CSV. Use a different filename to retain earlier exports. Keep the destination outside the scanned repository. Avoid concurrent runs writing to the same file.

Workflow Summary

Define the repository directory
    |
    v
Read file metadata recursively
    |
    v
Create inventory fields
    |
    v
Derive relative folder paths
    |
    v
Extract and standardise file extensions
    |
    v
Format modification timestamps
    |
    v
Sort by filename, then group by folder
    |
    v
Export the selected columns to CSV

The export contains FolderPath, FileName, Extension, SizeBytes, LastModified, and FullPath. Source files remain unchanged.

Troubleshooting

A) Directory Access Is Rejected

For an error such as:

Can't access directory ... - check folder restrictions

Verify that:

  • The directory exists on the Repertoire Server machine.
  • RepositoryRoot contains the intended absolute path.
  • elixir.data.file.readable permits the directory.
  • The edited etc/application.conf belongs to the running server installation.
  • Repertoire Server was restarted after the configuration change.

Note: ./repository resolves under the server installation. It does not automatically refer to a separate Repertoire Designer installation.

B) Records Are Missing or the Output Is Empty

  • Set Directory Field to RepositoryRoot, not a literal path.
  • Enable Recursive to include nested folders.
  • Confirm that the selected directory contains files.
  • Review execution messages for inaccessible locations.

Separate folder records, including empty folders, are not included in this workflow.

C) Folder Paths Remain Absolute

Check the value in Remove Repository Prefix. It must match the beginning of the generated file path, including case and slash style. Omit the trailing slash.

Update the prefix whenever RepositoryRoot changes.

D) CSV Export Fails

  • Confirm that CSV File Writer is enabled.
  • Check the destination against elixir.data.csv.writable.
  • Verify the actual output location on the server.
  • Check whether another application has locked the CSV.

Note: elixir.data.file.writable does not control CSV File Writer. Avoid concurrent runs writing to the same file.

E) Long Values Appear Cut Off in Excel

CSV files do not retain column widths or text wrapping. Long paths may appear partially hidden even though the complete value was exported.

Use AutoFit Column Width or Wrap Text in Excel. Automatic export-time column sizing requires a spreadsheet writer with formatting support.

What This Guide Achieves

  • Creates a structured inventory of files in a physical repository directory.
  • Preserves folder locations and original filenames for easy identification.
  • Presents file metadata in a consistent, readable format.
  • Organises the inventory for filtering, review, and comparison.
  • Exports reusable CSV data without modifying the source files.