MATLAB Answers

large excel data into multiple excel file using xlswrite ?

2 views (last 30 days)
MUKESH KUMAR
MUKESH KUMAR on 22 Aug 2019
Commented: M.Prasanna kumar on 22 Aug 2019
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 ?

  1 Comment

M.Prasanna kumar
M.Prasanna kumar on 22 Aug 2019
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])

Sign in to comment.

Answers (2)

Guillaume
Guillaume on 22 Aug 2019
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

  0 Comments

Sign in to comment.


Bob Nbob
Bob Nbob on 22 Aug 2019
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.

Translated by