How to split large Excel files in Node.js: a beginner's guide

You have an Excel file that is too large to open comfortably, import or email, and you need it as several smaller files, each with the same header row at the top. In Node.js that takes one short script. This guide builds it step by step, then gives you the version to use when a file is so large that reading it all at once runs out of memory.

What you need

• Node.js installed. Any current long-term support release works.
• A basic idea of how to run a JavaScript file from the terminal.
• The Excel file you want to split. The examples call it input_file.xlsx.

Step 1: set up the project

Create a folder for the project and start a Node.js project inside it:

mkdir split-excel
cd split-excel
npm init -y

Then install SheetJS, the library that reads and writes Excel files. Install it the way the SheetJS installation guide says, from its own CDN. The copy on the public npm registry stopped at version 0.18.5, so npm install xlsx gives you an old release. Copy the install line from the guide, which always carries the current version. It looks like this:

npm install https://cdn.sheetjs.com/xlsx-0.20.3/xlsx-0.20.3.tgz

Step 2: write the script

Create a file called split_excel.js in the folder and paste this in:

// split_excel.js
const fs = require('fs')
const path = require('path')
const XLSX = require('xlsx')

function splitExcelFile(filePath, rowsPerFile = 1000, outDir = 'output') {
  if (!fs.existsSync(filePath)) throw new Error(`File not found: ${filePath}`)

  // cellDates keeps dates as dates instead of serial numbers
  const workbook = XLSX.readFile(filePath, { cellDates: true })
  const sheetName = workbook.SheetNames[0]
  const rows = XLSX.utils.sheet_to_json(workbook.Sheets[sheetName], {
    header: 1, // an array per row, so the header is row 0
    defval: '', // keep empty cells, so columns do not shift
  })
  if (rows.length < 2) throw new Error('The first sheet has no data rows')

  const [header, ...data] = rows
  const baseName = path.parse(filePath).name
  fs.mkdirSync(outDir, { recursive: true })

  const totalFiles = Math.ceil(data.length / rowsPerFile)
  for (let i = 0; i < totalFiles; i++) {
    const chunk = data.slice(i * rowsPerFile, (i + 1) * rowsPerFile)
    const sheet = XLSX.utils.aoa_to_sheet([header, ...chunk], { cellDates: true })
    const book = XLSX.utils.book_new()
    XLSX.utils.book_append_sheet(book, sheet, sheetName)

    const outFile = path.join(outDir, `${baseName}_part_${i + 1}.xlsx`)
    XLSX.writeFile(book, outFile, { compression: true })
    console.log(`Created ${outFile} (${chunk.length} rows)`)
  }
  return totalFiles
}

const [, , input = 'input_file.xlsx', rows = '1000'] = process.argv
try {
  splitExcelFile(input, Number(rows))
} catch (err) {
  console.error(err.message)
  process.exit(1)
}

What each part does:

• readFile with cellDates: true opens the workbook and keeps date cells as dates. Without it, dates come back as serial numbers such as 45583, and the split files show numbers where the dates were.
• sheet_to_json with header: 1 returns each row as an array, so the first array is the header row.
• defval: '' keeps empty cells. Without it, a blank cell in the middle of a row is dropped and every value after it moves one column to the left.
• The loop takes the data in slices of rowsPerFile rows, puts the header back on top of each slice, and writes each slice as a new workbook in the output folder.
• compression: true makes the output files smaller. SheetJS writes uncompressed files unless you ask.

Step 3: run it

Put your Excel file in the same folder, then run the script with the file name and the number of rows you want in each file:

node split_excel.js input_file.xlsx 10000

On a file with 25,000 data rows it prints:

Created output/input_file_part_1.xlsx (10000 rows)
Created output/input_file_part_2.xlsx (10000 rows)
Created output/input_file_part_3.xlsx (5000 rows)

Each file opens in Excel with the original header on the first row. If the file name is wrong, the script stops with "File not found" instead of a long error.

When the file is too large to load at once

The script above reads the whole workbook into memory before it writes anything. That is fine for most files. For a very large one, the process can run out of memory, or take so long that it looks stuck.

We measured both approaches on the same test file: 300,000 rows, five columns, 62 MB, on a laptop running Node.js 22. The script above peaked at about 990 MB of memory and took 11.6 seconds. The streaming version below peaked at about 220 MB and took 4.8 seconds. Its memory stays roughly flat as the file grows, because it holds one row at a time instead of the whole sheet.

