Chossing random data from xls files

I have hundreds of excels files in different folders with data on hundreds of records,I want to record the data to a single (excel) file that choose randomly files from the folder and the rows (randomly too...) Please guide me what is the procedure that I adopt to cater this thing. Thanks

[Geoff Hayes]

Muahmmad - do you have a list (cell array) of all files (with paths to each file)? Do you need to select at least one row from each file? If data has been selected from a certain file, can we re-select that data? How many rows (in total) do you want to select?

Since your randomness involves choosing files from a list, or rows from a file, the generated random number will always be an integer. See randi for details on generating pseudorandom integers.

[Muhammad Usman]

Dear Geoff, no, i don't have a list of all files with paths yet. yes i need to select at least one row from each file. i have 29000 files, and 100,000 rows in total i want to select, and no i don't want to re-select that row.

Thank you for your time.

[Salaheddin Hosseinzadeh]

It's a pure programming problem.

Anyone with a little creativity, knowledge of programming, and of course some hours to think and trial and error should be able to tackle this really!

I don't see a point to ask this!

Answer 1

[Geoff Hayes]

Muahmmad - do you know what the minimum number of rows are in each file? If so, then you could create a vector of integers, one for each file, where the sum of all elements in the vector is 100000 (the number of rows that you want to extract). You can initialize the vector to that of all ones

 numFiles       = 29000;
 selRowsPerFile = ones(numFiles,1);

so that at least one row is selected from each file.

There may be some code on the MATLAB File Exchange that can create this vector for you (subject to the constraint of each element in the vector being at least one, and the sum of the elements in the vector being exactly 100000). If not, then you could write a simple while loop with randi to randomly select more rows for each file, until the 100000 rows have been assigned.

Then, once you have your list of files, for each file you can use xlsread to read in the data and then randomly selected a number of rows (again using randi) from that data (using the selRowsPerFile vector to tell you how many rows to read). Once those rows have been selected, then use xlswrite to write the data to file.