[dead]
Ask HN: How would you chunk a large Excel file?
21–30 of 49 posts
Re: Ask HN: How would you chunk a large Excel file?
#22Can 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.
Re: Ask HN: How would you chunk a large Excel file?
#23You 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 Superinten…
This looks great. Well done.
Thank you!
Re: Ask HN: How would you chunk a large Excel file?
#24I 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.
Re: Ask HN: How would you chunk a large Excel file?
#25convert to csv, Next is simple text processing.Final ,convert csv to excel
Re: Ask HN: How would you chunk a large Excel file?
#26It'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.
Yes, Microsoft.ACE.OLEDB.##.0
Re: Ask HN: How would you chunk a large Excel file?
#27This 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.
Re: Ask HN: How would you chunk a large Excel file?
#28How 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.
Re: Ask HN: How would you chunk a large Excel file?
#29[deleted]
Re: Ask HN: How would you chunk a large Excel file?
#30High 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).