How to read header (text data of 100000 rows,18 columns) from data in excel into Matlab using different directory

How to read header (text data of 100000 rows,18 columns) from data in excel into Matlab using different directory

Answer 1

[Fangjun Jiang]

Not sure what do you mean by "using different directory", but did you try?

[Num, Txt, Raw]=xlsread('C:\MyDirectory\MyExcel.xls');

[Gali Musa]

I have imported the excel data but there is no header name and the header will be use for the performance calculation

[Gali Musa]

i still have an issue when trying to do some calculation. My algorithm doesn't recognise the column header so as to pick and do some calculation.

[Fangjun Jiang]

You need to clarify your question. An simple example might be helpful. You might want to consider readtable(). See "doc readtabel". There is an example reading from Spreadsheet with Header created in the table.

[Gali Musa]

suppose this is the data want to read from excel and make this calculations in the Matlab

headers = {'time','power','T1','RH','P1','T2'};
Data = [5 10 15 20 25 30 35 40 45; 20 21 23 21 22 23 21 22 23; 200 202 205 207 206 205 201 202 208; 83 80 82 81 82 83 81 82 80; 101 101 102 103 105 104 102 101 100; 400 401 403 405 401 407 406 402 406];

a = power + T1;
b = P1 + T2;
c = RH + P1 + T2;

[Walter Roberson]

Do you mean that you have a .xls or .xlsx file that looks like

    time    power    T1     RH    P1     T2 
    ____    _____    ___    __    ___    ___
      5      20      200    83    101    400
     10      21      202    80    101    401
     15      23      205    82    102    403
     20      21      207    81    103    405
     25      22      206    82    105    401
     30      23      205    83    104    407
     35      21      201    81    102    406
     40      22      202    82    101    402
     45      23      208    80    100    406

[Gali Musa]

Yes it has 19 columns names and 56000 rows name for the time of the days in a year. Please see a sample attached

[Fangjun Jiang]

Use T=readtable('MyExcel.xls') then you can do T.power+T.T1

[Walter Roberson]

a = T.power + T.T1;
b = T.P1 + T.T2;
c = T.RH + b;

[Gali Musa]

Using T=readtable syntax is very slow. my challenges is i have date/time column on the data and Matlab recognises it as string and i tried to convert it to a number before using the [Num, Txt, Raw]=xlsread syntax having difficulty... i need to have column for time in the Num ... any help please

[Walter Roberson]

Is it a text file or a .xls or .xlsx file? Are you using MS Windows with Excel installed? Could you attach a small sample? And which MATLAB release are you using?

[Gali Musa]

Please see attached sample for the xlsx file. It does remove column for the date/time in NUm and i want it to be there because it uses wrong columns for the calculations due to the absence of the column. Thank you

[Fangjun Jiang]

T=readtable('Test1.csv');
NumTime=datenum(T.time)

[Walter Roberson]

"and Matlab recognises it as string"

For this purpose it is important that we know whether you are using MS Windows with Excel installed, and that we know which MATLAB version you are using. We also need a sample as xlsx not csv.

We need this information between there are multiple ways of encoding date/time information in Excel, and for some of them the result you will get in MATLAB depends upon which MATLAB version you are using and whether you are using MS Windows with Excel installed. Things that export as text dates are not necessarily stored as text.

[Gali Musa]

I'm using windows 7 with microsoft office 2013. I'm using Matlab R2016b. i tried uploading xlsx but not uploading(not supportted). Thank you

[Walter Roberson]

zip the .xlsx file and upload the zip

[Gali Musa]

Please find attached a zip folder. thank you

[Walter Roberson]

I checked inside the xlsx and find that the dates and times there are represented as excel numeric date format. readtable() would convert those to datetime objects by default.

If you find that xlsread() is faster for you than readtable, then you could try

Num = xlsread('Test1.xlsx', 1, '', 'basic')

Basic mode would use built in functions instead of talking to Excel, and would force the dates to be left numeric. The resulting numeric column would be in Excel date numbering format, such as 42208.5034722222 for 23-Jul-2015 12:05:00 . The easiest way to convert those to useable times would be

d = datetime(Num(:,1), 'ConvertFrom', 'Excel');

[Gali Musa]

Thank you so much for the useful information, however, xlsread() is faster

[Walter Roberson]

The magic way to force xlsread() to return a number for excel dates is to use 'basic' mode. If that is too slow for you, then fix your code so that it no longer expects to date to be a column in what is returned from the num output of xlsread.

For example you could change your code to use the raw output of xlsread(), so that you have all of the row numbering that you expect. Sample code:

[~, ~, raw] = xlsread('Test1.xlsx');
raw(1,:) = [];   %remove header
if ischar(raw{1,1})       %dates are string
 dates = datenum(raw(:,1));
 raw(1,:) = num2cell(dates);
end
num = cell2mat(raw);