How to Configure SFTP-based Data Automation for Reconciliation

Automating file ingestion over SFTP is a reliable way to remove manual uploads from recurring reconciliation cycles. This guide explains practical steps finance teams and operators should follow to configure SFTP-based data automation, map files correctly, and keep reconciliation runs predictable and auditable.

SFTP-based data automation reduces repetitive work, shortens review cycles, and helps maintain consistent data for matching engines. The approach described here works for common reconciliation scenarios such as bank vs books, payment gateway vs sales, marketplace settlements, and vendor statement matching.

We will focus on setup tasks you can complete with IT and your reconciliation admin, how to prepare files, configure mappings and derived columns, schedule automated runs, and monitor results so exceptions are surfaced quickly.

Why this topic matters

Finance teams spend disproportionate time retrieving and uploading source files before the actual reconciliation work begins. Automating that input via SFTP frees up time for investigation and exception resolution.

For monthly and high-frequency reconciliations, automation reduces human errors from manual downloads, ensures consistent file naming and structure, and enables scheduled runs that align with close calendars. In regulated environments, automation also supports creating repeatable, audit-ready processes.

Core components

A successful SFTP-based automation pipeline relies on four core components: the SFTP server and credentials, consistent file formats and naming conventions, accurate mappings and optional derived columns in the reconciliation engine, and a reliable scheduling and monitoring process.

SFTP server and credentials

• Hostname or IP: the SFTP endpoint your system will connect to.
• Port: commonly 22 unless your environment uses a custom port.
• Authentication: either username/password or SSH key pair. Prefer key-based auth where possible.
• Directory layout: agree on input folder(s), archive/processed folder, and error folder conventions.
• Access controls: limit the SFTP account to the specific directories needed for reconciliation files.

File formats and naming conventions

• Supported formats: CSV, XLS, XLSX.
• Header row: ensure a consistent header row across files intended for the same report configuration.
• Required columns: date, amount, and at least one identifier (order ID, transaction ID, invoice number, or reference).
• Naming: include report name and date period in file names, e.g., sales_report_2026-06-01_2026-06-30.csv, to avoid processing duplicates.
• Atomic delivery: ensure files are uploaded completely before the system picks them up; use a staging directory and move files atomically into the input folder when ready.

Mapping and derived columns in the reconciliation engine

• Column mapping: tell the reconciliation tool which column is date, which is amount, and which is the identifier.
• Supporting data: upload lookup files (product master, fee schedules, return reports) to enrich primary data where needed.
• Derived columns: create calculated fields for fees, net amounts, or conditional amounts so the reconciliation matches your business logic.

Scheduling and automation options

• Frequency: choose hourly, daily, or period-based schedules depending on reconciliation cadence.
• Delivery behavior: configure post-processing steps such as moving files to an archive folder after successful ingestion or to an error folder if parsing fails.
• Notifications: set email or in-app alerts for failed ingestions or parsing errors so operators can react quickly.

Practical implementation steps

1. Prepare SFTP server and access

• Work with IT or your vendor to provision an SFTP account scoped to the reconciliation input directory.
• Exchange public keys if using SSH key authentication and validate connectivity from the Cointab integration host or designated connector.
• Confirm directory permissions for write and move operations.

2. Standardize files and naming

• Coordinate with upstream teams to produce files in CSV/XLS/XLSX with agreed headers and consistent date formats.
• Define a filename pattern that includes report type and period so automation rules can pick the correct files.
• Recommend a temporary staging path for file assembly followed by an atomic move into the SFTP input directory when the file is complete.

3. Configure a new reconciliation and upload sample files

• In the reconciliation platform, create a new reconciliation profile for the process (for example, 'Sales vs PSP - Monthly').
• Upload sample Side A and Side B files manually to validate parsing and mapping before enabling SFTP automation.
• If multiple files represent the same report format, upload them under the same report configuration so they are processed together.

4. Map columns and create derived columns

• Select the header row, assign the date column, the amount column, and identifier column(s).
• Add supporting data files if needed to enrich records prior to matching.
• Use derived columns to normalize amounts or create net values. For example, create a derived amount that subtracts known fees so Side A net equals Side B net.
• Test the mapping by running a reconciliation on sample data and review matched, partially matched, and unmatched records.

5. Configure SFTP input, schedule, and notifications

• Enter SFTP connection details in the reconciliation profile: host, port, username, auth method, and input directory path.
• Define file name filters or patterns the system should pick up each run.
• Set schedule frequency and retention behavior: archive moved files to a processed folder and retain originals for X days per policy.
• Enable alerts for ingestion failures, parse errors, and missing expected files so operations can respond quickly.

6. Run, review, and iterate

• Trigger an initial automated run during a quiet window and monitor logs for connection, authentication, and parsing issues.
• Review the reconciliation output: confirm matched, partially matched, and unmatched buckets look correct and that skipped records include clear reasons.
• Iterate on mappings, derived columns, or supporting data to reduce partials and unmatched records.
• Document the final configuration and hand over run cadence and exception handling to the finance operations team.

Common mistakes to avoid

• Sending files with varying header rows under the same report configuration; this causes parsing failures.
• Uploading files directly into the input directory before they are fully written; use staging and atomic moves.
• Relying on ambiguous identifiers; ensure at least one stable identifier or implement robust derived matching logic.
• Not configuring retention and archive behavior, which can lead to accidental reprocessing or storage bloat.
• Ignoring parse warnings and skipped records; skipped rows contain important signals about bad or incomplete data.

Key Takeaways

• SFTP-based data automation removes manual uploads and makes recurring reconciliations repeatable and auditable.
• Standardize file formats, consistent headers, and clear filename patterns before enabling automation.
• Accurate column mapping, supporting data, and derived columns are essential to minimize exceptions.
• Use atomic file delivery and directory conventions to avoid partial or corrupt ingestions.
• Monitor ingestion logs and alerts so exceptions are addressed promptly and the reconciliation cadence remains reliable.

Conclusion

Configuring SFTP-based data automation streamlines recurring reconciliations and reduces manual work for finance teams. Start with a small test reconciliation, validate mappings and derived columns, then enable SFTP ingestion and scheduling to scale the workflow. Include monitoring and clear folder conventions to prevent common failures.

Start your 14-day free trial with Cointab. No credit card required. 14-day free trial.