How We Handle Client-Side CSV Merging Without Server Processing

When merging CSV files directly in a web browser, juggling mismatched columns and quoted cells presents significant challenges. Filetools recently rolled out CSV merge/split/transpose tools for the browser, but the trickiest part wasn't parsing the CSV format - it was dealing with real-world data without relying on a server.

The issue lies in the messy nature of CSV files found in the wild. Columns often don't line up properly. A cell value may contain a comma, but that comma is surrounded by quotes. Additionally, headers can be case-sensitive in some parts of the file and not in others.

In a server-based environment, a fast library can handle this efficiently while streaming the results. However, in a browser setting, the merge operation must remain consistent and predictable from the initial load. To tackle this, the approach is to:

1. Allow users to specify which columns to merge on (e.g., "id" or "email").

2. Perform a case-insensitive initial match, followed by an exact comparison.

3. If no match is found, notify the user and provide a selection from the detected headers.

4. Follow RFC 4180 strictly when handling quoted fields - a quote inside a quoted field is escaped as a double quote.

5. The csv-parse library (MIT license) is used and integrated into the site, similar to how libraries for PDF and ZIP files are managed.

Column order is crucial. The merge operation adheres to the column order from the first file, appending any new columns from subsequent files. This ensures a deterministic and reproducible outcome.

The importance of this client-side approach becomes clear when dealing with data that represents real records or transactions. With server-based tools, the assumptions are hidden - you upload, they merge, and you download. If a merge fails, the user receives an error message with no insight into the cause. Client-side, however, users can see the detected headers, approve or correct them, and retry immediately. This transparency is vital when handling sensitive data.

This week, Filetools introduced merge, split (by row count or column value), transpose, and comparison tools. The deterministic and transparent approach used for these operations applies across the board. Looking ahead, the team is considering tackling more challenging CSV operations, such as row-level filtering and conditional formatting, which currently require downloading additional tools or writing scripts.