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

    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)

  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")))

    #!/usr/bin/perl -CSD -w -Mstrict -Mwarnings -MText::CSV
    
    # chunk.pl -- split csv files into chunks
    
    # Usage message and exit if needed
    if (!@ARGV || $ARGV[0] eq '-h') {
        print "Usage: $0 input_csv [chunk_size] [output_filename_format] [separator]\n";
        print "Example: $0 input.csv 500 'input-%08d.csv' ','\n";
        exit;
    }
    
    # Set command-line arguments
    my ($INFILE, $CHUNKSIZE, $FMT, $SEP) = @ARGV;
    $CHUNKSIZE //= 500;
    $FMT //= "data-%08d.csv";
    $SEP //= ",";
    
    # Initialize CSV, file handles, and counters
    my $csv = Text::CSV->new({ binary => 1, auto_diag => 1, sep_char => $SEP, eol => "\n" });
    my ($i, $f, $out) = (0, 1, undef);
    open my $in, "<:encoding(UTF-8)", $INFILE or die "Cannot open $INFILE: $!";
    
    # Main loop
    while (my $row = $csv->getline($in)) {
        if ($i % $CHUNKSIZE == 0) {
            close $out if defined $out;
            open $out, ">:encoding(UTF-8)", sprintf($FMT, $f++) or die "Cannot open output file: $!";
        }
        $csv->print($out, $row) or die "Failed to write row: $!";
        $i++;
    }
    
    # Clean up: close file handles
    close $out if defined $out;
    close $in;

    Dim rng as range: set rng = Sheet1.UsedRange
    Dim rows as Long: rows = rng.rows.count
    Dim cols as long: cols = rng.columns.count
    Const SIZE as long = 500
    For i = 2 to rows step SIZE
      Dim wb as workbook: set wb = workbooks.add()
      wb.sheets(1).range("A1").resize(1, cols).value = rng.resize(1).value
      wb.sheets(1).range("A2").resize(SIZE,cols).value = rng.offset(i-1).resize(SIZE).value
      Call wb.SaveAs("C:\Temp\" & i & ".xlsx")
    next

    (require 'csv-mode)
    
    (let ((input-file "bigfile.xls")
          (output-dir "chunked/")
          (chunksize 1000))
      (cl-labels ((read-csv-file (filename)
                    (with-temp-buffer
                      (insert-file-contents filename)
                      (csv-mode)
                      (csv-parse-buffer t)))
                  (write-csv-file (filename data)
                    (with-temp-buffer
                      (csv-mode)
                      (dolist (row data)
                        (csv-insert-list row))
                      (write-region (point-min) (point-max) filename)))
                  (chunk-data (data chunk-size)
                    (let (result)
                      (while data
                        (push (cl-subseq data 0 (min chunk-size (length data))) result)
                        (setq data (nthcdr chunk-size data)))
                      (nreverse result))))
    
        (let* ((data (read-csv-file input-file))
               (chunks (chunk-data data chunk-size))
               (file-count 1))
          (dolist (chunk chunks)
            (let ((output-file (expand-file-name (format "chunk-%04d.csv" file-count) output-dir)))
              (write-csv-file output-file chunk)
              (setq file-count (1+ file-count))))
          (message "Processing complete. %d files created." (1- file-count)))))
    

  - 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