Divide humongous excel sheets into multiple excel files

Question

Not exactly a programming question but I have an excel file which has over a few thousand records and I would like to have it divided into smaller excel files of lets say a few hundred records. Whats the easiest way to do this - sorry for not being a direct programming question :(

Answer 1

If the spreadsheet is purely data (i.e. no cells referencing each other via formulas), then I guess I'd try to save it as a CSV, then programatically chop it up (e.g. by number of lines) and save each chunk to a different csv file.

Answer 2

Programmatically, there is nothing stopping you from opening the excel file and dividing it into several smaller files.

Check out http://www.tmssoftware.com/site/flexcelnet.asp for a third party tool to make this easier. You could do it completely on your own using SpreadSheetML, this just makes it easier.

Answer 3

If you have access to unix command line tools the CSV option mentioned earlier is trivial. Simple export to CSV, split --lines=100 the CSV into multiple files, import the files.

You could also try using macro scripting. Create a macro to cut the first few hundred records, paste them into a new spreadsheet and save with a unique name. Then press play multiple times or create a loop.