How Convert MS EXCEL Spreadsheet to an APL2 Array 
Author Message
 How Convert MS EXCEL Spreadsheet to an APL2 Array

I have data in Microsoft spreadsheet format - e.g. TABLE.CSV .
I would like to be able to import this table as an array in APL2
for Windows.  Can anyone here tell me how, or give me helpful
pointers please?

Gerry



Tue, 22 Jun 2004 05:11:28 GMT  
 How Convert MS EXCEL Spreadsheet to an APL2 Array
Gerry,

I can think of a couple of ways,,,

If you want to do this under program control, you could read the CSV file
with the process 10 external function Delta-FV.  This would give you a
vector of character vectors.  You could then use partition to split each of
the character vectors at the commas yielding a vector of vectors of
character vectors.  You could then use CTN to convert the elements
containing formatted numbers to numeric.

Alternatively, you could open the file interactively using CSV, copy the
data to the clipboard, and use PASTE_SPECIAL to share a variable with Excel.
If you use a left argument of 'XLTABLE DATA', the shared variable DDE
connection will do automatic type conversion and when you reference the
variable you will get a non-homogeneous nested array.

It is possible to start Excel, open the file, and establish the DDE
connection all under program control, if you would like more information,

(I think we may have a technote in the support section of our web site about
DDE and Excel too, but the site is down tonight and I can't check.  You
might want to look in the morning.)

David Liebtag
IBM APL Products and Services



Tue, 22 Jun 2004 12:32:30 GMT  
 How Convert MS EXCEL Spreadsheet to an APL2 Array
CSV format isn't a Microsoft specific format, it's a standard interface
format used to import and export data between many programs.  It's just
an ASCII text file with commas between the fields and (if I remember
right) quotes around the fields that contain text.  You can view the
file in a text editor to make sure it really is in CSV format and not
just in a file ending in .CSV.
Quote:

> I have data in Microsoft spreadsheet format - e.g. TABLE.CSV .
> I would like to be able to import this table as an array in APL2
> for Windows.  Can anyone here tell me how, or give me helpful
> pointers please?

> Gerry




Wed, 23 Jun 2004 09:44:19 GMT  
 How Convert MS EXCEL Spreadsheet to an APL2 Array
David Liebtag's suggestion is the neat way to go, but since you've stated
that you already are stuck with a CSV, I'll volunteer that I've got an APL
function that does it for me.  I can't say that it's bulletproof, in that
I've only ever used it for one personal financial application.

My function CSV does the parsing and, when it has assembled a token, calls a
customizable function TOKEN that, in my case, uses CTN to return numbers and
returns non-number representations as character strings.  CSV takes a
character vector argument, so you'll have to use your own favorite method of
reading the file, etc.

You're welcome to play with it.  However, since I'm a new contributor to
this group, someone is going to have to tell me the preferred method for
sending a pair of functions.  Perhaps it's just a matter of privately
communcating an ATF to the requestor.

Let me know.

Walt Niehoff


Quote:
> I have data in Microsoft spreadsheet format - e.g. TABLE.CSV .
> I would like to be able to import this table as an array in APL2
> for Windows.  Can anyone here tell me how, or give me helpful
> pointers please?

> Gerry




Wed, 23 Jun 2004 23:46:43 GMT  
 How Convert MS EXCEL Spreadsheet to an APL2 Array
I asked how I might bring a CSV Excel array into an APL2 workspace.  David
replied:


Quote:
> Gerry,

> I can think of a couple of ways,,,

> If you want to do this under program control, you could read the CSV file
> with the process 10 external function Delta-FV.  This would give you a
> vector of character vectors.  You could then use partition to split each
of
> the character vectors at the commas yielding a vector of vectors of
> character vectors.  You could then use CTN to convert the elements
> containing formatted numbers to numeric.

David:  Thanks so much for taking the time to reply.  But you passed me out!
It is over 10 years since I was hep with APL.  I have no idea what
"process 10 external function Delta-FV" is.  I have the User's Guide, the
Language Reference, and "APL2 At  A Glance" by Brown, Pakin and Polivka
here.  Where sjhould I look to learn about this please?

Quote:
> Alternatively, you could open the file interactively using CSV, copy the
> data to the clipboard, and use PASTE_SPECIAL to share a variable with
Excel.
> If you use a left argument of 'XLTABLE DATA', the shared variable DDE
> connection will do automatic type conversion and when you reference the
> variable you will get a non-homogeneous nested array.

I have tried copying the data in Excel to the clipboard, but I don't know
what
PASTE_SPECIAL is.  I can paste one line at a time into an APL workspace
and precede it by  ZZZ (is specified by) 'the pasted line'.  I can do it one
line at
a time and assemble it in that way.  But that is too tedious.

