File Import Routines

Automatic File Import

An import routine watches a source for files and brings them in without anybody doing it by hand.

Where to find it

Architect Panel → Data:

• File Stores — the stores themselves, and View File Store on a row
• Large Uploads — in-progress and stalled upload sessions
• File Import Routines — watched sources and their mappings

Architect Panel → Activity:

• Error Log — where a failed import is recorded

What a routine defines

• The source and its type — where files arrive.
• Check frequency — how often it is swept.
• Limit per check — how many files are taken in one pass.
• Processed action — what happens to a file once handled.
• Error action — what happens to one that fails.
• Logging level.

Each routine also names where the data goes: the database, the table, and the mapping.

The limit per check is a safety valve

Without it, a source that suddenly receives ten thousand files is processed in a single pass, and everything else waits. A limit turns a flood into a queue that drains.

Set it to something the system handles comfortably rather than to the largest batch you can imagine.

Move processed files

The processed action is what stops a file being imported twice. Moving it to a done folder, renaming it, or deleting it — any of these, but not "leave it where it is and hope".

A routine that re-reads the same file every sweep is the commonest way a duplicate import happens.

Failures need somewhere to go

The error action decides where a file lands when it cannot be processed. A quarantine folder is much better than deletion, because the file is usually the evidence of what went wrong.

And somebody has to look at that folder. A quarantine nobody checks is a queue of lost data.

Frequency against latency

Sweep as often as the business actually needs. Every minute for orders arriving from a trading partner; hourly for a nightly export. Frequent sweeps of an empty source are cheap but not free.

Logging level is a real choice

Verbose logging is invaluable while setting a routine up and expensive once it is running thousands of files. Turn it up to diagnose, down to operate.

Watch the volume

A routine that normally imports twenty files and today imported two thousand is telling you something — a re-send from a supplier, a stuck process at their end, or a loop. It is worth noticing before the data is in.

Test with a bad file

Not just a good one. A truncated file, one with the wrong columns, one that is empty. Confirm each ends up in quarantine rather than partly imported.

Worked example

An organisation sweeps a supplier drop folder every ten minutes, taking fifty files per pass, moving processed files to a dated archive and failures to a quarantine folder reviewed each morning. A malformed file test confirmed nothing was partly imported.

Recommendations

• Always set a limit per check.
• Move processed files so they cannot be re-read.
• Quarantine failures and have somebody read the folder.
• Test with malformed files, not only good ones.

Mapping Columns to Fields

A mapping says which part of an incoming file becomes which field in your data.

Where to find it

Architect Panel → Data:

• File Stores — the stores themselves, and View File Store on a row
• Large Uploads — in-progress and stalled upload sessions
• File Import Routines — watched sources and their mappings

Architect Panel → Data:

• Datastores — the fields being mapped to

Map by name where you can

Mapping by column position breaks the moment the source adds a column, and it breaks silently — every field after the insertion is shifted, so the data is wrong rather than absent.

Mapping by header name survives reordering and fails visibly when a column disappears, which is what you want.

Decide what happens to unmapped columns

Ignored, or an error. Ignoring is convenient and means a new column carrying something important arrives unnoticed. For anything where the source might change, failing is safer.

Types are where imports go wrong

• Dates — establish the source’s format explicitly. Day-first and month-first data looks identical for twelve days a month.
• Numbers — decimal separators, thousands separators, and negative values in brackets.
• Leading zeros — account numbers and postcodes that a spreadsheet has helpfully removed.
• Encoding — accented characters arriving as symbols.

Validate before writing

A required field empty, a value outside its range, a reference that matches nothing. Catching those at import means one file in quarantine; catching them afterwards means finding and correcting rows across your data.

Decide about existing records

Whether the import creates, updates or both, and what identifies a match. An import that creates duplicates because the matching field was not unique is the most common import failure, and it is very tedious to unpick.

Import a sample first

Ten rows, then look at them properly — not just that they arrived, but that every field holds what it should. Then run the rest.

Keep the source file

At least for a while. When somebody questions imported data, the file is what settles it, and it is the only way to distinguish a mapping problem from a source problem.

Re-check when the source changes

A supplier changing their export format will not tell you. Anything importing from outside your organisation deserves a periodic check that the mapping still matches what is arriving.

Worked example

An organisation maps by header name, fails on unrecognised columns, and states the date format explicitly. A ten-row sample was checked field by field before the first full run, which found postcodes losing their leading zeros in the supplier’s export.

Recommendations

• Map by name, never by position.
• State the date format explicitly.
• Import ten rows and inspect them before the rest.
• Keep source files to settle later questions.