Template Filler Tool

User Manual & Job Aid  |  Version 1.0  |  March 2026

1   What is the Template Filler Tool?

The Template Filler Tool (TFT) is an Excel macro workbook that automates the process of filling Oracle ERP upload templates from a Master Raw Data file.

Without TFT, a user would open the raw export, copy columns into the template manually, apply transformations (remove dashes, add fixed values, decide CREATE vs UPDATE row by row), save the file with a dated name, and repeat for every template variant. This takes hours and introduces copy-paste errors.

With TFT, you configure the mapping once, save it, and from then on the whole process runs in a single click — including file naming, filtering, and email dispatch.

2   Typical Workflow — Quick Start

First time with a new template — takes about 5–10 minutes to set up. Every run after that takes seconds.

3   The Run Sheet — Buttons & Email Settings

The Run sheet is the home screen of TFT. It contains four buttons and the email configuration area.

Email Settings (cells B16–B19 on the Run sheet)

Fill these in once. They are used every time you click Send Email.

4   Template Filler Form — Section by Section

The main form is divided into four sections: Template, Data Source, Output, and Column Mapping.

4.1 Template Section

Choose where your template lives:

• External file — browse to an .xls or .xlsx template file on your computer.
• This workbook — sheet — pick a sheet tab in the TFT workbook that serves as the template layout.

Header row — the row in the template that contains column names. Usually row 1. Use the arrow (◄ ►) buttons to change it. After changing, click Scan Template to refresh the column list.

Scan Template — reads the template headers and populates the Template Column column in the mapping grid. Always click this before mapping for the first time, or after changing the header row.

4.2 Config Load / Save Bar

The dropdown lists all saved configs. Select one and click Load Config to restore all settings (template, mapping, output folder, filter, etc.) from a previous session.

After making changes, click Save Config. If the config name already exists it is overwritten. To create a new one, type a new name in the box first.

4.3 Data Source Section

Source file — browse to your Master Raw Data file (.xlsx or .xls). The path is stored in the config but the file is never modified.

Hdr row — the row containing column headers in the source file. Usually 1.

Dat row — the first row of actual data. Usually header row + 1 = 2.

Scan Source — reads the source file headers and makes them available in the Source/Value dropdowns in the mapping grid. You must click this before the dropdowns will show your source columns.

4.4 Output Section

File Name Tokens

Example pattern: {DATE}_DK_{TMPL}_{GROUP}_ABCD → produces 18MAR2026_DK_TEMP_ITM_UPD_CATG_1234_ABCD.xls

4.5 Column Mapping Grid

The heart of the tool. Each row in the grid represents one column in the template. For each column you choose:

• Type — how to fill this column (see Section 5)
• Source / Value — the source column name or value to use
• Lookup Set — only used for LOOKUP and COND types
• Norm — whether to clean up the value (see Section 6)

5   Column Mapping Types

Every template column must be assigned a Type. The Type tells TFT where the value comes from.

5.1 COL — Detailed Notes

Use COL whenever the value comes from your raw data and differs from row to row. The Source/Value dropdown shows all column headers found in the source file after Scan Source.

Pair with Norm (Section 6) when the source data may contain dirty values (dashes as placeholders, leading spaces, invisible characters).

5.2 STATIC — Detailed Notes

Use STATIC for any value that is always the same regardless of the data row. The Source/Value dropdown shows presets — common values stored in the Presets sheet (see Section 9). You can also type a value directly.

When you save a config with a STATIC value that is not yet in the Presets sheet, TFT automatically adds it so it is available next time.

5.3 COND — Detailed Notes

COND checks whether the source column cell is blank for each row and writes one of two values accordingly.

Blank detection is smart: a cell containing only dashes (-), equals signs (=), spaces, or other non-alphanumeric characters is treated as blank. This prevents false UPDATE results when the source file uses - as a "no value" placeholder.

Lookup Set format: type the two values separated by a pipe character with no spaces: CREATE|UPDATE The value before the pipe is used when the source cell is blank; the value after the pipe is used when the source cell has content.

5.4 LOOKUP — Detailed Notes

Lookup tables live on the Lookup_Sets sheet. Each named range is a two-column table: column A = the key to match, column B = the value to write.

The Lookup Set dropdown in the mapping form lists all named tables on the Lookup_Sets sheet. If a key is not found in the table, the cell is left blank.

6   The Norm (Normalize) Checkbox

The Norm checkbox appears at the end of each mapping row. When ticked, TFT cleans the resolved value before writing it to the template.

What Norm does

1. Removes leading and trailing whitespace and invisible control characters.
2. If the result contains no letters or digits at all (only dashes, equals, at-signs, pipes, etc.) → the cell is cleared. These are data-entry errors or placeholder values in the source.
3. If the result contains at least one letter or digit → it is kept exactly as cleaned.

What Norm preserves

When to use Norm

Norm has no effect on STATIC, COUNT, COND, or LOOKUP types — those values are controlled by the mapping, not the source data. The Norm checkbox is automatically greyed out for those types.

7   Filter / Group Section

Use this section to control which source rows appear in the output and whether to split the output into separate files.

7.1 Group by Column