Quote:
> It is possible to start Excel, open the file, and establish the DDE
> connection all under program control, if you would like more information,


I will do that.

Quote:
> (I think we may have a technote in the support section of our web site
about
> DDE and Excel too, but the site is down tonight and I can't check.  You
> might want to look in the morning.)

I would like to do that too, but first  must get the address of your web
site.

Gerry



Mon, 28 Jun 2004 09:03:12 GMT  
 How Convert MS EXCEL Spreadsheet to an APL2 Array
Gerry,

APL2 now includes Associated Processors which allow you to call programs
written in other languages as if they were APL functions.  (They also allow
you to work with files as if they were variables and encapsulate workspaces
providing name isolation and easier code reuse.) Associated Processors 10's
primary purpose is to provides access to Rexx programs from APL2.  Processor
10 also includes some very easy to use and portable file IO routines.
Delta-FV is one of these routines.  You can find information about it in the
User's Guide in the Associated Processors chapter.

PASTE_SPECIAL is a function in the DDESHARE workspace in public library 2.
You can find documentation about it in the Supplied Workspaces chapter of
the User's Guide.

Our web site is http://www.ibm.com/software/ad/apl.  But, I have been unable
to get to it for the last couple of days.  The server seems to have lost the
APL2 pages.  If you can't get to them please be patient and try again
another day.  (I've written to the web masters asking them to look into the
problem.)

And finally, much of these facilities are not available in the base APL2
product.  They have been added in service releases.  You can download the
latest service from our web site.  If the web site is down and you need the

you.

Hope this helps.

David Liebtag
IBM APL Products and Services



Mon, 28 Jun 2004 12:09:20 GMT  
 How Convert MS EXCEL Spreadsheet to an APL2 Array
Interesting!  Somebody asked the same question a bit earlier.  Popular
topic!  I never saw a response to my earlier reply, but I'll repeat it here:

"David Liebtag's suggestion is the neat way to go, but since you've stated
that you already are stuck with a CSV, I'll volunteer that I've got an APL
function that does it for me.  I can't say that it's bulletproof, in that
I've only ever used it for one personal financial application.

"My function CSV does the parsing and, when it has assembled a token, calls
a
customizable function TOKEN that, in my case, uses CTN to return numbers and
returns non-number representations as character strings.  CSV takes a
character vector argument, so you'll have to use your own favorite method of
reading the file, etc."

Gerry, do you recall how to read a text file into a workspace using any
method?  (If not, you might want to spend some time reading the Users Guide
section on the FILE workspace.)  Once you get the file into the workspace as
a matrix or vector of vectors, you could use my CSV function to parse it row
by row or vector by vector.

Also, as to DDE mentioned by David:  I tried a simple function,
APL2_XLTABLE_CONNECT, in workspace DDESHARE today for the very first time.
Daggoned thing works very nicely.  With both Excel and APL running at the
same time, APL pulled a subarray out of the spreadsheet (by row/column
reference or by Excel subarray cell name) and handed me a matrix.  I found
it easier to read the APL2_XLTABLE_CONNECT description in the HOW variable
in the workspace than the user guide material.  But with an early success
and some advice from David, I now will get something out of reading the
Users Guide again.

If you want my CSV function, shoot me a note privately and I'll send it to
you.

Walt Niehoff



Tue, 29 Jun 2004 06:20:30 GMT  
 How Convert MS EXCEL Spreadsheet to an APL2 Array
Using APL2_XLTABLE_CONNECT will give better performance and results than
using CSV.  The XLTABLE format uses IEEE floating point so you won't pay the
rounding and performance penalties of converting to and from character
format.  XLTABLE also supports date, time, and error types.  And, in the
Excel to APL2 direction, it supports sparse arrays so the data transfer may
be significantly faster for large spreadsheets.

David Liebtag
IBM APL Products and Services



Tue, 29 Jun 2004 12:05:45 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Creating a MS Excel spreadsheet from Clipper

2. Having trouble converting array to spreadsheet string,...

3. convert txt excel file without open the excel application

4. Converting MS Fortran 5.1 into G77 (Allocatable Arrays)

5. Haskell and MS-Excel, MS-Access

6. convert 2d array to 1d array without using shift registers and build array

7. Manipulating Excel Spreadsheets in Dolphin

8. inserting blank lines (was Re: Preparing Excel spreadsheet forsearch engine)

9. Excel spreadsheet in a BLOB

10. Read Excel Spreadsheet into a .TPS File

11. create and name new worksheets in existing excel spreadsheet

12. How to read range from Excel 2000 Spreadsheet?

 

 
Powered by phpBB® Forum Software