Split and Merge CSV Files with a Free CSV Splitter in Sheets

Introduction

A file is too large for the system that needs it. Or a folder of monthly exports should be one table.

Both tasks are routine, and both can be done in a spreadsheet. Splitting means selecting a subset of rows and writing it to its own tab. Merging means stacking several tabs that share a header.

The methods below use built-in functions, so the result updates when the source changes and every step stays visible.

Split and Merge in One Sheet

Split and merge are two directions of the same job. In a split, one table becomes several. In a merge, several tables become one.

A spreadsheet handles both because a tab is just a range, and a range can be selected, copied or stacked by formula. The only requirement is a consistent header: every part of a split keeps it, and every file in a merge must share it.

Downloading is the final step. File, then Download, then Comma-separated values saves only the active sheet, which suits a split output. A merge stays as a tab until you export it once.

Split by Rows

Suppose the source sits on a tab called Data, with headers in row 1 and records from row 2 down. Decide how many records each output should hold, then create one tab per block.

For 500 records per file, the first tab takes rows 2 to 501 and the second takes rows 502 to 1001. Set the upper bound to the last row of the source, not an open column range, so the formulas stay fast. With 1,000 rows of data in total, the first two blocks look like this:

=FILTER(Data!A2:Z1001, ROW(Data!A2:A1001) <= 501, LEN(Data!A2:A1001) > 0)

=FILTER(Data!A2:Z1001, ROW(Data!A2:A1001) > 501, LEN(Data!A2:A1001) > 0)

The ROW comparison selects the block and the LEN test drops empty rows, so a partial final block ends cleanly rather than trailing blank lines. Add a third tab with the next row window when the source grows.

Each output needs its own header row. Copy the header into row 1 of every part, or reference it with a formula such as =Data!A1:Z1.

Split by Column Value

Splitting by column keeps related records together. A sales export can become one file per region, or a customer list one file per owner.

List the distinct values first so you know which tabs to create:

=SORT(UNIQUE(FILTER(Data!C2:C1001, LEN(Data!C2:C1001) > 0)))

Then give each value its own tab with a filter on that column:

=FILTER(Data!A2:Z1001, Data!C2:C1001 = "Kenya")

Repeat the formula with each value in place of Kenya. The header sits in row 1 of each tab as before.

The list of values is worth keeping on a summary tab. It shows how many groups exist, and a count beside each one flags a value that appears only once, which is usually a typo rather than a real region.

Automate the Split with Apps Script

When the column has many distinct values, creating a tab per value by hand is tedious. This function reads the source tab, groups the rows by one column and writes a tab for each group:

function splitByColumn() {
  const source = SpreadsheetApp.getActiveSheet();
  const book = source.getParent();
  const column = 3;
  const values = source.getDataRange().getValues();
  const headers = values.shift();
  const groups = {};
  values.forEach(function (row) {
    const raw = String(row[column - 1]).trim();
    if (raw === "") {
      return;
    }
    const base = raw.replace(/[\\/:*?\[\]]/g, " ").slice(0, 80);
    let name = base;
    let suffix = 2;
    while (groups[name] !== undefined || book.getSheetByName(name) !== null) {
      name = base.slice(0, 76) + " " + suffix;
      suffix += 1;
    }
    groups[name] = [];
    groups[name].push(row);
  });
  Object.keys(groups).forEach(function (name) {
    const sheet = book.insertSheet(name);
    sheet.getRange(1, 1, groups[name].length + 1, headers.length)
      .setValues([headers].concat(groups[name]));
  });
}

Change the column number to match the field to split on, then run the function from the Apps Script editor. The name cleanup removes characters that a sheet name cannot contain and caps the length, so an unusual value does not stop the run.

The function adds tabs and never deletes them. Clear the old output tabs first, or the second run fails when it tries to create a name that already exists.

Merge Files with Append Rows

The quickest merge is built into the import dialog. Import the first file into an empty spreadsheet, then import each further file with the same settings and choose Append rows to current sheet.

Sheets places the new rows underneath the existing ones and keeps a single header from the first import. The files must have the same columns in the same order, because the append does not match headers by name.

This is the right method for a one-off: a folder of monthly exports that will not change again. It is a poor fit for a live report, because each new file means another manual import.

Merge Tabs with VSTACK

For a merge that stays current, import each file as its own tab and stack the ranges with a formula. VSTACK appends one range below another, in the order you list them:

=QUERY(VSTACK(Jan!A2:Z1001, Feb!A2:Z1001), "where Col1 is not null", 0)

VSTACK alone would carry the empty rows at the bottom of each range into the result. The QUERY wrapper drops them, and the final argument of zero tells QUERY that the stacked block has no header row of its own.

List the tabs in the order you want the rows to appear. Add a third range for a third file, and keep every range on the same columns so the values stay under the right headings.

If the tabs hold the same columns in a different order, the stack will line up the wrong values. Reorder the columns first, or import each file with a consistent template.

A Worked Example

Suppose a tab holds five orders across two regions:

A split by region produces two tabs. The Kenya tab holds orders 1001, 1003 and 1004, and the Uganda tab holds 1002 and 1005. Each tab keeps the header row and can be downloaded as its own CSV.

A split by two rows per file produces three tabs: the first holds 1001 and 1002, the second holds 1003 and 1004 and the third holds 1005 alone. The last tab is shorter than the others, which is expected and is why the LEN test matters.

Merging the two region tabs with VSTACK returns the original five rows in region order, with no blank rows and no repeated header.

Troubleshooting Common Issues

• A filter returns nothing: check the value for a trailing space or a different case. Kenya and kenya are different values, so trim and standardise the column before filtering.
• A merge shifts the values: the tabs have a different column order or a different number of columns. Align the headers before stacking.
• Blank rows in a merged tab: VSTACK carries the empty space at the bottom of each range. Wrap it in QUERY with a not-null condition, as above.
• A script stops on a sheet name: sheet names cannot contain a colon, a slash, a backslash or a question mark. Clean the value before using it as a name.
• The file will not import: a CSV with more rows than the spreadsheet allows cannot be split after import. Split it before the upload.

Beyond a Spreadsheet

Spreadsheet splitting is comfortable up to the row and cell limits, and it keeps every output editable. The limits are real, though: a spreadsheet holds 10 million cells in total, and splitting into more tabs does not raise that ceiling.

When an export is too large to open at all, the split has to happen before the import. Do it on your own machine with a text editor or a command line split, bring in the parts, and the workflow templates cover the reconciliation steps that usually follow a split.

Splitting is a size problem. The next problem is usually quality: the same customer spelled differently across two parts, or a region value that means the same thing under two names. Flookup standardises those values before the parts are compared or merged.

One Big File Should Not Be a Blocker

Files arrive too large and too many, and reshaping them should not need a custom program. A FILTER selects a block of rows or a single column value, and VSTACK brings the parts back into one table.

Use the append option for a one-off merge and the formula route for a report that refreshes. Keep the header consistent and the row bounds tight, and the sheet stays fast.

When the parts also need matching and cleaning, Flookup Data Wrangler runs those steps inside the same spreadsheet.

Frequently Asked Questions