For businesses whose partners still send files, and probably always will

CSV and SFTP File Integration: Making File Feeds as Reliable as an API

For all the talk of real time APIs, a large share of business data in Australia still moves as files. Suppliers send stock and price lists as CSV. Logistics providers drop dispatch confirmations on an SFTP server. Older ERPs export orders as fixed width text. Banks, payment providers and marketplaces offer settlement reports as downloads. Someone opens the file, tidies it in Excel and imports it, usually first thing in the morning.

Files are not the problem. Manual handling of files is. A well built file integration collects the file on schedule, checks it is complete and sensible, transforms it, loads it, archives it and tells someone when it did not arrive. This page covers how to do that properly, the specific traps in CSV data that break imports, how to keep SFTP secure, and when it is worth asking a partner for an API instead.

Book a File Integration Call (03) 9003 0111

File Feeds in Numbers

5 checks

Before any file is loaded

Arrived, complete, expected structure, sensible row count and not already processed

1 archive

Of every file received

So any record can be traced back to the exact file and line it came from

15 to 60 min

Daily manual handling per feed

An illustrative range for download, clean up and import. Varies widely with file size and quality

0 silent failures

The real goal

A missing or rejected file should raise an alert the same morning, not a complaint a week later

Why File Integrations Deserve More Respect Than They Get

File feeds are often treated as a stopgap. In practice they run for years, and the ones that fail quietly do more damage than the ones that fail loudly.

Your partners choose the format, not you

A small supplier may only be able to email a spreadsheet. A national distributor may offer an SFTP drop with a nightly CSV. A third party logistics provider may have an API for some events and files for others. A legacy ERP may only export text files. Integration has to meet each partner where they are. Insisting on an API from a partner who cannot provide one usually means the data keeps arriving by email and gets typed in by hand.

Files are batches, and batches have timing

A file represents a moment: stock at 2am, prices effective from Monday, dispatches since the last file. That makes timing part of the data. A stock file processed six hours late overwrites newer sales with old quantities. A price file loaded before its effective date changes prices early. Good file integrations understand what moment each file represents and process it accordingly.

CSV is less standard than it looks

Comma separated values sounds simple, but files arrive with semicolon or tab delimiters, quoted and unquoted fields, commas inside product descriptions, different character encodings, header rows that change without notice and blank rows at the end. Each one can break an import or, worse, shift columns so that data loads into the wrong fields without any error at all.

The worst failure is the file that never came

An API call that fails returns an error. A file that never arrives produces nothing at all. If nobody is watching for its absence, the website keeps showing yesterday’s supplier stock, or the dispatch confirmations stop and customers stop receiving tracking emails. Monitoring for expected files that did not arrive is one of the most valuable and most often missing parts of a file integration.

The Anatomy of a Reliable File Integration

Six stages, in order. Skipping any of them is how file feeds end up being checked by a person every morning.

File retrieved

Collect on schedule

Files are collected from wherever the partner puts them: an SFTP server you host or they host, an FTPS endpoint, a cloud storage bucket, a shared drive or an email inbox dedicated to that feed. Collection runs on a schedule matched to when the partner publishes, and the integration knows which files it expects and when. A file that has not arrived by a cutoff time raises an alert to a named person rather than being silently skipped.

No half files

Confirm it is complete

A file that is still being written when it is collected will be truncated, and loading a half stock file can zero out thousands of products. Protection comes from agreed conventions: the partner writes to a temporary name and renames when finished, or sends a separate completion marker file, or includes a trailer row with the record count. Where none of those is possible, the integration checks that the file size has stopped changing before it is read.

Bad files stopped

Validate structure and content

Before anything is loaded, the file is checked: expected columns in the expected order, required fields present, numbers that are numbers, dates that are valid dates, codes that match known products or customers, and a row count within a sensible range of the previous file. A stock file with 40 rows when it normally has 8,000 is held for review rather than loaded. Individual bad rows are separated into a rejection report while good rows proceed, unless the error rate suggests the whole file is wrong.

Your data, your shape

Transform and map

The partner’s codes, units and formats are mapped to yours: supplier item codes to your SKUs, cartons to units, ex GST to inc GST where appropriate, their warehouse codes to your locations, and their date and number formats to the target system’s. Mapping tables are maintained in one place, ideally editable by the business team, so a new supplier code does not need a developer.

