Re: Tip for loading Excel Spreadsheet into Octave on Mac

[Henry F. Mollet]

Saving an Excel worksheet as CSV *does* work as you say and if I quote the
name, as I should, it can handle a white space. The same applies to BBEdit
if I  *had quoted* the filename.

octave:12> load "Test&test Excel.csv"
octave:13> whos
*** local user variables:
prot  type                       rows   cols  name
====  ====                       ====   ====  ====
 rwd  matrix                       39      7  Test_test_Excel

The changes you made about a year (thank you), to allow loading Mac Excel
CSV files, work fine but here are the reasons why I prefer to cut and paste
into BBEdit, then save as Unix, compared to saving as an Excel.csv file:

1. My Excel spreadsheets contain multiple sheets and only the working sheet
can be saved as .csv.

2. Even my working sheet is messy with charts and I really have to cut and
paste into new Excel file. I might as well start with a clean slate using
BBEdit.

3. When I amend and resave the file in Octave, the BBEdit file will reflect
that but not the Excel file. I.e. the following automatically appears in the
BBEdit file but not in the Excel file:
# Created by Octave 2.1.46, Wed Feb 04 18:50:05 2004 PST
<address@hidden>
# name: InputMatrix
# type: matrix
# rows: 39
# columns: 7

4. Possible future use of BBEdit because it allows opening of a file in
Hexdump (see below for first block). I have binary Systat file which I'd
like to convert directly into octave data files one day. I was hoping that
if I could look inside of a Systat binary file and inside of an Octave
binary file, I might be able to "doctor" the Systat binary file so that
Octave would be able to load (open) it.

However, you explained in a recent post why I cannot load binary files on my
Mac, so there was no point even trying to doctor my Systat binary files:
> From other posts, I seem to remember that you are using Mac OS X.
> Perhaps the problem is due to an old bug in the C or C++ library on
> that system that causes binary reads to report the wrong number of
> bytes or the wrong status at EOF (I can't remember the precise details
> of the bug, just that there was one -- maybe someone else remembers
> more can provide additional information).
Henry
N.b. Hexdump (first block)
0000: 23 20 43 72 65 61 74 65 64 20 62 79 20 4F 63 74     # Created by Oct
0010: 61 76 65 20 32 2E 31 2E 34 36 2C 20 54 75 65 20     ave 2.1.46, Tue
0020: 46 65 62 20 30 33 20 31 33 3A 31 36 3A 31 35 20     Feb 03 13:16:15
0030: 32 30 30 34 20 50 53 54 20 3C 6D 6F 6C 6C 65 74     2004 PST <mollet
0040: 40 48 65 6E 72 79 2D 4D 6F 6C 6C 65 74 73 2D 43     @Henry-Mollets-C
0050: 6F 6D 70 75 74 65 72 2E 6C 6F 63 61 6C 2E 3E 0A     omputer.local.>¿
0060: 23 20 6E 61 6D 65 3A 20 49 6E 70 75 74 44 61 74     # name: InputDat
0070: 61 0A 23 20 74 79 70 65 3A 20 6D 61 74 72 69 78     a¿# type: matrix
0080: 0A 23 20 72 6F 77 73 3A 20 32 36 0A 23 20 63 6F     ¿# rows: 26¿# co
0090: 6C 75 6D 6E 73 3A 20 36 0A 20 36 20 33 30 20 30     lumns: 6¿ 6 30 0
00A0: 2E 34 39 20 30 2E 39 35 32 20 30 2E 39 35 32 20     .49 0.952 0.952
00B0: 30 2E 31 35 35 0A 20 35 20 33 30 20 30 2E 31 39     0.155¿ 5 30 0.19
00C0: 20 30 2E 39 35 31 20 30 2E 39 35 31 20 30 2E 33      0.951 0.951 0.3
00D0: 32 0A 20 35 20 33 30 20 30 2E 35 31 20 30 2E 38     2¿ 5 30 0.51 0.8
00E0: 39 34 20 30 2E 38 39 34 20 30 2E 33 31 36 0A 20     94 0.894 0.316¿
00F0: 37 20 33 30 20 30 2E 36 37 20 30 2E 38 36 20 30     7 30 0.67 0.86 0


on 2/4/04 7:52 PM, John W. Eaton at address@hidden wrote:

> On  4-Feb-2004, Henry F. Mollet <address@hidden> wrote:
> 
> | There may be more sophisticated ways to get an Excel Spreadsheet into Octave
> | on Mac but this works for me and I don't have to explicitly change the
> | carriage return (\r) of Mac OS into newline (\n) of Unix. I suggest that
> | this is suitable for medium sized data matrices, which would be cumbersome
> | to enter in octave at the command line prompt but not that large that a
> | spreadsheet wouldn't really work either.
> | 
> | Copy your spreadsheet (I'm using Excel) and paste it into BBEdit Lite 6.1
> | for OS X. BBEdit has a Unix save/save as option. Octave will load this file:
> | 
> | octave:83> load Test&testfile
> | octave:84> whos
> | *** local user variables:
> | prot  type                       rows   cols  name
> | ====  ====                       ====   ====  ====
> |  rwd  matrix                       39      7  Test_testfile
> | Octave even takes care of the "&" in the filename and renames the matrix but
> | it cannot handle a space.
> | 
> | Now the name of the matrix "Test_testfile" can be changed to say
> | "InputMatrix" (so it will the same as in the script which will use the file)
> | octave:92> InputMatrix=Test_testfile
> | 
> | Now the file can be saved with the same name *in octave*
> | octave:94> save Test&testfile InputMatrix
> | 
> | BBEdit will even automatically amend the file with
> | # Created by Octave 2.1.46, Wed Feb 04 18:50:05 2004 PST
> | <address@hidden>
> | # name: InputMatrix
> | # type: matrix
> | # rows: 39
> | # columns: 7
> | Henry
> 
> If all you have is numeric data with no missing values, then Octave's
> load command should be able to read it if you save it in CSV file.
> For example, I loaded the following data
> 
> 1,2,3,4
> 5,6,7,8
> 
> 
> stored in a file called "foo bar" with no problem using the command
> 
> x = load ("foo bar");
> 
> with Octave 2.1.50 (running on a Debian x86 system, but I don't think
> that detail matters).  The file has only CR and no NL characters in
> it.  The result of loading the file is the matrix
> 
> x =
> 
>   1  2  3  4
>   5  6  7  8
> 
> Note that this method also handles filenames with spaces and allows you to
> rename the data to whatever variable name you would like.
> 
> Does this not work for you on OS X?  Can Excel save data in this
> format, or does it insist on quoting fields in some way, even when you
> save numeric data in CSV format?
> 
> jwe




-------------------------------------------------------------
Octave is freely available under the terms of the GNU GPL.

Octave's home on the web:  http://www.octave.org
How to fund new projects:  http://www.octave.org/funding.html
Subscription information:  http://www.octave.org/archive.html
-------------------------------------------------------------