How Do You Automate Recurring CSV Imports and Exports Between Systems That Don't Share an API?

Detailed Explanation

Not every system a business relies on has a usable API. Older accounting software, some point-of-sale systems, bank statement exports, and plenty of niche industry tools still work the way integrations worked decades ago: one system produces a file — almost always CSV — on a schedule, and something else has to pick it up and load it in. How do you connect systems that don't integrate natively covers this as one of three general connection approaches (alongside middleware and direct API integration); this page is the deeper, practical treatment of that file-based approach specifically, since it's common enough on its own to need one.

This is a different problem from keeping a spreadsheet in sync with your business systems, which assumes a spreadsheet is one of the two endpoints. Recurring CSV import/export applies to any two systems exchanging flat files — a legacy point-of-sale system's nightly sales export feeding an accounting platform, a bank's transaction export feeding a reconciliation process, a supplier's inventory feed landing in your ordering system — with no spreadsheet necessarily involved on either side.

How It Works

1. A drop-off and pickup point. The exporting system writes a file to a location the importing side can reach automatically — a shared network folder, an SFTP server, or an inbox that only receives these files. This location needs to be genuinely reliable and access-controlled, since it's the single point every run depends on.

2. A schedule or trigger. Most flows run on a fixed schedule (nightly, hourly) matching how often the source system produces a new file, though some platforms can trigger on a new file's arrival instead of waiting for the next scheduled check.

3. Structure validation before import. Before loading any data, check that the file actually matches the expected structure — the right columns, in roughly the right format — rather than assuming today's file looks like yesterday's. This single check catches most of what otherwise causes silent bad-data imports.

4. Field mapping. Map each column in the file to the correct field in the destination system, consistently every run. This mapping is usually the most fragile part of the whole setup, because it has no enforced connection to the source system's actual structure — see Common Mistakes below.

5. Import, with explicit exception handling. Load the valid rows, and route anything that fails validation (a malformed row, a missing required field, a value in a format the destination doesn't accept) to a location someone actually checks, rather than silently skipping it or letting the whole import fail with no detail on why.

Setting It Up

1. Confirm there's genuinely no API-based option first. File-based syncing is a fallback, not a default — see how do you connect systems that don't integrate natively for when a middleware connector or direct API integration is the better choice instead. Reach for this pattern only once that's ruled out.

2. Pick a drop-off location built for the job, not a convenient one. A dedicated SFTP location or a folder with controlled access is more reliable and more secure than, for example, routing files through a general shared drive or a personal inbox.

3. Build in file-identity tracking to prevent double-processing. Record which files have already been imported (by filename, a checksum, or by moving processed files to an "archive" location) and check against that record before importing — otherwise a file left in place, or a retry after a partial failure, can load the same data twice.

4. Validate structure before mapping data. Check the file's columns match what's expected before running the field mapping — this single step catches a source system's format change before it produces bad data downstream, rather than after.

5. Alert on a missing file, not just a failed import. A scheduled pickup that finds no file at all is often a more serious problem (the export never ran) than one that finds a file with a few bad rows — make sure a missing expected file triggers a visible alert, not silence.

Things to Consider

• This is inherently more fragile than an API-based connection, and that's expected, not a sign of doing it wrong. A file-based sync has no real-time acknowledgment that the other side received and processed the data correctly — build monitoring around that limitation rather than assuming it away.
• Column order and naming are the most common silent failure point. A source system update that reorders, renames, or adds a column can quietly break field mapping that assumed a fixed position — map by column header name where the tool supports it, and validate structure on every run rather than trusting it hasn't changed.
• Match keys and duplicate prevention still apply here, the same as any other sync. See how do you clean up messy data before automating it for choosing a reliable key when the same real-world record might appear in more than one file.
• A file-based sync failing looks exactly like data simply not showing up — with no error to point to unless you built one. See how do you stop an automation from failing silently and how do you troubleshoot a sync that stopped working between two systems for the broader monitoring and diagnosis discipline this pattern needs even more than an API-based sync does.
• Treat this as a stopgap where replacing the system is realistic. If a system's lack of an API is a recurring source of fragile file-based workarounds across multiple integrations, that's a legitimate factor in deciding whether to replace it, not just build around it indefinitely.
• Character encoding is a specific, common CSV failure point worth checking before relying on a new file exchange. See why do names or special characters come out garbled when syncing data between two business systems for why CSV files are especially prone to this and how to prevent it.

Common Mistakes

• Mapping fields by column position instead of column name. A flow that assumes "column 3 is always the amount" breaks the moment the source system adds or reorders a column — map by header name wherever the platform allows it.
• No check for whether the expected file actually arrived. A silent no-show (the source export failed, or ran late) looks identical to "nothing changed today" unless the flow explicitly checks for and alerts on a missing file.
• No protection against importing the same file twice. Without tracking which files have already been processed, a retry, a manual re-run, or a file left in the pickup location gets imported again, duplicating every record it contains.
• Treating a successful file transfer as a successful import. The file arriving doesn't mean its data loaded correctly — validate the import itself, including how many rows succeeded versus failed, not just that the file was received.
• Building this as a permanent architecture instead of a documented stopgap. File-based syncing being the only option today doesn't mean it always will be — note it as a fallback in your system documentation so a future review reconsiders it if either system ever adds a real API.