Project Overview
What is AutoSync?
AutoSync is a lightweight automation tool I developed to solve a real operational problem in an audit and compliance workflow. The team regularly receives large volumes of files through an SFTP server using WinSCP. These files must be downloaded, tracked, stored on OneDrive, and shared with team members daily.
Before AutoSync, the entire workflow was manual, time-consuming, and error-prone. AutoSync automates the complete chain — from SFTP synchronization to reporting and email notifications.
My Role
- End-to-end product design and development
- Requirements gathering with audit team
- Python automation scripting
- WinSCP CLI integration
- Excel report generation
- Configuration and documentation
Tech Stack
- Python
- WinSCP (CLI)
- Pandas & OpenPyXL
- JSON configuration
- SMTP email automation
- Git & GitHub
The Problem
The audit team downloaded dozens of files daily from an SFTP server. This process involved opening WinSCP, navigating multiple folders, manually downloading files, copying them into OneDrive, updating an Excel tracking sheet, and notifying the manager.
This workflow was not just inefficient — it introduced real operational risks.
- Files were sometimes missed or downloaded twice
- There was no reliable way to detect updated vs unchanged files
- Manual Excel tracking was inconsistent
- Human error created audit traceability gaps
- The process took 1.5–2 hours every day
The core question that triggered this project was simple: “Can we automate all of this?”
How I Approached the Problem
Instead of making assumptions, I began by observing the real workflow and speaking with the person who performed this task daily. I asked them to demonstrate the exact folder structure, naming conventions, and how they currently tracked new files.
- Mapped WinSCP remote directories
- Reviewed OneDrive folder layout
- Documented manual steps and pain points
- Identified failure points and inefficiencies
Once I understood the full workflow, I broke the problem into smaller, testable automation stages rather than building a monolithic script.
The Solution
AutoSync automates the entire workflow using a configuration-driven architecture.
- Connects to SFTP using WinSCP CLI
- Synchronizes remote folders to a local directory
- Scans all files and extracts metadata
- Compares with previous runs to detect changes
- Categorizes data into New Data, Old Data, and New Files
- Generates clean Excel reports
- Sends automated email notifications
The entire tool is driven by a single config.json file, allowing credentials, folder paths, and settings to be updated without touching the code.
Why I Designed It This Way
Excel Output
The audit team required Excel reports because spreadsheets are the standard format for documentation and audit evidence. The output includes clearly structured sheets for New Data, Old Data, and Newly Added Files.
Config-First Design
Credentials, folder paths, and environment settings are never hardcoded. This improves security, flexibility, and long-term maintainability.
WinSCP CLI
Python SFTP libraries were slower and unreliable for large directories. WinSCP was already trusted by the team and provided faster, more stable synchronization.
Clear Data Categorization
Separating Old Data, New Data, and New Files makes audit reviews simple and provides a transparent change log.
Detailed Workflow
1. SFTP Synchronization
A WinSCP command file is auto-generated and executed to log into the SFTP server, navigate to the target directory, and synchronize all changed files into a local folder.
2. Folder Scanning
The Python script recursively scans the local folder, extracts file names, paths, and last-modified timestamps, and stores them in a structured DataFrame.
3. Change Detection
The script compares the current run with the previous run to identify new files, modified files, unchanged files, and potential duplicates.
4. Excel Report Generation
Three structured sheets are generated: New Data, Old Data, and New Files. This format provides an audit-ready change log.
5. Email Notification
The final Excel report is automatically emailed to the manager and team, eliminating the need for manual status updates.
Challenges Faced
- Timestamp normalization between Windows and SFTP
- Handling duplicate filenames with different timestamps
- Preventing Excel format mismatches
- Validating malformed JSON configuration files
- Structuring the project for GitHub release
Impact
Manual effort dropped from 1.5–2 hours per day to roughly 5 minutes. After automation, no files were missed, and the reporting process became standardized and predictable.
What I Learned
- Understanding the manual process is critical before automating
- Small tools can deliver massive business value
- Config-driven design increases product longevity
- Clear documentation improves adoption
- Reliability matters more than cleverness
Future Improvements
- Automatic upload to OneDrive / Google Drive
- GUI interface for non-technical users
- Deleted file detection
- Detailed logs and dashboards
- Standalone EXE packaging
- Multi-SFTP support
- Cloud-based deployment
Closing Summary
AutoSync started from a simple request — “Can we automate this repetitive process?” By understanding the workflow deeply, speaking with the people doing the work, and designing a robust, configuration-driven solution, I built a tool that now runs reliably every day.
It saves time, reduces errors, improves audit traceability, and standardizes an operationally critical process. This project reflects real-world problem-solving, product thinking, and end-to-end ownership.