Aa
Contrast
Text size

Blog / Data In

The Copy Paste Nightmare

22 May 2026

Five years of monitoring data that could not be analysed as a time series, because every phase used different units, schemas and column names.

You have collected the data and checked it. Now you need to merge it with last year's dataset, with your partner's records and with three monitoring periods that used slightly different schemas. You open Excel and start copying. Three days later you are still not done.

Environmental data integration is where clean data from several sources meets the fact that nobody designed these systems to talk to each other. The chaos does not announce itself until you are already buried in it.

What it looks like

A coastal water quality programme I know had five years of monitoring data in three phases. Phase one used one database schema. Phase two added variables and changed the coordinate format. Phase three brought in a partner organisation with a schema of its own. The science was continuous, but the data was not.

Every time they needed a multi year trend, for an annual report, a grant application or the ministry review that decided their next funding, one senior data manager spent two or three weeks harmonising the datasets by hand. Salinity was in PSU in phase one and parts per thousand in phase two. Temperature was called "temp_C", "temperature" or "WaterTempCelsius". Timestamps followed three time zone conventions, because each phase used different field equipment.

She had built a personal system of correction scripts, lookup tables and memory about which quirk lived where. It worked, and it depended entirely on her being there. When she took extended sick leave, the quarterly report was six weeks late, not because the analysis was hard but because nobody else could navigate the consolidation. The director put it simply: "Our data exists. We just can't use it."

Four places where it goes wrong

Manual copy and paste becomes the standard procedure. With no automatic integration layer, data moves by human hands: copy from the field database, paste into the analysis sheet, reformat the columns, and repeat for every source, period and partner. Each step can go wrong. A column lands in the wrong order, a unit conversion is missed, a filter is applied in one version and forgotten in another, and the error spreads silently until something looks wrong in a presentation. Manual integration also does not scale. One project and one year is manageable. Three projects, four partners and five years is a full time job that produces data ready for analysis, not analysis.

Versions multiply. The file is called data_final.csv, then data_final_v2.csv, then data_final_v2_corrected_FOR_REPORT.csv, and then something worse. Each exists because someone needed a change and did not want to overwrite the original. Nobody is sure which one is current. When a partner asks for your dataset, which version do you send? When a funder asks you to reproduce a number from the last report, which file did it come from? Version chaos makes your data unreliable by accident.

Partner data refuses to fit. Your organisation has standards, and so do your partners. A university group, a government network or an NGO sends you a piece cut for a different puzzle: other variable names, other species lists, other spatial reference systems, other time resolutions and quality flags with other meanings. Every new partnership creates a new harmonisation task, and if it is not systematised it is redone from scratch at every update.

Historical data is locked in obsolete formats. Your oldest data is often the most valuable, because long time series cannot be replaced. It is also the data trapped in an Access database from 2007, a spreadsheet with macros that only run on an old Windows version, or the format of software that no longer exists. I have watched teams find out that they could not run the climate trend analysis that would have made their grant application compelling, because the first decade of data could not be opened. The data was not corrupted. The ability to use it had quietly expired.

What fixes it

Data from any source, any period and any partner should flow into one consistent, searchable system automatically.

The foundation is an automated pipeline for extracting, transforming and loading data. You build one pipeline per source that does the conversion for you. When the partner sends a CSV in its own format, the pipeline converts it to your schema, standardises the units, checks the names against your taxonomy and loads it into your database. No manual steps, and no manual errors. When updated data arrives next quarter, the pipeline runs again and treats it the same way. Python with pandas, well structured R workflows or a tool such as Apache NiFi can do this, and you build it once.

Keep a single source of truth. One canonical database and one current version, not a folder of CSVs. A well managed PostgreSQL database with documented access is a practical environmental database management system and covers most of what environmental organisations need. A correction is made in one place, a partner receives current data from one place, and you can reproduce last year's number by running the same query against the same data.

Migrate the history, once. Identify the obsolete formats, extract the data into open standard ones, document the transformations and archive the originals. Then bring the old data into your single source of truth. A permanent liability becomes a permanent asset, and your long time series becomes something you can query.

Write a harmonisation protocol for each partner. Record what their field names map to in your schema, which unit conversions apply, how their quality flags translate and what validation their data goes through. When they send an update, the protocol handles it. When a new person takes over, they run the protocol instead of working it out from scratch.

The programme built pipelines for each of its three historical phases and for its partner's feed, and documented the data manager's personal scripts and lookup tables so that they could be run by anyone. Its next multi year analysis ran in hours instead of weeks. When she took leave the following year, the quarterly report went out on time, because the integration no longer lived in one head.

The pattern

Integration failures feel like a coordination problem: partners should standardise, earlier teams should have used one schema, someone should have planned the architecture. They are an infrastructure problem that looks like a history problem. Messy legacy data and different partner formats are a fact of life in environmental research, because organisations run across years, funding cycles and changes of staff. The question is not whether your history is messy. It is whether you have built infrastructure that absorbs the mess and delivers consistency on the other side.

Manual integration cannot scale, because every new source makes the problem bigger. With pipelines every new source gets one, and it runs for good at no extra effort. Multi year analysis becomes possible instead of heroic, historical trends become visible and your dataset becomes a scientific asset. Data you cannot combine is data you cannot fully use. Fix integration and you find you have science you did not know you had.

Escaping Data Chaos

Also on LinkedIn

This article was first published in the Escaping Data Chaos series. The stories in the series are composites of real projects, anonymised.

Read it on LinkedIn

Recognise this problem?

One conversation to find out if we can help.

Book a free 15 minute call