Processing xls, xlsx, xlm file in Universe
Sami
file in Universe
Scott Ballinger
> I am using Universe on a Unix box, is there a way to process an excel
> file in Universe
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
Tony Gravagno
>I am using Universe on a Unix box, is there a way to process an excel
>file in Universe
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
eppick77
> I am using Universe on a Unix box, is there a way to process an excel
> file in Universe
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
Tony Gravagno
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
sdavmor
> eppick77 wrote:
>
>> On Jan 19, 6:41 pm, Sami<samimassar...@gmail.com> wrote:
>>> I am using Universe on a Unix box, is there a way to process an excel
>>> file in Universe
>>
>> 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
>
> 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
[nods]
Very useful idea.
> 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>
[nods again]
Another very useful idea. Long-term even more useful IMO.
> 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
--
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
eppick77
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
Brett Callacher
"Tony Gravagno" <tony_g...@nospam.invalid> wrote in message
news:2hugj69pp6ntjfn7f...@4ax.com...
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
Mike Preece
> "Tony Gravagno" <tony_grava...@nospam.invalid> wrote in message
>
> - Show quoted text -
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.
Kevin Powick
> as part of a data
> migration from legacy (non-Pick) systems to SAP.
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
Tony Gravagno
Tab-delimited files are Not "Excel spreadsheets". That is a low-level
mechanism to import/export data with Excel, as with other
environments. In this market Pick people equate CSV to Excel, but
end-users who use Excel every day don't equate the two. Excel is a
powerful tool that does a Lot of stuff. Plain text columns and rows
pale in their ability to express data compared to rich Excel
worksheets. If someone asks for Excel and you give them CSV/tabbed
files, it's like someone asking for wine and getting water.
[AD] For real Excel spreadsheets from MV, see NebulaXLite:
nospamNebula-RnD.com/products/xlite.htm
[/AD]
The original request was for moving Excel spreadsheets into Universe.
For raw, unformatted data, conversion to CSV is appropriate as that
doesn't bring with it the formatting. But for moving data To Excel as
we've seen discussed here, I strongly urge you to consider options
othr than CSV/tabbed files.
Other web pages on the topic (including solutions to the OP request) :
http://www.pickwiki.com/cgi-bin/wiki.pl?CSV
That links to Universe BASIC code for CSV2ARRAY and CSV2FIELDS.
However note that the solution implies the source data has already
been extracted to CSV, which is what my ExcelExport does.
http://www.pickwiki.com/cgi-bin/wiki.pl?ConnectToMicrosoftExcel
HTH
Tony Gravagno
Nebula Research and Development
TG@ remove.pleaseNebula-RnD.com
Nebula R&D sells mv.NET and other Pick/MultiValue products
worldwide, and provides related development services
remove.pleaseNebula-RnD.com/blog
Visit PickWiki.com! Contribute!
http://Twitter.com/TonyGravagno
Brett Callacher
Tony,
I see you point about the OP request - I think I misunderstood that to mean
produce a spreadsheet hence my response.
However, I think that your point about equating CSV to Excel is misdirected.
Just because the method described gets data into Excel, does not imply that
one need stop at that point. We use automation to format the Excel report
and this is all database driven and includes Pivot Table support etc. It
works well IMO - I have no reason to believe that your product doesn't too.
Brett
Tony Gravagno
>I see you point about the OP request - I think I misunderstood that to mean
>produce a spreadsheet hence my response.
>
>However, I think that your point about equating CSV to Excel is misdirected.
>Just because the method described gets data into Excel, does not imply that
>one need stop at that point. We use automation to format the Excel report
>and this is all database driven and includes Pivot Table support etc. It
>works well IMO - I have no reason to believe that your product doesn't too.
Brett - understood. It wasn't my goal to be all encompassing, just to
make a general point for those to whom it applied. It's very
difficult to express subtleties in this medium and to accommodate too
many of the exceptions, without getting overly verbose - a line which
I believe I crossed once. ;)
"Most" MV developers who are asked for Excel give CSV. Even many
professional IT people who are asked for Excel from their management
will import with CSV and then spend hours to days manually formatting
for readability and aesthetics. I've seen this many times, it was the
inspiration for NebulaXLite. End-users just accept that this is what
comes from their Pick system. It's great that you post-process the
data after import. I believe your users, compared to others, would
have less of an urge to replace the old Pick box with something that
works with their modern tools. As always, I don't care what solutions
are used, just keep your end-users happy, as you're doing, and we'll
all stay in business for a little longer.
Thanks.
T
mvdbman
wrote:
> Brett - understood. It wasn't my goal to be all encompassing, just to
> make a general point for those to whom it applied. It's very
> difficult to express subtleties in this medium and to accommodate too
> many of the exceptions, without getting overly verbose - a line which
> I believe I crossed once. ;)
I believe I hurt myself when I fell off my chair, laughing!
-Bruce H