How to Split excel file with multi columns into equal smalller excel files using matlab

Please how can splitting Excel file (n*m) into smallaer equal excel files by matalb

[darova]

What is criteria of splitting (what size)? What have you tried?

[Furat Alobaidy]

thanks for reply,

i have large file (excel( aroound (500,000) rwos , I need splitting them into seperate excel files in equal size , (for example each file arround 100000 rows .

[darova]

What about for loop?

[dpb]

"... excel( around (500,000) rwos , I need splitting them into seperate excel files in equal size , (for example each file arround 100000 rows"

Why do they need to be split into files, specifically?

Why not just process the parts desired in memory?

[Furat Alobaidy]

ok thanks,

Why do they need to be split into files, specifically?

buz i need spliting the files into small parts in order to process them as traiining and testing samples for NN learning \

Why not just process the parts desired in memory? i will try that . thanks

Answer 1

[Payas Bahade]

Hi Furat,

I have written a code which can be used to split one large excel file into multiple smaller excel files. Following sample code splits given excel file into 3 smaller files of 50 rows each.

inputFile = 'test11';% Input excel file name
M = 50; % Number of rows to be included in splitted excel files 
N = 3; % Number of files to be created
for k = 0:N-1
    outputFile = [ inputFile num2str(k+1,'%02i') '.xlsx' ];% Output file name
    cellRange=[ 'A' num2str(M*k+1) ':' 'D' num2str(M*k+M) ];% Defining area of input excel file to be written in output file
    X=readmatrix('test11.xlsx','Range',cellRange);% Reading required area in input excel file
    writematrix(X,outputFile);% Writing required area in output file
end

You can achieve your objectives by changing the above code as per your requirements.

Hope this helps!

[Furat Alobaidy]

thanks a lot , i really appriciate for that.

but gave me :Undefined function or variable 'readmatrix'.!

[dpb]

readmatrix not introduced until pretty recently--don't remember which release, precisely.

[Furat Alobaidy]

thanks its work perfect , i update my version from 2016 into 2019 , best thanks

[Furat Alobaidy]

Thanks for your help , but when i used this progran to data file in attacheed,

its slpiting but without coulum ( C) " memory address " , at that excel file , how can overcome this problem please?

% this file using to splitting xcell file into parts , Oct 21 2019


inputFile = ('book1F.xlsx');% Input excel file name
M = 100; % Number of rows to be included in splitted excel files 
N = 20; % Number of files to be created
for k = 0:N-1
    outputFile = [ inputFile num2str(k+1,'%02i') '.xlsx' ];% Output file name
    cellRange=[ 'A' num2str(M*k+1) ':' 'D' num2str(M*k+M) ];% Defining area of input excel file to be written in output file
    X=readmatrix('book1F.xlsx','Range',cellRange);% Reading required area in input excel file
     writematrix(X, outputFile);% Writing required area in output file
     
end 
%%% end 

[Payas Bahade]

Hi Furat,

To resolve the above issue of 'C' column not been read, specify 'OutputType' as 'string' in 'readmatrix' function. Please use code mentioned below:

inputFile = ('book1F');% Input excel file name
M = 100; % Number of rows to be included in splitted excel files 
N = 20; % Number of files to be created
for k = 0:N-1
    outputFile = [ inputFile num2str(k+1,'%02i') '.xlsx' ];% Output file name
    cellRange=[ 'A' num2str(M*k+1) ':' 'D' num2str(M*k+M) ];% Defining area of input excel file to be written in output file
    X=readmatrix('book1F.xlsx','Range',cellRange,'OutputType','string'); % Reading required area in input excel file
     writematrix(X,outputFile);% Writing required area in output file   
end

Hope this helps!

[Furat Alobaidy]

thanks alot for your help.