Read Excel file, specific sheet and able to define the variable names within the code, and not read first row

8 views (last 30 days)
Jeffrey Beckstead
Jeffrey Beckstead on 15 Feb 2021
Edited: Cris LaPierre on 15 Feb 2021
I have an Excel spreadsheet with two sheets. I want to read a specific sheet (second sheet), and define the variable names within MatLab code. I am able to do this on in other scripts that only use first sheet with the options. Since I want to read the second sheet (or a defined sheet) AND define the variable names, I have attempted to use the spreadsheetImportOpions. With this I am able to set the sheet, and name the variables for the spreadsheet columns. The spreadsheet has headers at each column. I do not want to read the header (ex. normal readtable with "ReadVariableNames" field).
readtable( filename, "ReadVariableNames", false)
I am not able to use the "ReadVariablesNames" with the spreadsheetImportOptions
If I use the readtable( filename, "Sheet", 2, "ReadVariableNames", false), I can not set the variable names for the columns.
What I have used on a single sheet (or the first sheet)
fileOpts = detectImportOptions(waveSpecificFile);
fileOpts.VariableNames(1) = {'Wavelength'};
fileOpts.VariableNames(2) = {'Property'};
transTable = readtable( waveSpecificFile, fileOpts );
But this file only has one sheet, or this data is on the first sheet.
How can I set the variable names AND read a different sheet?
My attempt
fileOpts = spreadsheetImportOptions;
fileOpts.Sheet = "Property"; %Property being the Excel name of the second sheet (or sheet that I want to read)
fileOpts.VariableNames(1) = {'Wavelength'};
fileOpts.VariableNames(2) = {'Profile'};
propertyTable = readtable(fileFullPath, fileOpts);
Excel data on "Property" sheet
Wavelength_nm Transmission % I don't want to use these names
400 1
401 2
402 3
403 4
404 5
405 6
406 7
407 8
408 9
409 10

Answers (1)

Cris LaPierre
Cris LaPierre on 15 Feb 2021
Edited: Cris LaPierre on 15 Feb 2021
Try this
opts = detectImportOptions(filename);
opts.Sheet = "Property";
opts.VariableNames = ["Wavelength","Property"];
data = readtable(filename, opts)

Products


Release

R2020b

Community Treasure Hunt

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

Start Hunting!