Problem with xlsread.

Hello, everyone¡

I'm trying to read a .xls file using the xlsread code from matlab, after a lot of fail attempts, I just can not do it by my self, so I save the file in .txt format (from the .xls) and was easy to read using textscan, because I can differentiate each column of the data matrix with this simple format.

%f-%3c-%f %f:%f:%f %f %f %f

Well, I'm just wondering if is possible to read this kind of data, using the xlsread or any other code, directly from the exel file.

Thanks for your time.

.xls file

     Date:  [2009-12-13,2014-07-21]  
      
    [Min] m/s Velocidad de Viento[2m]  [Prom] m/s Velocidad de Viento[2m]
  2009-12-13 00:00:00  3.94  4.01
  2009-12-13 01:00:00  3.00  3.44
  2009-12-13 02:00:00  1.82  2.71
  2009-12-13 03:00:00  2.83  3.03
  2009-12-13 04:00:00  1.76  1.91
  2009-12-13 05:00:00  0.84  1.90
  2009-12-13 06:00:00  3.43  
  2009-12-13 07:00:00  1.55  2.15
  2009-12-13 08:00:00  0.18  0.80
  2009-12-13 09:00:00  1.05  1.47
  2009-12-13 10:00:00  3.35  3.72
  2009-12-13 11:00:00    3.89
  2009-12-13 12:00:00  4.75  4.77
  2009-12-13 13:00:00  5.11  5.15
  2009-12-13 14:00:00  4.49  4.69
  2009-12-13 15:00:00  3.64  3.90
  2009-12-13 16:00:00  3.12  4.490
  Date:  [2009-12-13,2014-07-21]

Answer 1

[Walter Roberson]

That particular .xls file is not readable with xlsread() for OS-X, at least not on my R2014a release (and probably not in versions up to R2015b either as that code did not change.)

The file is a binary .xls file; only text .xls and .csv and .xlsx files can be read in Basic Mode in MATLAB on OS-X or Linux or on MS Windows systems that do not have Excel installed.

You would need to arrange to have the file saved in a different format to read it in MATLAB on OS-X.

[Image Analyst]

It reads in fine on a Windows system with Excel installed. Though, of course with empty cells here and there, those will show up as Nan's. Can you read them on a Windows system and convert them to .xlsx?

[Walter Roberson]

If you have Excel for Mac, then you can Save As, and choose Excel Workbook (xlsx). It might give you a compatibility warning about not supporting Unicode, but none of the characters used are outside ISO-8896-1 so you can go ahead and save it.

Then still on OS-X you can use

[~, ~, raw] = xlsread('caleta_toro_direccion_del_viento.xlsx');   %note change in name for xlsx

I would not advise bothering with the first output (num) or second output (txt) as those will take effort to line up with the others due to header lines and strings.

Once you have the "raw" cell array, the first 4 lines are headers which you can put aside for now.

In row 5 to 46588 of the cell array, the first column will (in OS-X or Linux, but not necessarily MS Windows) come out as formatted strings that are the times, such as '2016-01-19 10:00:00'. You can use datetime() to interpret those if you have R2014b or newer, or datenum() if you have an older version.

In row 5 to 46572 of the cell array, the 4 columns after that will be numeric values. Note that these do not extend as long as the dates do: rows 46573 to 46588 have dates but no associated readings. On those rows, columns 2 to 5 will contain NaN

Rows 46589 and 46590 of raw will contain NaN values as those rows are empty.

Rows 46591 and 46592 of raw each contain two columns of text followed by 3 columns of NaN; these columns are trailer that contain no useful data.

Answer 2

[Image Analyst]

Yes it is possible to read the data directly from the Excel workbook file with the xlsread() or readtable() functions. If you want us to try, use the paper clip icon to attach your workbook.

[Javier]

Thanks Image Analyst, How are you?

I'm currently running Matlab on OS X El Capitan, I'm reading that a lot of peoples have problems trying to read Exel files from this platform, maybe is a software issue.

[Image Analyst]

Maybe. Let's see your software so we can determine that. You haven't given us anything to go on - no error message, no source code, no workbook attached, etc. If you let us, we might be able to help you. http://www.mathworks.com/matlabcentral/answers/6200-tutorial-how-to-ask-a-question-on-answers-and-get-a-fast-answer

Otherwise, call the Mathworks for technical support.

[Javier]

ok¡

[Image Analyst]

It reads in just fine with xlsread(). What is the problem?