File and database integration: automate CSV, FTP, SQL and XML data flows

Not every system has a modern API. Suppliers send price lists as CSV files, logistics partners drop files on an SFTP server, and older line-of-business systems keep their data in a SQL database that nothing else can see. Someone ends up downloading, reformatting and importing those files by hand, often at the same time every day.

File and database integration automates that work, so data from files, folders and databases flows into the rest of your business on its own.

What is file and database integration?

File and database integration is the automatic exchange of data between flat files, file servers or databases and your core business systems. Zynk connects:

• Files and spreadsheets such as CSV, Excel, XML and JSON
• File servers over FTP and FTPS, SFTP, or both through the Remote File Transfer connector
• Databases such as SQL Server, SQLite and any ODBC-compliant database through ODBC
• Email, so inbound messages and attachments can start a workflow with the Email connector

On the other side sit the systems you run the business on, such as Sage 200, Sage 50, Xero, QuickBooks, NetSuite and Shopify.

Why manual imports and file handling break down

File-based processes tend to start small and become critical without anyone noticing:

• Repetitive work. Staff spend time each day downloading, opening, cleaning and re-uploading the same kinds of file.
• Formatting errors. A changed column, date format or delimiter breaks an import, and the problem is often found after the bad data has gone in.
• Missed files. If the person who runs the import is away, files sit on the FTP server and the data is late.
• Legacy systems left out. Data trapped in an older database can't feed accounting, ERP or CRM, so it is re-keyed or ignored.
• Security gaps. Files are moved over unsecured connections or emailed around as attachments.

How file and database integration works with Zynk

Zynk uses XML as its core data format, so every file or query result can be converted into a common structure and mapped to any system. A typical setup looks like this:

1. Collect the data. Download files from FTP, FTPS or SFTP, read a folder, process inbound emails, or run a query against SQL Server, SQLite or an ODBC database.
2. Convert and transform. Turn CSV, Excel and JSON into XML, reshape it with reusable XSLT stylesheets, and merge, split or filter documents without writing custom code.
3. Deliver it. Import the result into your accounting, ERP, CRM or ecommerce system, or export data back out as CSV, XML or JSON for partners and other applications.
4. Tidy up and schedule. Rename or delete remote files once they are processed, keep directories in sync, and run the whole workflow unattended on a schedule.

Repeater tasks loop through every file in a folder, every row in a spreadsheet or every record in a query, so the same workflow handles one file or hundreds.

Common uses

• Automate CSV imports of bank, supplier or partner data into your accounting system
• SFTP integration with logistics providers, marketplaces or trading partners who exchange files rather than API calls
• SQL Server integration to export query results into other systems, or load data into SQL Server for reporting
• XML integration to standardise inbound data from different sources into one structure, including EDIFACT messages for EDI integration
• Legacy system integration, connecting older databases and file exports to modern ERP and ecommerce platforms

What to look for in file and database integration software

• Support for every format you receive, including CSV, Excel, XML and JSON
• Secure transfer over SFTP or FTPS rather than plain FTP
• Database connectivity through native connectors and ODBC, not one-off scripts
• Transformation tools for mapping and reshaping data without development work
• Scheduling and repeaters, so recurring files are processed without anyone watching
• Logging and error alerts, so a malformed file is flagged instead of failing silently
• The ability to connect the same data to accounting, ERP, CRM and ecommerce systems on one platform

Get started

File and database workflows are usually tailored to the formats and systems involved. See all integrations to check the systems you use, browse templates for pre-built connections, or talk to our team about your files and databases.