From Manual XML Conversion to a More Efficient SFTP-Based Workflow
Project Overview
A client approached me with a requirement to convert an XML file into Excel and then convert the updated Excel data back into XML.
The initial requirement was relatively straightforward: create two scripts that would allow the client to upload an XML file, generate an Excel file from it, make changes in Excel, and then generate an updated XML file.
As the project progressed, however, the client’s workflow became more sophisticated. The XML files were being exchanged through an SFTP server, and the client wanted to reduce the amount of manual work involved in moving, editing, converting, and returning those files.
This turned the project from a simple XML conversion task into a broader XML/Excel/SFTP workflow automation project.
The Initial Requirement
The client first contacted me looking for a way to convert XML data into Excel and then convert the modified Excel file back into XML.
The proposed solution consisted of two scripts:
- XML → Excel
- Excel → XML
The client had web hosting available, so the initial plan was to install the scripts online and provide separate links for the conversion process.
The basic workflow was designed to be simple for the end user:
Upload XML → Download Excel → Edit Excel → Upload Excel → Download XML
The client initially considered whether a web interface could eliminate the need for Excel, but based on the workflow at that stage, using Excel was considered more user-friendly and practical.
The Workflow Became More Complex
Several months later, the client returned because the original converter was working well, but they wanted to improve the overall process.
The client explained that the actual workflow involved:
- XML files being pulled from an SFTP server
- XML being converted into Excel
- New information being added or existing information being updated in Excel
- Excel being converted back into XML
- The resulting XML being uploaded back to the SFTP server
The goal was to make this entire process smoother and more automated.
This was an important change in scope because the challenge was no longer just XML conversion. The system now had to fit into an existing SFTP-based business workflow.
Designing the Automation Workflow
The client proposed using a dedicated machine onsite that could remain running continuously.
The proposed workflow was roughly:
SFTP Server
↓
Retrieve XML
↓
Convert XML to Excel
↓
Place Excel in Shared/Local Folder
↓
Client Updates Excel
↓
Move Updated Excel to Completed Folder
↓
Convert Excel Back to XML
↓
Upload XML to SFTP
The client also considered using watched or “hot” folders so that files could automatically move through the workflow once users completed their changes.
This approach provided a practical way to connect the remote SFTP process with the client’s local network and Excel-based workflow.
Working Around SFTP Restrictions
One of the technical considerations was that the SFTP server was restricted to an IP address associated with the client’s site.
Because of this restriction, the automation could not simply be deployed and connected to the SFTP server from an arbitrary hosting environment.
The client explained that the SFTP access was locked to the client’s IP, which meant the processing component would need to run on a remote or onsite system that had the appropriate network access.
The solution therefore needed to separate the conversion logic from the final onsite environment.
The plan was to develop the processing script so that it could be tested and then run on the client’s remote system.
Development and Delivery
After the client received approval to move forward, the automation project was started.
The client specifically confirmed approval to proceed, and I began working on the requested automation.
The delivered solution was intended to support the client’s XML/Excel processing workflow while allowing the final SFTP and local-network components to be configured within their own environment.
After delivery, the client continued working on the onsite portion of the workflow. They explained that they would have a dedicated computer onsite and could provide remote access so that the remaining workflow could be completed.
The Result
The project evolved through several stages:
Stage 1 — XML Conversion
The original requirement was to provide a practical way to convert XML data into Excel and back into XML.
Stage 2 — Hosted Conversion Tools
The conversion scripts were planned for installation on the client’s hosting environment, giving the user simple upload/download functionality.
Stage 3 — SFTP Integration
The client’s real-world workflow introduced SFTP as the source and destination for the XML files.
Stage 4 — Local Processing
Because of the SFTP IP restrictions, the workflow needed to operate within the client’s network environment.
Stage 5 — Workflow Automation
The client explored using a dedicated onsite machine and watched folders to reduce manual file handling.
This progression demonstrates how a relatively simple data-conversion requirement can evolve into a customized business automation workflow once the complete operational process is understood.
Key Technical Challenges
The project involved more than simply converting between two file formats.
The main challenges included:
- Maintaining the XML-to-Excel conversion workflow
- Converting modified Excel data back into the required XML structure
- Working with an SFTP-based data exchange process
- Handling an SFTP server restricted by IP address
- Designing a workflow suitable for a dedicated Windows machine
- Connecting local folders with the conversion process
- Considering automated file monitoring and processing
- Keeping the process simple enough for the end user
The project also required coordination between the conversion software, the client’s hosting/network environment, SFTP access, and the local workflow.
Why Workflow Design Matters
One of the most important lessons from this project was that solving the immediate technical problem is only part of the job.
The first requirement was essentially:
“Convert XML to Excel and Excel back to XML.”
But the client’s actual business process was closer to:
SFTP → XML → Excel → User Updates → Excel → XML → SFTP
Once the complete workflow was understood, the solution could be designed around the client’s actual operation rather than treating XML conversion as an isolated task.
This is often the difference between a basic utility script and a solution that fits into a real business process.
Conclusion
What started as an XML-to-Excel conversion project developed into a broader SFTP and workflow automation project.
The client initially needed a straightforward conversion tool, but after using the converter, they returned with a larger requirement to make their XML update process more efficient and automated. The client specifically reported that the converter “works great” before discussing the next stage of automation.
The project is a good example of how understanding the complete data flow can reveal opportunities for automation beyond the original request.
For businesses that regularly exchange structured XML data through SFTP while relying on Excel for manual updates, a customized conversion and automation workflow can significantly simplify the process and reduce repetitive file-handling steps.