From Spreadsheet Rows to a Folder Tree
A practical design pattern for turning a three-column Excel hierarchy into nested folders without losing the parent-child relationships.
Origin: November 21, 2024 · Technical pattern
A three-column table can describe a folder tree, but only if the program knows what the blanks mean.
Consider this entirely synthetic example for a fictional creative project:
| Root | Category | Subcategory | |---|---|---| | DemoProject | Design | Logos | | | | Exports | | | Production | Drafts | | | | Final |
The intent is one root folder named DemoProject. Inside it are Design and Production. Under Design are Logos and Exports; under Production are Drafts and Final.
The blanks are not empty new folders. They mean keep the last explicit parent at that level.
A safe implementation sequence
- Ask for the destination first. A folder picker returns the parent directory. If the user cancels, create nothing.
- Skip header rows. Start at the first actual data row, not the column labels.
- Maintain parent state. When the root column contains a name, update the active root. When a new category appears, update the active category under that root.
- Resolve each child under its actual parent. Never build a path by blindly joining the values visible in the current row.
- Validate folder names. Reject empty required names, reserved names, path separators and traversal such as
... Decide how to handle conflicting duplicates. - Create top-down. Confirm the root exists before attempting a category, and the category before attempting its child.
- Report the exact failure. Include the table row, intended relative path and stage. Do not display success if only the root was created.
What can go wrong?
A macro may create the top folder but fail on a child because it constructed the child path under a blank or stale category. The selected destination isn't necessarily the problem.
Another failure appears when the table has repeated category labels or intentional gaps. If the code treats every row as independent, it can accidentally flatten the tree.
The data model needs to say what blank values mean. It cannot assume the reader will interpret them correctly.
A test worth running
With a synthetic four-row sheet, choose an empty temporary destination. Run the creation routine once, inspect the exact directory tree, then run it again.
The second run should follow the selected duplicate policy, rather than creating surprising new paths.
Canceling the folder picker should make no change at all. Invalid names should stop with a useful error.
This is a proposed test contract, not a claim that the November 2024 macro passed it. The historical exploration still had path and hierarchy errors.
A folder tree is a relationship, not a collection of strings.