MATLAB Answers

0

large excel data into multiple excel file using xlswrite ?

Asked by MUKESH KUMAR on 22 Aug 2019
Latest activity Commented on by 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

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.

2 Answers

Answer by 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.


Answer by 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.

  1 Comment

thank you for the suggestion bob nbob

Sign in to comment.