Ask HN: How would you chunk a large Excel file?

It's Excel, so just write a macro?

That's only 20 cut and paste operations? I would certainly do that manually before trying to code it. And I'm a proficient coder, and not a proficient Excel user.

If it had 100k rows, I'd be out of my depth, so I'd hit google.

Bet it took you more time.to write that code than if you'd done it manually.

If you actually had to do it 'properly' there are actually a ton of options:

  - do it old school with a VBA macro
  - use the newer js macro stuff
  - xslx files are just a zip file of XML so could just do it in pretty much any language

I’m not at my machine to whip up an example but this is an ideal use case for pandas. You can read or stream the excel file and split it in probably <20 lines of python.

Not knowing what the data is like; I'd save it in a comma- or tab-separated text format. From there, it's just a few lines of bash.

This is so quaint. I love it.

Not even going to ask AI. Waiting for a bash one-liner before OP reminds us they are on corporate Windows machine.

Write a script. I know there are decent api for excel and files.

I dont know the api or recommended scripting language. This would be a good case for chatgpt or equivalent type task. Enough to get started.

edit: I asked chatgpt, it recommended python and 'pandas' for interacting with excel

    python
    import pandas as pd
    # Load the data from an Excel file, assuming headers are in the first row by default
    data = pd.read_excel('path/to/your/file.xlsx')
    # Define the number of records per chunk
    chunk_size = 500
    # Split the data into chunks and write each chunk to a new Excel file
    for i in range(0, len(data), chunk_size):
        # Extract the chunk of data based on the current index range
        chunk = data.iloc[i:i + chunk_size]
        # Write the chunk to a new Excel file, including headers as column names
        chunk.to_excel(f'output_{i // chunk_size + 1}.xlsx', index=False)

I asked about the first 'row', and it claims panda includes that in each chunk, but I don't know about that. It's at least a place to start to iterate from. Would need to iterate further with real code/tests.

I would use R. Phoneposting now but something like

  library(tidyverse)
  library(readxl)
  library(writexl)
  read_excel("file.xlsx") %>%
    group_by(group_id =     row_number() %/% 20) %>%
    group_walk(~ write_xlsx(.x, paste0("file_", .y, ".xlsx")))

edit: updated to write xlsx instead of csv

It's been well over a decade since I last dealt with Excel, but I remember you could actually query the data with SQL without opening the file, like you would with any flat-file db. If the size is the problem. It was poorly documented but I'd done it a few times and it worked really well. The best part being it was simple, fast and worked even with locked files. Otherwise I don't understand the question.

The C# interop API will surely work, but I wouldn't claim it's user-friendly.

I would strongly consider dumping rows into SQL, for more "natural" selection.

[1] https://learn.microsoft.com/en-us/dotnet/api/microsoft.offic...

Not to be that guy, but this is the kind of task that LLMs are great at assisting with.

Seems like a 30 second LLM prompt for a Python script.

With something in Excel that needs to chunk, Python is good, but also Go and Rust are good for these situations. ChatGPT is a good starting point to build out some boilerplate.

For readable/not binary files, there are standard tools like split, awk, etc.

VBA macro in the original excel file.

You can use my SQL spreadsheet app: https://superintendent.app

I had a similar problem at work where I needed to do some formula on a 5 GB CSV file. Excel can't handle more than 1M rows. Database through command line is too clunky. I did try to split the CSV into multiple files before but using formula on top of multiple files isn't easy. Eventually I built a Desktop GUI wrapper on SQLite, and it grew into Superintendent.app (now powered by DuckDB).

The newest version supports "Ask AI", which can be used for "Ask your CSVs anything" and "Ask AI to write/rewrite SQLs for you". It has been pretty fun to use and tell AI to "Expand *", "format all dates to something like May 3, 2024", and etc.

[dead]

Can you confirm that said file is purely static data? Chunking a file like that is one thing. Handling one with cell references is a different animal.

I don't understand what you mean by easier way, because it's always going to be a script. Unless you mean some low code/no cool tool that was already tailor made to do this.

If you're worried about data not fitting in memory, then stream the file. It seems like the Java API has support for this, surely other languages do too.

convert to csv, Next is simple text processing.Final ,convert csv to excel

This is a classic xyproblem [0], excel is almost never the answer when you’re dealing with “big” data, you’re almost always better off getting the data in a csv or db format and working on it from your favorite scripting language.

[0] https://xyproblem.info/

How you split millions of rows can be very different than shorter files, especially when you run into issues with file input/output issues in different languages.

Opening and working on an excel file with a few million rows can need a bit more ram than anticipated especially based on it's size/complexity.

The quickest way I'd start with is to convert it to a CSV, read the file in, and rewrite it out 500 lines at a time.

High level concept:

Xlsx or xls?

If it’s xlsx, stream the file, chunking 500 rows into each new file, watching for ref A1, then injecting that into previous files.

The xlsx format is a zip file of xml, not so bad to work with once you start.

I should note, this works best if you don’t care about the order of the rows. They technically aren’t required to be in order (I think).