No duplicates

Load once and only once

Every file is identified by name, timestamp and a content fingerprint, and recorded when processed, so the same file collected twice is not loaded twice. Where a file contains transactions such as orders, dispatches or payments, each record carries a unique reference that the target system checks before creating anything. That protects against duplicate orders, duplicate invoices and double counted payments when a partner resends a file.

Traceable history

Archive and report

Processed files are moved to an archive with their processing result, and a short summary is produced: rows received, loaded, rejected and why. Rejection reports go to whoever can fix the data, often the partner. Archived files are retained for a defined period, which for anything feeding your accounting records should reflect your record keeping obligations, generally five years for tax records in Australia.

Common File Feeds and How They Should Run

Task	Traditional	Automated Properly	Notes
Supplier stock and price list	Emailed spreadsheet, cleaned in Excel, imported	Collected, validated, mapped and loaded nightly	Price changes may need approval before going live, especially if they affect margin.
3PL dispatch confirmations	Someone checks the SFTP folder and updates orders	Orders marked shipped with tracking automatically	A missing dispatch file should alert by mid morning, because customers are waiting for tracking emails.
Legacy ERP order export	Text file rekeyed into the warehouse system	Fixed width file parsed and sent on	Often the most practical way to integrate an older system that has no API at all.
Marketplace or payment settlement report	Downloaded monthly and reconciled by hand	Downloaded and matched on each settlement	Settlement files feed reconciliation, so field definitions must be understood exactly.
Retailer sales and stock reports	Portal download, pasted into a master workbook	Collected and loaded into reporting	Retailer report formats change occasionally, so header validation matters.
Bulk product data from a brand	Large spreadsheet imported with errors	Validated row by row with a rejection report	Rejected rows go back to the brand with reasons, rather than being fixed silently.
Export for an accountant or auditor	Report run and emailed manually	Scheduled export to a secure folder	Agree the format once and produce it the same way every time.
Partner resends yesterday’s file	Loaded again, duplicates created	Recognised and skipped	Duplicate protection is essential for any file containing transactions.

The Specific Ways CSV and SFTP Feeds Break

Excel silently changing the data

Opening a CSV in Excel and saving it again is one of the most common causes of corrupted imports. Excel removes leading zeros from barcodes and postcodes, so 0812 becomes 812, converts long numbers such as EAN codes into scientific notation, reformats dates, and may change product codes that look like dates. Automated feeds avoid this by never passing through a spreadsheet application, and by treating identifiers as text in every mapping.

Date and number formats

Australian partners usually write dates as day, month, year. Overseas systems and software defaults often use month, day, year. A date like 03/04/2026 is ambiguous and can load as the wrong month without error. Numbers can arrive with thousand separators, currency symbols or a comma as the decimal point from some European systems. The feed specification should fix the format for every date and number field, and validation should reject anything that does not match.

Columns that move without notice

Partners add, remove or reorder columns when they upgrade their systems, often without telling anyone. If the integration reads columns by position rather than by header name, data shifts into the wrong fields: cost prices loading as sell prices, or quantities loading as product codes. Read by header name wherever headers exist, and validate that the expected headers are present before loading anything.

Plain FTP and shared passwords

Plain FTP sends credentials and data unencrypted and should not be used for business data. Use SFTP with key based authentication, or FTPS, restrict access by IP address where the partner can support it, give each partner its own account limited to its own folders, and rotate keys when staff or partners change. Files containing customer personal information fall under the Privacy Act 1988 and the Australian Privacy Principles, including the obligation to take reasonable steps to secure it, so consider encrypting those files at rest as well.

Full files and delta files confused

A full file contains the complete current state, such as every product with its stock level. A delta file contains only changes since the last file. Treating a delta as a full file can set every product not in the file to zero or delete it. Treating a full file as a delta can miss products that have been discontinued. Agree with each partner which kind they send, and design the load logic for that specific kind.

Encoding problems in names and descriptions

Files saved in different character encodings turn apostrophes, accented characters and symbols into garbled text, which then appears on your website or in customer emails. Agree on UTF 8 where possible, detect the encoding where not, and check a sample of descriptions after the first few loads. This matters most for product data that will be shown to customers.

How Yes AI Approaches File Integration

We check for a better option first

