I am using Universe on a Unix box, is there a way to process an excel
file in Universe

On Jan 19, 3:41*pm, Sami <samimassar... (AT) gmail (DOT) com> wrote:
I found (love the google) an xls2tab perl script that works pretty
well for xls files; it has the advantage of also standardizing dates
and numbers. Works great but is not particularly fast; haven't tried
in on the newer xlsx, xlsb, etc type files (what is an xlm file?)

I suppose another way to do it is to utilize an accuterm windows
script to invoke Excel on your workstation and have it do the
conversion to tab delim or other output format that you can then parse
& process in UV. I do that in the reverse, to build "real" excel files
when necessary.

/Scott Ballinger
Pareto Corporation
Edmonds WA USA
206 713 6006

Sami wrote:
Related question here:
nospamNebula-RnD.com/blog/tech/mv/2010/08/free-utils1.html
See notes on ExcelExport.

That gives you one CSV file for each sheet. (Note that utility may
not work for Excel 2007/2010, but it can be enhanced.)

For a complete *nix solution, Google for something that does what
ExcelExport does, get the data to a host OS file, then pull it into
Universe.

These days, the solution to "how do I do X with MV" is frequently not
to do X with MV at all, but to do X with tools that are designed to do
X, and then to pull the data into MV.
I blogged on that topic too:
nospamNebula-RnD.com/blog/tech/mv/2009/08/mv-to-anything.html

HTH

On Jan 19, 6:41*pm, Sami <samimassar... (AT) gmail (DOT) com> wrote:
We are on QM but the concept is the same.

We put the excel file on a samba drive. Set a pointer to the
directory. Open the record in basic and just process it.

Eugene

eppick77 wrote:

Eugene, in order to "open the record" and "just process it", the
record (excel file) needs to be text (XML), not binary. Excel files
can be either, depending on where they came from. I'd be interested
in both your source file format as well as the QM interface.

I'll mention that years ago I proposed to PS/RD/TL that they publish
the API for the OPEN Systems File Interface so that I could create a
driver which would indeed allow us to do something like this:
OPEN "XLS:WORKBOOK.XLS,SHEET1" TO FV ELSE STOP
READ ROW1 FROM FV,"1" ELSE STOP
COL_B = ROW1<2> ; * etc

In their wisdom the folks at PS/RD/TL have never seen that as having
any value, I bring up the subject again every couple years. That
could also be extended to web services:

OPEN "WS:http://myserver/ws/GetCity" TO FV ELSE STOP
READ RESULT FROM FV,POSTALCODE ELSE STOP
CRT "City is ": RESULT<1>

BASIC can be used for a Lot of things like this, if only the DBMS
vendors had a little vision.

I believe it's possible to create drivers like this for QM. Using the
C interfaces in Universe, Unidata, QM, D3, and others, it's possible
to do all of this with slightly irregular syntax. And that is another
answer for the OP's question "is there a way". Yes, Universe/*nix can
reach out to a remote Windows server and retrieve and process a binary
Excel file, as can any other MV system, but it would take some coding.

T

On 01/20/2011 10:24 AM, Tony Gravagno wrote:
[nods]
Very useful idea.

[nods again]
Another very useful idea. Long-term even more useful IMO.

Cheers, SDM -- a 21st Century Schizoid Man
Systems Theory project website: http://systemstheory.net
find us on MySpace, GarageBand, Reverb Nation, Last FM, CDBaby
free MP3s of Systems Theory, Mike Dickson & Greg Amov music at
http://mikedickson.org.uk

On Jan 20, 1:24*pm, Tony Gravagno <tony_grava... (AT) nospam (DOT) invalid>
wrote:
Tony,

Agreed that we can not process the file if it has binary. However, we
save the excel file as eithe .txt or .csv and place it into a samba
directory.

We put a pointer (although you could put the file path in the program)
that points to the directory.

14= OPEN "IMPORT.FILES" TO F.IMPORT.FILES ELSE PRINT "CANNOT
OPEN IMPORT.FILES" ; STOP
15= PROMPT ""
16= CLEAR=@(-1)
17= EOS = @(-3) ;*--Clear to the end of the screen
18= EOL = @(-4) ;*--Clear to the end of the line
19= RTN=" - <RTN> TO CONTINUE"
20= PL=@(0,22)
21= TAB=CHAR(9)


24= LOOP
25= PRINT "HAS FILE BEEN SAVED AS A TAB DELMITED FILE AND
PLACE IN import_files ?":; INPUT RESP
26= IF RESP # "Y" AND RESP # "N" THEN CONTINUE
27= IF RESP="N" THEN STOP
28= IF RESP="Y" THEN EXIT
29= REPEAT
30= READ REC FROM F.IMPORT.FILES,"BDS_NO_SALES_REP.txt" ELSE
31= MESSAGE="CANNOT READ BDS_NO_SALES_REP.txt FROM
import_files"
32= GOSUB MESSAGE.ROUTINE
33= STOP
34= END
35= CONVERT TAB TO @VM IN REC
36= CONVERT CHAR(13) TO "" IN REC
37= MAX.CNT=DCOUNT(REC,@AM)
38= FOR CNT=2 TO MAX.CNT
39= LINE=REC<CNT>
40= CONVERT @VM TO @AM IN LINE
41= CUST.NO = TRIM(LINE<1>)
42= NAME = TRIM(LINE<2>)
43= CITY = TRIM(LINE<3>)
44= STATE = TRIM(LINE<4>)
45= ZIP = TRIM(LINE<5>)

Hopefully that gives you an idea. We also go the other direction and
build .csv files from out data to export to the outside.

Eugene

"Tony Gravagno" <tony_gravagno (AT) nospam (DOT) invalid> wrote

Tony,

To get Excel to open and process a file there is another way. Create a tab
delimited text file and give it a .xls extension. Excel will open this as
though it is an Excel file, though in fact it is running the text import
silently. We do this all the time.

Agreed wholeheartedly about your OSFI ideas. Big trick missed.

Brett

On Jan 21, 10:05*am, "Brett Callacher" <bre... (AT) gpmdev (DOT) co.uk> wrote:
Yep. Did a lot of that last year using mvBase to grab tab delimited
excel spreadsheets, cleanse and transform the data, writing it back
out to other tab delimited excel spreadsheets as part of a data
migration from legacy (non-Pick) systems to SAP.

On 2011-01-21 08:32:26 -0500, Mike Preece <michael (AT) preece (DOT) net> said:
I'm involved in a Pick to SAP migration these days. God, does SAP
suck. Good thing it is so much cheaper than Pick.

--
Kevin Powick