How to Import excel document with alot of sheets that does not ignore date in first column

Hi,

I need to import an excel doc that has a lot sheets (individual stocks on each sheet).

I manage to import with the code underneath, but it skips column A on every sheet that is the date. The date are formatted in excel as this : 1/3/2017 etc. If I manually change them to be numbers in excel it works, but that takes too much time in the long-run so I want that MATLAB solves that for me. Any suggestions/help?

[~,sheet_name] = xlsfinfo('filename.xlsx'); 
for k= 1:numel(sheet_name)
    data{k} = xlsread('filename.xlsx', sheet_name{k});
end

[Andrei Bobrov]

Please attach here one your xlsx-file.

[Kristian Opsahl]

Hi,

This is a small version of the full file I will use in the end

Answer 1

[Andrei Bobrov]

fn = 'Path_to_your_file\MachineLearning.xlsx';
[~,sheet_name] = xlsfinfo(fn); 
sheet_name = strrep(sheet_name,' ','_');
S = struct();
for ii = 1:numel(sheet_name)
    [d,t] = xlsread(fn,ii);
    if ii == 1
        vn = t(5,2:end);
        vn{13} = strrep(vn{13},'%','PR');
    end
    t(6,1) = t(1,2);
    RTime = datetime(t(6:end,1),'f','dd.MM.uuuu');
    S.(sheet_name{ii}) = array2timetable(d,'Rowtimes',RTime,'v',vn);
end

[Kristian Opsahl]

That is just beautiful Andrei. Thank you so much.

Answer 2

[KSSV]

I suggest you to use readtable.

With xlsread:

[num,txt,raw] = xlsread(myfile) ;

[Kristian Opsahl]

Hi, Thank you for your answer. With this I am only able to get the first sheet, Not the rest. Am I doing something wrong with my setup then?

[KSSV]

Read the documentation.....you need to provide the sheet number which is to be read.