Splitting an excel file using matlab

Hi,

I have a 900,000 row excel file that I want to split to 900 files. I am wondering if i can do this with matlab. the content is a single column with strings like :US000305AA00. Kindly advise otherwise i have to do it manually which is very time consuming.

Answer 1

[Rick Rosson]

Yes, you can do this task with MATLAB. Please try the following:

inputFile = 'myfile';
A = xlsread([ inputFile '.xlsx' ]);

M = 1000;
N = 900;

for k = 0:N-1
   outputFile = [ inputFile num2str(k+1,'%03i') '.xlsx' ];
   xlswrite(outputFile,A(M*k+(0:M-1)+1));
end

I have tested this code for M = 20 and N = 10, and it works just fine. I suspect, however, that it will take a long time to run for a file containing 900,000 numbers.

HTH.

Rick

[joseph Frank]

I am receiving an error

Error in ==> Split at 9

xlswrite(outputFile,A(1000*k+(0:M-1)+1));

??? Index exceeds matrix dimensions.

[Rick Rosson]

Sorry. I fixed both errors, made a few improvements, and tested the code. It works just fine.

[joseph Frank]

How come? I am still receiving the same error. If k=0 the your codes are generating A(0:1000) and i think this is the source of error. it should be A(1:1000)

[Rick Rosson]

It goes from 0:999, but then I add 1, so that it goes from 1:1000. Please check it again. Thanks.

[joseph Frank]

Rick,

It is not exceeding K=0. I double checked everything and i am using it on a smaller file.weird.

the codes are:

inputFile = 'ISIN';

A = xlsread([ inputFile '.xlsx' ]);

M = 1000;

N = 89;

for k = 0:N-1

outputFile = [ inputFile num2str(k+1,'%03i') '.xlsx' ];

xlswrite(outputFile,A(M*k+(0:M-1)+1));

end

and I am using it on a 90,000 rows file. I will send it to u by email.Please advise

[joseph Frank]

I found the error:

it is failing to read the xlsx file and returning A as empty

[Rick Rosson]

Hi Joseph,

I have given you everything you need to solve this problem. I have tested my code, and it works just fine. Please try to get it to work on your machine with your file. If you assign the correct values to the parameters, use the correct file name and extension, and make sure the file is available on the MATLAB path, it should work.

[joseph Frank]

I imported the file manually using the import wizard and it is working well. Thanks Rick but still I can't figure out why A was empty given that my xlsx file name was ISIN.xlsx

Answer 2

[Walter Roberson]

Is the file .csv format or .xls or .xlsx format?

Does the header of the file need to be repeated for each new file?

Is the output fixed at 900 files, or is it fixed at 1000 rows per file?

Probably you probably do not need a lot more than this FAQ

but if your file does happen to be .csv and you do not need to repeat the header line there might be easier alternatives (especially if you are using MacOS or Linux)

[joseph Frank]

the file is .xlsx format without headers. Each file should contain 1000 observations.so in total I should have 900 files.