Flat file integration: here’s everything you need to know

As your organization looks to integrate applications, one of the methods at your disposal includes flat files.

To help you evaluate this integration method for any of the scenarios you’re considering, we’ll break down how it works, its use cases, its pros and cons, and more.

But first, let’s align on what a flat file is.

What is a flat file?

It’s any human-readable file that contains specific records and uses a delimiter (e.g., commas) to separate the records’ fields. 

Flat files can take various forms, including comma separated value (CSV) files, text files (TXT), and tab-separated values (TSV) files. They can also store a wide range of records, from customer orders to employee information.

‍

What is flat file integration?

It’s the use of flat files to sync data between applications. This involves exporting a file from one system (“source system”), transforming and validating its data, and then importing it to another system (“destination system”), where the data can be processed and added to the appropriate places.

Flat file integration vs API integration

It’s easy to confuse file-based integrations with a popular alternative option—API-based integrations. Their difference is simply in how the applications are connected. The former uses files to connect applications while the latter uses the applications’ underlying APIs.

Related: The differences between flat file integration and API integration

Examples of flat file integration

We’ll cover a few common internal and customer-facing flat file integrations.

Add newly-signed customers to your accounting system

Your accounting team needs to become aware of new clients quickly so that they can begin the work of adding them into their system and invoicing them with little delay.

To help them do just that, you can implement a flat file integration that works as follows:

‍

1. A flat file is exported from your CRM every 24 hours with all the clients who signed that day. The file also includes specific details on each client, such as the terms of their contracts and their billing information. 

2. The file’s data format is transformed via a script to meet the data model your ERP system accepts.

3. Once transformed, the flat file is imported into the ERP system, where the clients’ data is processed and used to create new client accounts and corresponding invoices.

Keep critical employee records securely stored and easily accessible

As you add and edit employee data in your HRIS, you can back up their information in your file storage system, where it can also be kept secure and easy to access for the appropriate stakeholders.

To facilitate this use case, you can build the following integration:

1. A flat file is exported from your HRIS every 24 hours with key information on your employees, from employees’ full names to their social security numbers to their addresses.

2. The file is imported into your file storage platform, where it’s processed.

3. If there are new employees, new files get created in the file storage system for them; if an existing employee’s information has changed since the last import, their information is updated in the file storage system; and if there are no changes for an existing employee since the last import, their information is left unchanged in the file storage system.

Enable clients to auto provision and deprovision users in your product 

To help clients add and remove users in your product, as well as modify permissions based on changes to a user's role, you can build the following flat-file integration:

1. Once a day, a client’s file is exported from their HRIS with all the employees in their HRIS.

2. The file’s data format is transformed via a script to match the data format of users in your product.

3. The file is imported into your product. New employees in the file are automatically added as users in your product and are assigned the appropriate level of permissions; employees who aren’t included in the file are removed as users in your product; and employees whose information has changed since the last import are updated accordingly in their user profile—which could potentially change their level of permissions.

Related: A guide to automated provisioning

Benefits of flat file integration

Here are just a few reasons to adopt flat file integrations.

Universally accessible

Most applications, regardless of where they’re hosted or the use cases they support, can export and import flat files. As a result, you'll likely be able to rely on flat file integrations across all the  scenarios you’re interested in.

Supports a wide range of data

Flat files can store and sync nearly every type of data you’re interested in—from employee records to financial information. As a result, it can support a broad range of integration use cases.

Syncs data efficiently

Since files can store a high volume of information, they can integrate data in batches efficiently. In other words, whatever your cadence looks like for integrating data, the files can transfer all the information at once.  

Drawbacks of flat file integration

Unfortunately, flat file integrations aren’t without their flaws.

Complex to implement and maintain

Whatever method you use to build and maintain the file integrations—scripts or an integration middleware tool—you’ll need your engineers heavily involved throughout their life cycles. This can prove to be time and resource intensive for them, especially when you need to scale your integrations.

Lack of real or near real-time syncs

In many cases, you’ll need data to move between systems as quickly as possible, whether that’s  routing leads to sales reps, sending escalated tickets to engineering, and so on.

Since flat file integrations can’t sync data faster than a few minutes (at best), it may not support your time-sensitive use cases effectively.

Security risks

While a file gets transferred, it can be vulnerable to data leakage, especially if you aren’t using a measure like Secure File Transfer Protocol (SFTP). Assuming you’re integrating sensitive data, like employees’ or clients’ personal information, your organization can potentially face reputational and financial damage.

Related: How API and SFTP integrations compare

When to integrate with flat files

Given all these pros and cons, you’re likely wondering when it makes sense to integrate systems via files.

The answer largely depends on the applications you’re looking to connect and the specific requirements for the integration. Let’s take a closer look at these factors.

Endpoint accessibility

You might find that the applications you want to connect don’t offer APIs; or they might offer APIs but not the specific API endpoints you care about. In either scenario, you’re left with no option but to integrate the applications through other means, such as files.

It’s also worth noting that even if an application supports the endpoints you care about, the quality of their documentation on those endpoints (and on their APIs more broadly) can be insufficient, out of date, or difficult to find. If you find yourself in any of these positions, it can also be worth using flat files instead of APIs.

Syncing frequency

Since flat file integrations can’t sync data frequently, you’ll need to review your processes and prioritize the ones that sync data once every few hours, days, weeks, etc. for flat file integrations.

While it obviously depends on the nature of your business and how, exactly, your team wants certain processes to run, this can take the form of updating employee information in your HRIS, adding data to your business intelligence platform, revising inventory data in your e-commerce solution, etc.

Simple data syncs

If the files you’re syncing don’t require significant data transformations and their records aren’t likely to change much, if at all, over time, then using flat files can make sense. 

That said, the scenarios that meet these requirements are few and far between. Most types of data consistently evolve in unpredictable ways, and some level of data transformation is generally needed, as applications often use different data formats and schemas.

Build secure and performant integrations at scale with Merge

Merge allows you to add hundreds of integrations to your product by simply building to a single unified API. 

Moreover, using Merge, you can:

• Use webhooks to sync data with your product in real-time
• Enable your customer-facing employees to diagnose, troubleshoot, and resolve clients’ integration issues quickly and independently 
• Save your engineers from maintaining your integrations—as Merge maintains them on your behalf

Learn more about Merge by scheduling a demo with one of our integration experts.