1–10 of 49 posts
Edit: Guys this is just an example. I'm looking for a general solution. It could be 10 million rows.
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 languageIf it had 100k rows, I'd be out of my depth, so I'd hit google.
Not even going to ask AI. Waiting for a bash one-liner before OP reminds us they are on corporate Windows machine.
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)
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")))