41–49 of 49 posts
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_ex…
I would save my data in CSV format, then use this. Save the code below as chunk.pl (remove leading spaces) and call it as "perl chunk.pl" : #!/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…
library(readr)
library(dplyr)
library(purrr)
data 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_ex…
Pandas is absolutely the way. The code you gave looks ok.
(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)))))
> Guys this is just an example. I'm looking for a general solution. It could be 10 million rows. It can't be. Excel's maximum row limit is only ~1 million.
I would naturally do it in Emacs Lisp for its superior excel reading abilities (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-…
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