Extract and process a specific Excel Rows

Hi ,

Kindly. i have Excel file contains on two columns with multi-hundred of rows, i need to extract values for specific rows only and storing them in vectors , for example :

Col1 col2

sd1 8.57

sd 2 20495706

memory :A1 20495706

a6 1 44

a7 1000

system:ALU 10000000000

memory:: dtat1 36800

So i need store the values for the specific rows as following :

v1 = 8.57 % vale of row "sd1"

v2= 20495706 % value of row memory :A1

and ignore other rows in the Excel file.

i appreciate for any help.

best regards

[Walter Roberson]

Are the row numbers known, or are the rows determined according to contents?

[Furat Alobaidy]

yes , the rows determined according to contents.

Answer 1

[Walter Roberson]

For csv files, it might be practical to use low-level I/O to find the location you need in the file, such as by buffering a block from the file and searching it (reading a bit at a time without buffering is very inefficient.)

For xls files, you would have the possibility of iterating through the binary structure of the file looking for the first match for the string you are looking for. It might not have been as efficient as some of the possibilities, but it would be possible and that could potentially be important for files larger than your available RAM.

For xlsx files, the contents are compressed .xml files. If you do not permit a routine such as readtable() to process the whole file for you, then you would need to use a java routine to decompress the zip structure, find the appropriate file, and start parsing through the results using something like regexp() . Getting all of that right is inconvenient, as there are complexities involved where the xml file can refer to contents in other of the .xml files .

If you are using MS Windows with Excel installed, you can open an ActiveX instance to Excel and tell it to do the searches.

Most of the time the easiest thing to do for xlsx files is read in the whole file and throw out what you do not need.

[Walter Roberson]

Also in some cases you might want to use a tall() array backed by a datastore that contains the .xlsx file.

xlsx files contain at most 2^20 (1048576) rows, so especially with it appearing that there are only a few columns, reading all of the data and throwing away what you do not need is probably much easier than the alternatives.