Measured on one test file

Streaming holds one row, not the whole sheet

	Read the whole file (SheetJS)	Stream row by row (ExcelJS)
Peak memory	About 990 MB	About 220 MB
Time	11.6 seconds	4.8 seconds
As the file grows	Memory grows with it	Memory stays roughly flat
Script length	41 lines	60 lines
Use it for	Most files, and quick changes	Very large files, or a small server

Streaming needs a different library, ExcelJS, which can read and write a workbook one row at a time. Its documentation covers the streaming reader and writer. Install it in the same folder:

npm install exceljs

Then create split_excel_stream.js:

// split_excel_stream.js
const fs = require('fs')
const path = require('path')
const ExcelJS = require('exceljs')

async function splitLargeExcelFile(filePath, rowsPerFile = 100000, outDir = 'output') {
  if (!fs.existsSync(filePath)) throw new Error(`File not found: ${filePath}`)
  fs.mkdirSync(outDir, { recursive: true })

  const baseName = path.parse(filePath).name
  const reader = new ExcelJS.stream.xlsx.WorkbookReader(filePath, {
    sharedStrings: 'cache', // resolve text cells
    styles: 'cache', // needed to recognise dates
    hyperlinks: 'ignore',
    worksheets: 'emit',
  })
  let header = null
  let outFile = ''
  let writer = null
  let sheet = null
  let part = 0
  let count = 0

  async function closePart() {
    if (!writer) return
    sheet.commit()
    await writer.commit()
    console.log(`Created ${outFile} (${count} rows)`)
  }

  async function openPart() {
    await closePart()
    part += 1
    count = 0
    outFile = path.join(outDir, `${baseName}_part_${part}.xlsx`)
    writer = new ExcelJS.stream.xlsx.WorkbookWriter({ filename: outFile })
    sheet = writer.addWorksheet('Sheet1')
    sheet.addRow(header).commit()
  }

  for await (const worksheet of reader) {
    for await (const row of worksheet) {
      if (!header) {
        header = row.values // the first row is the header
        continue
      }
      if (!writer || count === rowsPerFile) await openPart()
      sheet.addRow(row.values).commit() // written to disk, then released
      count += 1
    }
    break // first sheet only
  }
  await closePart()
  return part
}

const [, , input = 'input_file.xlsx', rows = '100000'] = process.argv
splitLargeExcelFile(input, Number(rows)).catch((err) => {
  console.error(err.message)
  process.exit(1)
})

How it differs from the first script:

• The reader is a stream. WorkbookReader hands you one worksheet, then one row, at a time. styles: 'cache' lets it recognise date cells, and sharedStrings: 'cache' turns text cells back into text.
• Each row is committed as soon as it is added. commit() writes the row to the output file and releases it, so memory does not grow with the file.
• A new output file opens every rowsPerFile rows, with the header written first.

Run it the same way:

node split_excel_stream.js input_file.xlsx 100000

Which one to use

Start with the first script. It is shorter and easier to change. Move to the streaming version when loading the file takes more memory than the machine can spare, or when the script runs on a small server next to other work.

Common problems

• Dates come out as numbers. Read with cellDates: true, as the first script does. In the streaming version, keep styles: 'cache'.
• Values move one column to the left. A blank cell was dropped. Keep defval: '' in sheet_to_json.
• You need a different sheet. Both scripts split the first sheet only. In the first script, change SheetNames[0] to the position of the sheet you want.
• The script slows down your web server. Splitting a file is CPU work, and in a server process it holds up every other request. Run it in a worker thread or a separate process. How to move CPU-heavy work off the Node.js event loop shows how.

The code

The original script is in our public repository, boffincoders/excel-splitter. The versions on this page add the output folder, the command-line arguments, date and blank-cell handling, a clear error when the file is missing, and the streaming option. Both were run against the test files described above before they went on this page.

If splitting files is one step in something larger, such as a nightly import, a report that feeds another system, or spreadsheets that customers upload every week, the script is the easy part, and the work is making it run unattended: retries when a file arrives half written, a log that shows which rows failed, and an alert when it stops. That is the kind of job Node.js developers by the month take on inside your own repository, and it is usually a few days of work rather than a project.

---

Originally published on Boffin Coders. If you want a job like this running unattended inside your own repository, you can hire Node.js developers by the month.