Not every system has a direct interface it can connect to. An old program, a supplier's price list, or an institution's report are often only available as files.
In such cases, file-based transfer is a practical solution that should not be underestimated. When properly structured, it runs smoothly and reliably.
In this article, we covered template preparation, field mapping, validation steps, and erroneous row management.
Table of Contents
When to use file transfer?
File transfer is a practical method that comes into play when a direct connection cannot be established. It is not suitable for every scenario, but in some cases, it is the only option.
It is the most suitable method for one-time data migrations. Importing existing cards during system setup is a typical example of this.
It is also used for regular lists coming from external parties. Supplier price lists are usually shared as files.
On the other hand, it is not suitable for flows requiring real-time data. Fast-changing data such as stock balances require a direct connection.
The right question is whether the file is sufficient; if it is, the simple solution should always be preferred.
Template preparation
The success of a file transfer depends on a well-defined template. Files arriving in a free format require manual correction every time.
The template must clearly state what each column means. It is also essential that the header row remains fixed.
Mandatory and optional fields must be separated. A row with a missing mandatory field must be rejected without being transferred.
Having a sample row in the template significantly reduces filling errors.
Sharing the template with the other party and keeping it unchanged is a prerequisite for regular transfers.
Format traps
Most errors in file transfers originate from the format rather than the data. If these traps are known in advance, they can be easily prevented.
Number formatting is the most common issue; it must be consistent whether the decimal separator is a dot or a comma.
Date formatting also frequently generates errors. Mixing up the day and month order leads to hard-to-notice problems.
Codes starting with zero get broken by turning into numbers in spreadsheet programs. These fields must be defined as text.
Character encoding should also be checked; corruption of Turkish characters is a common issue.
Field mapping
The correspondence of columns in the file to fields in the system must be defined. This mapping can be done once and saved.
The saved mapping is reused in subsequent transfers and speeds up the process. Re-mapping every time is not sustainable.
Constant value assignments can also be defined; a field not present in the file can be populated with a constant value.
Transformation rules may also be required; the other party's codes must be converted to your own codes.
We discussed code transformation in the multi-code management article.
Validation steps
Pre-transfer validation costs much less than subsequent corrections. This step should never be skipped.
The first check is that mandatory fields are filled. Incomplete rows should be listed before transfer.
The second check is that reference fields have corresponding matches. A row cannot be written with a product code that does not exist in the system.
The third check is that numerical fields are within a reasonable range. Excessively large amounts are usually format errors.
The preview screen provides the final check before transfer and prevents surprise results.
Erroneous row management
It is rare for all rows in a file to be error-free. What matters is how erroneous rows are handled.
Canceling the entire transfer due to a single erroneous row is an unsustainable approach for large files.
The correct method is to import valid rows and collect the erroneous ones in a separate list. This list can be corrected and re-imported.
Providing the error message along with the line number makes correction easier. A generic error message is useless.
Offering the erroneous rows as a downloadable file significantly speeds up the correction process.
Risk of duplicate import
Importing the same file twice is the biggest risk of file-based integration. The result is duplicate records and corrupted balances.
The first way to prevent this risk is to save the imported files. A warning should be given when the same file is uploaded again.
The second way is to use a unique key on a row basis. Records with the same key are not added again, they are updated.
Marking the import batch is also useful; a faulty import can be rolled back in bulk.
When these precautions are not established, the correction work takes much longer than the import itself.
Regular import workflow
One-time imports can be done manually; however, a workflow is needed for regularly recurring streams.
Leaving files in a specific location and automatically retrieving them from there is a common method.
A file naming convention should also be established; names containing dates make tracking easier.
Moving processed files to the archive prevents them from being processed again and creates a historical record.
The failure of the expected file to arrive should also generate an alert; a silently missing import is noticed late.
Export
File integration is not one-way; extracting data is also a frequently needed operation.
Periodic statements sent to the financial advisor are a typical example of this. The format expected by the other party must be used.
Saving export templates prevents the need for re-editing every time.
Privacy must be observed in imports containing personal data; only the necessary fields should be exported.
We discussed the obligations in the data management article.
Points to consider
Modification of the template by the other party is the most common reason that disrupts regular imports. Therefore, the template should be locked.
Skipping the validation step leads to dirty data entering the system, which is much harder to clean up later.
Failing to set up duplicate transfer control also creates serious data issues.
Making file-based solutions permanent is another risk; direct connections should be used whenever possible.
We covered direct synchronization options in the ERP synchronization article.
Frequently asked questions
Which file formats can be used?
Common spreadsheet formats can be used; we determine the format you will use together during the setup phase.
How many rows can be transferred at once?
Large files are transferred by splitting them into parts, which also makes error control easier.
Can a failed transfer be rolled back?
If the transfer batch is marked, a bulk rollback becomes possible; therefore, batch marking is important.
Can the transfer be automated?
Regular retrieval of files dropped in a specific location can be configured; we plan the scope together.
File-based transfer is a solution that should not be underestimated when direct connections cannot be established. Being simple does not mean it is unreliable.
Keep the template fixed and share it with the other party; changing column layouts are the most common reason for disrupting regular transfers.
Never skip the validation step either; preventing dirty data from entering the system is much easier than cleaning it up afterwards.
Make sure to set up duplicate transfer control; uploading the same file twice is the biggest risk of this method.
In the long run, aim for a direct connection; file solutions work most efficiently when treated as temporary.
By consulting with the EQLEM team you can create your data transfer plan.