large excel data into multiple excel file using xlswrite ?

I had excel sheet data havaing 1048576 rows and I want to split it into 12 excel files and save data in 12 different excel files. How can I do this ?

[M.Prasanna kumar]

import your excel file in to matlab, then store entire matrix and assign a variable to it.

then, first 'n, rows store it in a newmatrix 1, second 'm' rows store it in a 'new matrix2' and so on

the apply

xlswrite(C:\Folder\name.xls, [new matrix1])

Answer 1

[Guillaume]

This should do it:

folder = 'C:\somewhere\somefolder';
destname = 'splitfile%2d.xlsx';
largefile = readtable('yourexcelfile');  %assuming the data is on the first tab and has a consistent format for the rows
destindex = ceil((1:height(largefile))' / 12);
for idx = 1:max(destindex)
    writetable(largefile(destindex == idx, :), fullfile(folder, sprintf('destname', idx)));
end

Answer 2

[Bob Thompson]

M.Prasanna kumar is definitely on the right track. The only suggestion I would make is not to create multiple new matrices, just index through the original matrix. I would also suggest looping the results to automate the process.

Keep in mind that you are working with a lot of data at once, and it will not be a super quick process.

data = xlsread('myexceldata.xlsx'); % Import excel data
filenames = ...;    % Array of new file names. You can also generate these with sprintf if you would prefer
ranges = [1 5; 6 10003; 10004 10005]; % Array of ranges for each file. This is not necessary if each group is going to cover a range of the same size

for i = 1:12;
    xlswrite(filenames{i},data(ranges(i,1):ranges(i,2),:));
end

Some tweaking is probably necessary to make this perfectly match your setup, but the idea should be generally what you're looking for.

As a warning, I have a tickling in the back of my brain that it is only possible to load so many excel rows at once, some kind of limitation within excel. If you run into an error along those lines you may want to look into converting the excel file data into a .csv or something similar.

[M.Prasanna kumar]

thank you for the suggestion bob nbob