How to know the range automatically identified by readtable when importing an Excel file?
51 vues (au cours des 30 derniers jours)
I am using readtable in an app to import data from an Excel spreadsheet, which contains several lines of heading and then the actual table with information: something like the example below
readtable successfully manages to skip the headers and to pull out the data. My problem however is that I also need to copy all the information in the header section because then I need to create a second file with the same header and only some of the elements of the table below. I cannot simply copy a given range (say A1:G6 in the example above) because the number of rows/columns of the header changes depending on the file.
Is there a way to know which range was automatically identified as a table by readtable, so that I could then deduct the range of the header that I need?
dpb le 13 Avr 2023
It will be only as good as the ability of the detectImportOptions bot's ability to recognize the start of the data, but
will show you what it determined automagically...the two ranges are the starting cell (upper LH corner of the range); the number of elements in the VariableNames array will let you deduce the number of columns. It does not return the Excel UsedRange range address; that could be a useful adjunct to ask for as an enhancement; as is, all you can do is then read the table and see how many rows were returned. That size, of course, is dependent upon the setting of the 'ImportError' and 'Missing' rules and the data content of the file itself.
IF (the proverbial "big if") the format is always similar to the shown table, it's probably going to be pretty reliable; less well formatted or more eclectic data collections may confuse the scanning and not always get the same answer for what appear to be very similar worksheets. In particular, missing or nonnumeric data in columns that should be recognized as numeric often will cause the whole column to be treated as a cell or may change the 'DataRange' value returned.