J: reading data from an external file? (medium long) 
Author Message
 J: reading data from an external file? (medium long)

Okay, here's a question from a newbie.  (I'm still trying to find the
answer, but I figured one of you may know it already.)  For background, I'm
running J Release 3 (Professional Edition Version 3.02) on 95 or NT.

I'm trying to read a file created by a spreadsheet under Windows NT.  I
have 3 versions of the file: a.xls, a.csv, and a.txt.  I hope to use OLE to
link my results back into another Excel spreadsheet, but that comes a bit
later.  This data will be created by someone else's scheduled batch job
overnight and can be created in any one of the three formats.

Because a field (column) of the data can contain blanks, I decided to work
with a.csv first.  It nicely separates each field (column) with a ',', but
it surrounds each string with a '"'.

There are 8 columns and some 53 rows (plus one title row) in the original
spreadsheet.  I use 'm' fread to get the data into J. $a (where a is now
the name of the array) is 54 117.  If I try

$ ',' chop 2 { a

for example, I get 8, as I expect.  If I try

$ ',' chop 1 2 { a

I get 4!  Doing ',' chop 1 2 { a, I get the first 3 columns boxed as I
expect and then the last 5 in the same box.  Doing

$ ',' chop a

gives 1, as everything is now in one box.

(And yes, I tried $ ',' chop 1 { a and got 8, too; it's just doing more
than one line at a time that makes problems.)

I thought the fact that there were '"' marks delineating the strings would
be a problem, so I tried

',' chop 1 2 { '" ' charsub a

but that just replaces '"' with ' ', as expected; the boxing is still
wrong.

I then got the idea that the fret at the end of the line may be the
difficulty, so I did the following:

test =. 1 2 { a
testc =. 2 117 $ (toJ {. test),(toJ }. test)

(Oops: nonce error, whatever that is.)

testc1=. toJ 0 { test
testc2=. toJ 1 { test
testc=. 2 117 $ testc1,testc2
$ ',' chop testc

which gives 5!  The last box was now split in two, but in different places
in each row!  

Does anyone have any ideas of what causes this to work this way?  As I say,
I'm continuing to push ahead, but I do need to show results (in addition to
learning more and more about J).

Also, if it is better to link to a.xls directly via OLE or to process the
a.txt file, I'd listen to that sort of advice, too.

Thanks in advance,

Bill

--
Bill Harris                             Hewlett-Packard Co.
R&D Productivity Department             Lake Stevens Division

phone: (206) 335-2200                   8600 Soper Hill Road
fax: (206) 335-2828                     Everett, WA 98205-1298



Mon, 24 May 1999 03:00:00 GMT  
 J: reading data from an external file? (medium long)

Quote:
Bill Harris writes:
>Because a field (column) of the data can contain blanks, I decided to
work
>with a.csv first.  It nicely separates each field (column) with a ',',
but
>it surrounds each string with a '"'.

>There are 8 columns and some 53 rows (plus one title row) in the original
>spreadsheet.  I use 'm' fread to get the data into J. $a (where a is now
>the name of the array) is 54 117.  If I try

The data you read in is a character matrix of the form:

   dat
one,two,three
1,2,3        
4,5,6        
7,8,9        
12,15,18    

To split each row on the comma, use the chop function with rank 1,
i.e. apply chop to each row, thus:

   ','chop"1 dat
+---+---+---------+
|one|two|three    |
+---+---+---------+
|1  |2  |3        |
+---+---+---------+
|4  |5  |6        |
+---+---+---------+
|7  |8  |9        |
+---+---+---------+
|12 |15 |18       |
+---+---+---------+

Chop will work with a matrix directly - if the delimiter is vertically
aligned (and therefore not in this case).

If your first row is column headers, and the rest is numeric data,
try:

   dat=. ','chop"1 dat

   hdr=. {.dat
   val=. 0 ".&> }.dat

   hdr
+---+---+-----+
|one|two|three|
+---+---+-----+
   val
 1  2  3
 4  5  6
 7  8  9
12 15 18

Using .csv files is probably fine for what you are doing.



Mon, 24 May 1999 03:00:00 GMT  
 J: reading data from an external file? (medium long)

Quote:

>I'm trying to read a file created by a spreadsheet under Windows NT.  I
>have 3 versions of the file: a.xls, a.csv, and a.txt.

I have had good success using ODBC to read .xls files.  I haven't tried
writing back to them, but I assume that would work as well.

N.D.



Tue, 25 May 1999 03:00:00 GMT  
 J: reading data from an external file? (medium long)


Quote:
> Because a field (column) of the data can contain blanks, I decided to work
> with a.csv first.  It nicely separates each field (column) with a ',', but
> it surrounds each string with a '"'.

You might want to try something like:

require'files misc convert'
a=.   fread'c:\temp\test.csv'
b=.chop a
c=.',' chop each b

/Gosi



Fri, 28 May 1999 03:00:00 GMT  
 J: reading data from an external file? (medium long)

Thanks to all who responded.  

cdburke's response seems to work; I just need to deal with the fact that
some of the "values" in the spreadsheet are strings, not numbers.  Some of
the others should, as well, but that was the most complete solution.

The response about ODBC prompted me to read that section of the manual.
That appears to be a very good solution; unfortunately, I'm running 16 bit
Microsoft apps and 32 bit J (and checking into upgrading Excel).  

Thanks,

Bill

--
Bill Harris                             Hewlett-Packard Co.
R&D Productivity Department             Lake Stevens Division

phone: (206) 335-2200                   8600 Soper Hill Road
fax: (206) 335-2828                     Everett, WA 98205-1298



Sat, 29 May 1999 03:00:00 GMT  
 
 [ 8 post ] 

 Relevant Pages 

1. Read VFP data files into Clipper data files

2. Tested routine for simulting external read of ascii data (for omission detection)

3. Reading a data file from a very remote data

4. help reading negative values in data file - test code and test data

5. JS-EAI with *JS*-callback

6. js.exception 3279.js

7. Reading a binary file / writing binary data to a file

8. reading in external file in clarion4 eval

9. ? logo basics. loading external data file to array

10. Reading External .txt files in Quartus II

11. reading text from external files

12. reading variables from an external file using expect

 

 
Powered by phpBB® Forum Software