readtable changing range (not all contents)

조회 수: 51(최근 30일)
Dave
Dave 2018년 12월 11일
댓글: Jeremy Hughes 2018년 12월 14일
Hello,
Manually Importing an XLS file (right click import data) the window by default highlights a matrix, say C10:X90
If I manually change the default "column vectors" to "table" and then click import, then it imports the matrix I need.
I need to do this for many files. However, using readtable('file1.xls') it imports all contents, beyond that matrix.
The matrix range changes from file to file so I cannot fix it to C10:X90, so file2.xls can be C15:X80, etc
How can I import the highlighted matrix (which changes from file to file) in a loop?
  댓글 수: 7
Walter Roberson
Walter Roberson 2018년 12월 13일
With that file the highlighted area is A7:F20. There is numeric data in F7 and there is no obvious reason why it should be excluded.
If you were to use
[num,txt,raw] = xlsread('Importing.xls')
then num would correspond to B7:F15
If you want to automate then you need to define more rigorously what is to be imported or not. For example is the rule that you are always extracting from the left side, column A? And is the rule that you always stop at the first empty column (excluding header lines) ?

댓글을 달려면 로그인하십시오.

답변(1개)

Cris LaPierre
Cris LaPierre 2018년 12월 14일
편집: Cris LaPierre 2018년 12월 14일
Not sure how to detect the end of your dataset before using readtable, but what about reading it in and then deleting the extra rows? For this, I'm assuming Title1 will be populated for every row that has data. Adjust for how your data actually behaves.
opts = detectImportOptions('Importing.xls');
startRow=opts.DataRange
tbl = readtable('Importing.xls',opts)
tbl(ismissing(tbl.Title1),:) = []
Probably worth stating that this also assumes every file has a column header Title1 that contains the Char2, ... values.
  댓글 수: 1
Jeremy Hughes
Jeremy Hughes 2018년 12월 14일
If you're already using import options,
opts.MissingRule = 'omitrow' will remove rows with missing. However that will omit rows with ANY missing data in ANY column.
But, if all you want is to read a block, setting opts.DataRange to the beginning cell of that range will read until it reaches the bottom, then stop.
e.g.
opts.DataRange = 'B3'

댓글을 달려면 로그인하십시오.

Community Treasure Hunt

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

Start Hunting!

Translated by