Read excel file with number and text

90 views (last 30 days)
Marpe
Marpe on 23 Nov 2022
Answered: Seth Furman on 5 Dec 2022
Hello,
I want to load an excel file (shared in attachments) which has numeric and text 'values' in it (text cells are also really important).
I tried using both readtable and xlsread but I never managed to replicate exactly the excel table in matlab (usually the text rows were NaN).
Here is my code.
datapath = '/…/';
d = dir(datapath);
nd = length(dir);
files=dir(fullfile(datapath,'*.xlsx'));
for n=2
file=fullfile(datapath,files(n).name);
int=readtable(file,"ReadVariableNames",true,"VariableNamingRule","preserve");
end
Thanks for your help,
Marine

Sign in to answer this question.

Accepted Answer

Seth Furman
Seth Furman on 5 Dec 2022
Ran in:
Table variables cannot contain a mix of numbers and text. In order to preserve the data, we must import each variable as text. detectImportOptions provides more advanced control over how data are read.
https://www.mathworks.com/help/matlab/ref/detectimportoptions.html
opts = detectImportOptions("BH_Model.xlsx",VariableNamesRange="A1:BO1",VariableNamingRule="preserve");
opts.VariableTypes = repmat("string",1,numel(opts.VariableTypes));
Table variables must all have the same number of rows, so extra rows are filled with missing strings.
readtable("BH_Model.xlsx",opts)
ans = 174×67 table
Date 11-Oct-2022 11-Oct-2022_1 11-Oct-2022_2 11-Oct-2022_3 11-Oct-2022_4 11-Oct-2022_5 12-Oct-2022 12-Oct-2022_1 12-Oct-2022_2 12-Oct-2022_3 12-Oct-2022_4 12-Oct-2022_5 12-Oct-2022_6 12-Oct-2022_7 12-Oct-2022_8 12-Oct-2022_9 12-Oct-2022_10 12-Oct-2022_11 20-Oct-2022 20-Oct-2022_1 20-Oct-2022_2 20-Oct-2022_3 20-Oct-2022_4 20-Oct-2022_5 20-Oct-2022_6 20-Oct-2022_7 20-Oct-2022_8 20-Oct-2022_9 20-Oct-2022_10 20-Oct-2022_11 20-Oct-2022_12 20-Oct-2022_13 20-Oct-2022_14 20-Oct-2022_15 20-Oct-2022_16 20-Oct-2022_17 21-Oct-2022 21-Oct-2022_1 21-Oct-2022_2 21-Oct-2022_3 21-Oct-2022_4 21-Oct-2022_5 21-Oct-2022_6 21-Oct-2022_7 21-Oct-2022_8 21-Oct-2022_9 25-Oct-2022 25-Oct-2022_1 25-Oct-2022_2 25-Oct-2022_3 25-Oct-2022_4 25-Oct-2022_5 25-Oct-2022_6 25-Oct-2022_7 25-Oct-2022_8 25-Oct-2022_9 25-Oct-2022_10 25-Oct-2022_11 26-Oct-2022 26-Oct-2022_1 26-Oct-2022_2 26-Oct-2022_3 26-Oct-2022_4 26-Oct-2022_5 26-Oct-2022_6 26-Oct-2022_7 ___________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ ______________ ______________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ ______________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ ______________ ______________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ _____________ "Date" "11-Oct-2022" "11-Oct-2022" "11-Oct-2022" "11-Oct-2022" "11-Oct-2022" "11-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "12-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "20-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "21-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "25-Oct-2022" "26-Oct-2022" "26-Oct-2022" "26-Oct-2022" "26-Oct-2022" "26-Oct-2022" "26-Oct-2022" "26-Oct-2022" "26-Oct-2022" "Animal" "Ku" "Ku" "Ku" "Ku" "Ku" "Ku" "Bi" "Bi" "Bi" "Bi" "Bi" "Bi" "Ne" "Ne" "Ne" "Ne" "Ne" "Ne" "Jo" "Jo" "Jo" "Jo" "Jo" "Jo" "La" "La" "La" "La" "La" "La" "Le" "Le" "Le" "Le" "Le" "Le" "La" "La" "La" "La" "La" "La" "Ne" "Ne" "Ne" "Ne" "Bi" "Bi" "Bi" "Bi" "Bi" "Bi" "Ku" "Ku" "Ku" "Ku" "Ku" "Ku" "Pfi" "Pfi" "Le" "Le" "Le" "Le" "Le" "Le" "Period" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Pre" "Pre" "Pre" "Pre" "Breath-hold" "Breath-hold" "Post" "Post" "Method" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "ECG" "BCG" "BCG" "BCG" "ECG" "BCG" "ECG" "BCG" "Intervals" "0.88" "0.74" "1.14" "0.74" "0.82" "0.78" "0.82" "0.8" "1.46" "0.56" "0.86" "0.92" "0.74" "0.5" "0.72" "0.62" "0.38" "0.58" "0.78" "0.7" "1" "1" "0.74" "1.42" "0.58" "0.64" "1" "1.08" "0.66" "1" "0.94" "0.76" "1.14" "1.18" "0.78" "0.84" "1.02" "1.06" "0.84" "0.84" "0.62" "0.4" "0.78" "0.74" "0.7" "0.58" "1.52" "1.2" "1.28" "1.02" "1.08" "1.04" "0.82" "0.88" "1.08" "0.62" "0.9" "1.1" "1.06" "0.5" "0.56" "0.66" "0.92" "0.92" "0.7" "0.74" "Intervals" "0.74" "0.8" "1" "1.06" "0.74" "0.82" "0.86" "0.86" "1.44" "0.84" "0.86" "0.8" "0.5" "0.56" "0.62" "0.66" "0.6" "0.66" "0.8" "0.84" "1" "1.02" "0.68" "0.68" "0.6" "0.54" "0.92" "1" "0.7" "0.4" "0.96" "1.06" "1.16" "1.28" "0.48" "0.68" "0.78" "0.68" "0.88" "0.86" "0.56" "0.48" "0.56" "0.62" "0.7" "0.66" "1.76" "1.52" "1.4" "0.44" "1.34" "0.98" "0.84" "0.86" "1.42" "0.7" "0.86" "0.64" "1.14" "0.88" "0.58" "0.72" "0.98" "0.96" "0.62" "0.92" "Intervals" "0.78" "0.84" "1.14" "1.06" "0.96" "0.68" "1.02" "1.08" "1.52" "1.52" "0.94" "0.88" "0.5" "0.5" "0.7" "0.66" "0.58" "0.56" "0.84" "0.82" "1.06" "1.06" "0.68" "0.82" "0.62" "0.62" "0.88" "1.28" "0.68" "0.48" "1" "0.98" "1.22" "1.12" "0.68" "0.72" "0.74" "0.74" "0.9" "0.92" "0.64" "0.58" "0.5" "0.66" "0.72" "0.7" "1.88" "1.9" "1.48" "1.14" "1.26" "1.5" "0.64" "0.84" "1.32" "1.2" "0.76" "0.86" "1.18" "0.58" "0.6" "1.14" "0.96" "0.94" "0.6" "0.78" "Intervals" "0.78" "0.34" "1.02" "1.1" "0.92" "0.94" "1.08" "1.04" "1.58" "0.58" "1.16" "1.22" "0.82" "0.5" "0.7" "0.68" "0.5" "0.54" "0.88" "0.96" "1.02" "1.06" "0.62" "0.36" "0.64" "0.58" "0.96" "1.44" "0.7" "0.9" "1.12" "1.04" "1.18" "1.22" "0.72" "0.76" "0.76" "0.76" "0.92" "0.92" "0.6" "0.66" "0.66" "0.72" "0.72" "0.72" "1.84" "1.84" "1.52" "1.2" "1.24" "0.92" "0.76" "0.78" "1.14" "1.04" "0.7" "0.86" "1.16" "0.68" "0.62" "1.22" "0.9" "0.62" "0.7" "0.68" "Intervals" "0.8" "0.86" "1.2" "1.22" "0.94" "0.52" "1.04" "1.04" "1.54" "0.42" "1.2" "1.14" "0.46" "0.46" "0.78" "0.78" "0.7" "0.68" "0.86" "0.76" "1" "1" "0.6" "0.66" "0.66" "0.76" "1.06" "1.1" "0.7" "0.38" "1.06" "1.14" "1.22" "1.24" "0.76" "0.64" "0.8" "0.8" "0.96" "1" "0.64" "0.62" "0.68" "0.72" "0.9" "0.56" "1.82" "1.86" "1.56" "1.3" "1.24" "1.36" "0.68" "0.68" "1.08" "1.12" "0.68" "0.6" "1.26" "0.7" "0.6" "1.12" "0.92" "0.58" "0.74" "0.8" "Intervals" "0.92" "0.84" "1.18" "1.18" "0.72" "0.68" "1.06" "1.06" "1.52" "0.54" "1.14" "1.2" "0.52" "0.52" "0.78" "0.86" "0.58" "0.58" "0.82" "0.84" "1.06" "1.06" "0.6" "0.68" "0.68" "0.7" "1.18" "1.26" "0.66" "0.74" "1.04" "0.46" "1.22" "1.26" "0.56" "0.94" "0.86" "1.66" "0.94" "0.9" "0.62" "0.7" "0.78" "0.7" "0.76" "0.8" "1.86" "1.84" "1.66" "1.66" "1.28" "1.56" "0.64" "0.66" "1.08" "0.32" "0.68" "1.16" "1.4" "0.92" "0.6" "1.04" "1.16" "0.9" "0.8" "1.24" "Intervals" "0.74" "0.78" "1.22" "1.2" "0.88" "0.36" "1.2" "1.2" "1.52" "1.52" "1.18" "1.14" "0.66" "0.56" "0.82" "0.78" "0.58" "0.58" "0.84" "0.84" "1.08" "1.04" "0.64" "0.54" "0.72" "0.66" "1.04" "0.98" "0.6" "0.54" "1.14" "1.08" "1.32" "1.36" "0.52" "0.68" "0.84" "0.84" "0.96" "0.98" "0.64" "0.64" "0.76" "0.58" "0.86" "0.88" "1.48" "1.5" "1.72" "1.34" "0.96" "0.98" "0.64" "0.66" "0.96" "0.64" "0.68" "0.82" "1.1" "0.54" "0.72" "1.48" "1.22" "1.22" "0.78" "0.62" "Intervals" "0.86" "0.9" "1.2" "1.22" "0.8" "0.32" "1.28" "1.3" "1.52" "1.52" "1.36" "1.34" "0.48" "0.54" "0.78" "0.82" "0.68" "0.6" "0.88" "0.88" "1.16" "1.16" "0.66" "0.64" "0.74" "0.8" "1.04" "0.56" "0.64" "0.62" "1.12" "0.72" "1.32" "1.34" "0.72" "0.74" "0.84" "0.84" "1" "1.04" "0.62" "0.52" "0.64" "0.78" "0.78" "0.72" "1.42" "1.42" "1.8" "1.52" "1.02" "0.72" "0.62" "0.58" "0.96" "0.92" "0.86" "0.46" "1.06" "0.6" "0.74" "0.38" "1.12" "1.12" "0.78" "0.68" "Intervals" "0.92" "0.78" "1.24" "1.24" "0.78" "0.48" "1.16" "1.16" "1.5" "1.5" "1.48" "1.5" "0.46" "0.5" "0.88" "0.8" "0.5" "0.58" "0.92" "0.92" "1.14" "1.14" "0.62" "0.64" "0.74" "0.74" "0.96" "1.4" "0.64" "0.72" "0.88" "0.78" "1.24" "1.36" "0.74" "0.72" "0.82" "0.82" "1.1" "1.08" "0.64" "0.64" "0.66" "0.66" "0.72" "0.64" "1.52" "1.5" "1.76" "1.44" "0.96" "1.02" "0.6" "0.68" "0.94" "0.94" "1.12" "0.64" "1.48" "1.1" "0.9" "1.06" "1.14" "1.08" "0.82" "0.82" "Intervals" "0.7" "0.42" "1.16" "1.16" "0.78" "0.78" "1.14" "1.08" "1.46" "1.5" "1.46" "1.46" "0.64" "0.58" "0.8" "0.8" "0.58" "0.58" "0.96" "0.96" "1.1" "1.1" "0.64" "0.64" "0.72" "0.62" "1.18" "1.16" "0.66" "0.58" "0.74" "0.72" "0.96" "1.28" "0.62" "0.68" "0.82" "0.86" "1.12" "1.12" "0.64" "0.64" "0.72" "0.82" "0.8" "0.74" "1.58" "1.6" "1.96" "1.94" "0.98" "0.94" "0.6" "0.6" "0.96" "1.02" "1.16" "0.58" "1.3" "1.08" "1.04" "0.84" "1.04" "1.04" "0.8" "0.68" "Intervals" "0.8" "0.86" "1.14" "1.14" "0.74" "0.78" "1.04" "0.82" "1.54" "1.54" "0.92" "0.82" "1.1" "0.64" "0.74" "0.82" "0.58" "0.58" "0.96" "0.96" "1.12" "1.12" "0.64" "0.72" "0.62" "0.36" "1.08" "0.82" "0.64" "0.64" "0.72" "0.88" "1.04" "1.32" "0.52" "0.82" "0.8" "0.76" "1.04" "1" "0.64" "0.7" "0.58" "0.66" "0.92" "0.82" "1.6" "1.6" "1.8" "1.82" "0.98" "1" "0.6" "0.54" "1.14" "1.18" "1.22" "0.66" "1.22" "0.8" "0.94" "0.84" "1.06" "1.12" "0.64" "1.2" "Intervals" "0.88" "0.74" "1.3" "1.32" "0.72" "0.74" "0.9" "0.96" "1.64" "1.64" "1.04" "1.08" "0.62" "0.62" "0.84" "0.84" "0.66" "0.58" "0.94" "0.94" "1.2" "1.2" "0.58" "1.18" "0.64" "1.02" "1.14" "1.1" "0.62" "0.68" "0.78" "0.8" "1.32" "1.32" "0.84" "0.9" "0.8" "0.8" "1.04" "1.1" "0.58" "0.62" "0.64" "0.58" "0.86" "0.76" "1.6" "1.54" "1.88" "1.9" "0.9" "0.64" "0.66" "0.6" "1.24" "1.18" "1.02" "0.56" "0.94" "0.5" "0.78" "0.78" "1.08" "1.02" "0.64" "0.68"

More Answers (0)

Categories

Find more on Spreadsheets in Help Center and File Exchange

Tags

Community Treasure Hunt

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

Start Hunting!