If a partner already offers a reliable API or a native connector exists for your systems, we will recommend it. File integration is the right answer when the partner only offers files, when a legacy system only exports files, or when batches genuinely suit the process better than real time messages.

A written feed specification

For each feed we document the format, columns, data types, date and number formats, full or delta, schedule, completion convention and contacts. Partners receive the specification, which removes a large share of future arguments about what a field means.

Feeds run and watched by us

Collection, validation, mapping, loading, archiving and missing file alerts run on a managed cloud automation layer we operate, with a daily summary per feed and immediate alerts for rejections or files that did not arrive.

Secure transport by default

SFTP with key authentication or FTPS, separate accounts per partner, restricted folders, encrypted archives where personal information is involved and documented key rotation. No plain FTP, and no credentials shared over email.

From Manual Imports to Automated Feeds

Five steps. A single well defined feed can be automated in days. A program covering many suppliers and partners is usually delivered feed by feed.

List every file you handle

Every file that arrives or leaves: who sends it, how, how often, what it contains, who handles it now and how long it takes. This usually reveals feeds nobody knew were manual.

Specify each feed

Agree format, columns, types, schedule, full or delta and completion convention with each partner, and document it so both sides work from the same definition.

Set up secure transport

SFTP or FTPS accounts per partner, key authentication, folder structure for incoming, processed, rejected and archive, and access limited to what each partner needs.

Build and run in parallel

Automate collection, validation, mapping and loading, then run alongside the manual process for a period and compare results before switching over.

Monitor and improve

Daily summaries, rejection reports to partners, missing file alerts and a periodic review of which feeds could move to an API as partners upgrade.

FAQ

Is CSV file integration outdated compared with APIs?

No. APIs are better for events that need to move in real time, such as individual orders or stock changes after a sale. Files remain a sensible choice for batch data such as full price lists, nightly stock snapshots, settlement reports and bulk product data, and they are often the only option with smaller suppliers, logistics partners and older systems. The goal is not to eliminate files but to stop handling them manually.

What is the difference between SFTP and FTP?

Plain FTP sends usernames, passwords and file contents without encryption, so anyone able to observe the traffic can read them. SFTP runs over SSH and encrypts everything, and supports key based authentication instead of passwords. FTPS is FTP with TLS encryption added. For business data, use SFTP or FTPS and avoid plain FTP entirely, particularly for files containing customer details, pricing or financial data.

Why do our barcodes lose leading zeros after an import?

Almost always because the file was opened and saved in a spreadsheet application, which treats barcodes and codes as numbers and strips leading zeros, or converts long codes into scientific notation. Automated file integrations avoid spreadsheet applications entirely and treat all identifiers as text. If a partner prepares their file in a spreadsheet, ask them to format those columns as text before export, and validate code lengths on arrival.

How do we stop a bad supplier file wiping out our stock levels?

Validate before loading. Check the file is complete, has the expected columns, and has a row count within a reasonable range of the previous file. Hold any file that fails for review rather than loading it. Also be clear whether the file is a full snapshot or a list of changes, because a change list treated as a full snapshot will set every missing product to zero. Many businesses also cap how much stock can change in one load without approval.

Can you automate files that arrive as email attachments?

Yes. A dedicated inbox per feed receives the attachments, and the integration collects them, checks the sender and the file name against what is expected, and processes them like any other file. It is less robust than SFTP, because email can be delayed or filtered as spam, so missing file alerts matter even more. Where a partner can move to SFTP or cloud storage, that is usually worth asking for.

How long should we keep the files we receive?

Long enough to trace any record back to its source and to meet your record keeping obligations. For files that feed accounting or tax records, Australian businesses are generally required to keep records for five years, and your accountant can confirm what applies. Product and stock files may need a shorter retention period. Files containing personal information should be kept only as long as needed and stored securely, consistent with the Australian Privacy Principles.

Should we host the SFTP server or use the partner’s?

Either can work. Hosting your own gives you control over security, retention and access for all partners in one place, and suits businesses receiving files from many sources. Using the partner’s server is common with large logistics providers and retailers who dictate their own process. Whichever you choose, document who owns the server, who can access each folder and who is responsible when files stop flowing.

Stop Opening Spreadsheets Every Morning

Book a call. We list every file feed you handle today, show you which ones are worth automating and which partners could move to an API, and give you a specification and a priced plan. The review is yours either way.