Select a source column from the dropdown. This is the column whose values determine which rows belong together. Leave as (none) to include all rows in one file.

Click Load Values to populate the value list below with the unique values found in that column. Then select which values to include or exclude.

7.2 Filter Modes

7.3 Output Modes

8   Saved Configs — Saving and Loading Mappings

A config stores everything you set in the form: template selection, header row, column mapping (type, source, lookup set, norm for every column), output folder, file format, file name pattern, filter mode, and group column.

Configs are stored on the Saved_Configs sheet. You can view them there but should not edit them manually.

The source file path is intentionally NOT saved. Each run you browse to the current month's source file. This prevents the config from accidentally pointing to last month's data.

Loading a config

1. Open the Template Filler form.
2. Select the config name from the dropdown at the top of the form.
3. Click Load Config.
4. The template and all mapping settings are restored. A green confirmation message appears.
5. Browse to your source file and click Scan Source.
6. Click Run.

9   The Presets Sheet

The Presets sheet is a reference table of common STATIC values. Column A = Template_Column (the template column name, e.g. ORGANIZATION_CODE). Column B = Preset_Value (the value to fill, e.g. 100).

When you select STATIC type for a column in the mapping form, the Source/Value dropdown is pre-populated with all Preset_Value entries that match the template column name. This saves typing and prevents typos.

Auto-append: When you save a config with a STATIC value that is not already in the Presets sheet, TFT adds it automatically. Over time the Presets sheet builds up a complete library of known values.

10   Batch Run — Running Multiple Configs at Once

The Batch Run form lets you run several saved configs sequentially with one source file and one click. This is ideal when you produce multiple template variants from the same Master Raw Data file.

How to use Batch Run

1. Click Run Batch on the Run sheet.
2. Tick the configs you want to run in the list.
3. Click Browse… next to Master Source Data File and select your source file.
4. Optionally, browse to an Output Folder to override the folder stored in each config. Leave blank to use each config's own saved folder.
5. Click Run Selected.

Each config runs in sequence. Progress is shown in the status bar. Results are logged to the Log sheet when complete.

11   The Log Sheet

Every run is recorded on the Log sheet. The log is cumulative — rows are never removed automatically (use Log Archive to clear it periodically).

12   Send Email

The Send Email button opens a file picker that shows all output files recorded in the Log. The email is composed in Outlook and opened for your review — it is never sent automatically.

The file picker

• All SAVED files from the Log are listed, pre-selected.
• Files that no longer exist on disk are shown with [file not found] and are NOT pre-selected.
• Each row shows the filename prominently, with the folder path as context to the right.
• Select All — ticks all existing files.
• Clear All — unticks everything.
• Add File… — browse to any file anywhere on your computer and add it to the list. Use this to attach files that were not created by TFT (e.g. a cover note or a reference document).

Email preview

The bottom of the picker shows a preview of the To:, Subject:, and file list that will appear in the email. If the To: field (cell B16 on the Run sheet) is blank, a warning is shown — you can still open the email and fill in the address manually in Outlook.

After clicking Send Email

Outlook opens with the email ready to review. You can edit the recipients, subject, body, or add additional attachments before clicking Send in Outlook.

13   Log Archive

When the Log sheet grows large, use Log Archive to export it and clear it.

1. Click Log Archive on the Run sheet.
2. Browse to the folder where you want to save the archive file.
3. A confirmation dialog appears: "This will archive X log rows and clear the Log sheet. Are you sure?" — No is the default. Click Yes to proceed.
4. The archive is saved as an .xlsx file with a dated name. The Log sheet is cleared.

14   Best Practices

1. Always Scan Source before running. Even if you loaded a config, the source columns are not in memory until you scan. Without scanning, the run will fail or produce empty columns.
2. Save your config before running for the first time. If the run fails you can reload the config and adjust without re-mapping everything.
3. Use Norm on COL columns from Oracle exports. Oracle exports frequently contain dashes, extra spaces, or invisible characters in "empty" cells. Norm clears these automatically without affecting real values.
4. Do not use Norm on code columns (item numbers, IDs). These are controlled values. If they are dirty in the source, that is a source data problem that should be fixed upstream, not silently modified by TFT.
5. Prefer "Not blank" filter over "Include selected". Selecting individual values means you must re-open the config every time a new org code or category appears in the data. "Not blank" handles new values automatically.
6. Use Batch Run for recurring multi-template jobs. Set up all your configs once, then every run is: open Batch Run → browse to source → Run Selected. Avoids opening the mapper form at all.
7. Keep the Presets sheet tidy. Remove outdated values (old process IDs, retired org codes). The Presets sheet is shared across all configs — stale values clutter every STATIC dropdown.
8. Use the {GROUP} token in the file name pattern when running "One file per group value". Without it, all files would have the same name and only the last one (or versioned copies) would survive.
9. Archive the Log monthly. A large Log slows down the Send Email file picker (it scans every SAVED row). Archive at the end of each month cycle.
10. Fill in B16–B19 on the Run sheet once and leave them. The email settings are permanent — you only need to update them if recipients change. The body tokens ({DATE}, {FILES}) are replaced fresh on every send.

15   Common Mistakes & Troubleshooting