Reading Excel files on a Mac

Trying to read some data from Excel files into Matlab, but my files seem to be causing quite a few problems. I'm working on a Mac (Matlab R2010a, Excel 2004 for Mac), so I attempt to read via:

[num,txt,raw] = xlsread(filename, 1, '', 'basic');

35 of the 41 files produce the following error:

 ??? Error using ==> xlsread at 234
 File could not be read by biffparse.  Invalid record ID.

while the remaining 6 files instead error with:

 ??? Error using ==> xlsread at 234
 File does not contain recognized Excel data.  Try saving as Excel 98.

The first error arises from a call to biffparse on line 470 of xlsread (trying to read sheet names), while the latter comes from a later call to biffparse on line 485 of xlsread (trying to read data from first sheet). Unfortunately, biffparse itself is a mex file, so I can't look further into exectly what it's stumbling over.

Each of my files include 30-35 worksheets, each holding a formatted table (mix and text and numbers). All are Excel 5.0/95 workbooks. Unfortunately I can't take Matlab's advice and save in '98 format, since this is not an export option in my version of Excel. Because of the high number of sheets, I'd really like to avoid converting all the files to .csv files. Anyone know what might be causing xlsread to stumble here? Or know of a tool that more reliably imports .xls files to Matlab sans COM server?

Answer 1

[Kelly Kearney]

Since I was unable to find a way to get Matlab to read these older files, and was unwilling to manually save all the worksheets to .csv, I resorted to (shudder) Applescript for the task. Veering off the Matlab topic, but in case any other Mac users out there encounter these Matlab/Excel incompatibilities, here's the script I used for the task:

--  excel2csv.applescript
--
--  Created by Kelly Kearney on 2011-08-31.
--
--  This script processes all .xls files in the directory and saves
--  all worksheets within the files to .csv files, named <file>.<sheet>.csv

-- User chooses a directory (b/c I can't figure out how to do this 
-- via input variables)

tell application "Finder"
  set inputDir to choose folder with prompt "Select a folder"
  set filelist to files in inputDir whose name ends with ".xls"
end tell

-- Convert all worsheets of Excel files to .csv files

repeat with x from 1 to count of filelist
  
  set f to item x of filelist
  
  
  set csvPaths to {}
  tell application "Microsoft Excel"
    open f
    set theSheets to worksheets of active workbook
    set workbookName to name of active workbook
    if workbookName ends with ".xls" then set workbookName to text 1 thru -5 of workbookName
    repeat with aSheet in theSheets
      set thisPath to (inputDir as string) & workbookName & "." & name of aSheet & ".csv"
      set end of csvPaths to thisPath
      save aSheet in thisPath as CSV file format
    end repeat
    close active workbook without saving
  end tell
  
end repeat

Answer 2

[Fangjun Jiang]

Hah, found this! Hope that is the cause of the problem.

[NUMERIC,TXT,RAW]=XLSREAD(FILE,SHEET,RANGE,'basic') reads an XLS file as above, using basic input mode. This is the mode used on UNIX platforms as well as on Windows when Excel is not available as a COM server. In this mode, XLSREAD does not use Excel as a COM server, which limits import ability. Without Excel as a COM server, RANGE will be ignored and, consequently, the whole active range of a sheet will be imported. Also, in basic mode, SHEET is case-sensitive and must be a string. And this is still required as late as R2011a. http://www.mathworks.com/help/techdoc/ref/xlsread.html

One cheap shot is to just try xlsread(filename) to see if it goes through.

In your Excel 2004 for Mac, can you do "Save As..." then in the dropdown list for "save as type", choose the version for '98 format? That is how I do conversions.

[Kelly Kearney]

The sheet name issue doesn't seem to be the problem; changing my command to

[num,txt,raw] = xlsread(filename, 'Table of Contents', '', 'basic')

still results in the error:

??? Error using ==> xlsread at 234

XLSREAD unable to read sheet Table of Contents.

File does not contain recognized Excel data. Try saving as Excel 98.

And no, I don't have the option to save in '98 format from my version of Excel. I did try resaving as "Excel 97-2004 & 5.0/95 Workbook", which is the only newer format offered besides the new xlsx format (which are not supported by Matlab at all on a Mac), and that produced this:

Warning: XLS File contains unrecognized string header - skipping

remaining text - (1027:0x7 57708).

along with empty results (num = [], txt = {}, raw = [NaN]).

An additional detail: xlsfinfo is unable to read the 35 files that throw the first error from my original question. The other 6 can be read by xlsfinfo without problem.

[Fangjun Jiang]

No more clues. Your suspicion is probably right. It's the old Excel format. Do you have to use 'basic' option on Mac? What you got if you just run xlsread(filename)?

[Kelly Kearney]

Yes, I have to use the basic option on Mac; it reverts to that even if I don't specify:

>> [num,txt,raw] = xlsread('~/Documents/Research/Data/Npafc/pre1992/1952.xls');

Warning: Could not start Excel server for import, 'basic' mode will be

used. Refer to HELP XLSREAD for more information.

> In xlsread at 176

??? Error using ==> xlsread at 234

File could not be read by biffparse. Invalid record ID.

[Fangjun Jiang]

It sounds like a dead end. You have Excel on Mac so it doesn't have the COM server. I think you best shot is to ask someone to convert the Excel files for you.

Answer 3

[Jai Davies-Campbell]

I use TextWrangler... just copy and paste into TW and save it as a .txt file and then load that into Matlab. Seems to work.

Example

Peel_Angle=load(['Aramoana_Cam1_2013_Peel_Angles.txt']);

Name=Peel_Angle(:,1); PA1=Peel_Angle(:,2); PA2=Peel_Angle(:,3);

change to suit