Extract and process a specific Excel Rows

2 views (last 30 days)
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
  2 Comments
Walter Roberson
Walter Roberson on 22 Nov 2019
Are the row numbers known, or are the rows determined according to contents?
Furat Alobaidy
Furat Alobaidy on 22 Nov 2019
yes , the rows determined according to contents.

Sign in to comment.

Answers (1)

Walter Roberson
Walter Roberson on 22 Nov 2019
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.
  1 Comment
Walter Roberson
Walter Roberson on 22 Nov 2019
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.

Sign in to comment.

Tags

Products


Release

R12.1

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!