Err, okay. Python has PyPI, and some very robust numerical processing and scientific plotting packages that make it largely capable of replacing a lot of the functionality of Matlab.
And the syntactic enforcement of whitespace makes Python programs eminently more readable than the glurge that is most Perl scripts. I like and use both languages, but Python is my tool of choice for any large scale scripting or numerical processing.
Stranger
I’m a perl junkie but if I had to recommend a language that does the kind of stuff that perl does to someone I’d probably not go for perl. Maybe ruby, maybe python, probably python; perl is great if you’re really comfortable with it, but it has way too many quirks IMHO.
But as you note, CPAN is amazing - for the OP: CPAN is the repository for perl extensions (libraries / modules) and I’ve yet to see anything like it for any other language. For some reason, there are perl extensions that do pretty much anything you’d need (even if the quality varies). For most common tasks you can easily find 3 or more solutions, and the http://search.cpan.org/ site also has user ratings and reviews - which is useful because some problems have too many solutions to make it easy to pick one.
If you can find perl modules that do what you want, and you probably can, it may still make sense to use perl instead of something else.
Ooooh…PyPl. Watch me shiver in my boots!
(I’m just kidding. I like Python a lot.)
Perl has numerous statistics, analysis and symbolic computation packages and more plotting interfaces than you can shake a stick at, as well.
Just try reading it if you like spaces instead of tabs.
Since I’m one of the laziest people I know, I’m all for making things easy! That’s why I write programs; because doing it by hand is tedious, and it’s impossible for some files.
Just to be clear: Perl and Python will write fixed-block text files (i.e., if the record length needs to be 350, then it will space-fill to 350 and the record will not end at the end of the data), right? What kind of input files will it read? And fields will not be separated in a text file? For example, position 1 might be C, position 2 through 7 might be 987654, position 8 through 11 might be 2600, position 12 through 41 might be JOHNNY LA INC (space-filled through position 41), and so on out to 350 bytes. It should come out as C9876542600JOHNNY LA INC[SPACES][etc] and not separated by any sort of delimiter.
Yes, you can make it do that, and they will both read numerous types of outputs but ASCII text processing is part of the core functionality of both languages;
Can do with ease.
Larry Wall, the creator of Perl, is trained as a linguist, and this is reflected in the multi-valued syntax of Perl. Its core strength is its ability to parse text in pretty much any manner you could possibly conceive. The type of functionality needed by the o.p. should be possible in nearly any language after a fashion (though to write something in C++ to do this would be nightmarish) but this is the type of thing Perl was created to do.
Stranger
If you’re on Windows I’d have a look into PowerShell as well. Very straightforward handling of things like Excel files.
My last Perl project was pretty much that. I had to read and write a bunch of fixed-length records to convert between an old format and a new format for submitting state unemployment tax. There’s probably a CPAN module for handling fixed-length fields in an OO-ish sort of way, but it’s easy enough to do it by hand.
Perl’s good about handling really long strings without requiring much effort on the part of the programmer. If you want to create a string of 350 spaces, it’s as simple as:
$foo = ' ' x 350;
It also has a substr function which lets you easily replace a range of characters in a string because it can be used as a lvalue (the stuff on the left side of an assignment operation). So if you wanted to insert ‘blah’ into a field that starts at position 50 and is 10 characters long, it’d look something like this:
substr($foo, 49, 10) = 'blah ';
Note that it’s using 49 instead of 50 because everything’s 0-indexed in Perl, and I had to pad out what’s being assigned to the substring with 6 spaces (otherwise it’d end up shrinking the string). But you can wrap that in a function that handles that stuff, and then you only have to worry about it once.
Also, on the Excel front, Perl has the Spreadsheet::ParseExcel and Spreadsheet::WriteExcel modules which make life a lot easier. I’ve used them to automate a lot of spreadsheet building at work that was previously done semi-manually.
Given that you are in a windows environment, and given that you work involves MS office tools I’m going to suggest VBA or .net as a solution.
VBA is built in to excel and is already mentioned in detail above.
.net is what I would use for your scenario. There are plenty of built in API’s that handle Ms office interoperability. You can get started building a nice looking app or more importantly, an efficient one pretty easily given .net’s level of windows integration. You also have a wide variety of languages to choose from: c++, c#, VB.net, J#, f#. Finally, it can be used to deploy to a number of different solutions. Need a webportal to interact with your data? Hello asp.net. Need an interactive online and desktop solution? Silverlight online (or desktop too), and windows forms or wpf for a desktop solution.
Another option if you want to do wonderful things with scripting (like AppleScript in the Mac world): AutoIt.
It’s free and it is a very powerful tool for knitting together Windows programs to do something useful.
Way,way back in 1989 I wrote a program in …GW BASIC. (It just reads two files of numbers, compares the data one line at a time and prints out any differences it finds)
The program worked perfectly the first day I used it, and it STILL works perfectly for my needs. (which are pretty damn simple 
)
So, yes, I still use a program from the stone age, once every day…
It ain’t broke, so I ain’t gonna fix it.
As others have said, simple. I think many people using computers today don’t appreciate that if you use a true programming language you are not limited by the assumptions of a package. You can use absolute spacing, as has been mentioned, if you have some sort of delimiter you can split on them, or you can even split on something you read in from another file.
As for input file flexibility, I often have to read and process gigantic files, so I pipe a gzipped file through gzcat and into my Perl script, which reads the plain text from standard input.
Basically, you can read input a line at a time from any source, and the input line becomes a string which you can do whatever you want with. If it makes more sense to read several lines you can do that. You can output the modified line immediately, or store it and output all of them in one batch.
You can get very fancy and efficient, but you can also do things very easily. You can probably do what you want in under ten lines (and even less if you want to be tricky.)
I used the GRAPH package for a very cool web interfaced package which lets you plot all kinds of field against each other, much of it non-numeric. Very handy because my VP wants to see relationships that no one would ever dream of.
Thanks for this. I don’t browse through packages as often as I should. I use Open Office, but I suspect it would read the Excel output fine. I sometimes want to format an output better than you can do in csv, like assign colors. I have a macro for it, but it would be easier to do it directly.
I’m an old Pascal programmer and teacher, and the {}s sometimes let me food myself that Perl is a structured language.
I’ve been on UNIX systems since 1980, so I know regexps just fine. Sometimes they make sense, and sometimes the job needs something else. However the hash is feature of Perl which has saved me the most time and the most lines of code. But that is more than the OP needs for his problem.
Thanks for starting this thread and to all others for additional comments. I am also starting to look at Perl as an alternative to Easytrieve, but it would be for my own ad hoc stuff in addition to one stream of batch jobs used for processing records of tape volumes, which is used for disaster recovery. The mainframe operating system ( z/OS ) has a set of ported tools that contains Perl, so that will be something that I look at down the road. Also, a co-worker mentioned Python, so there is another.
Thanks again,
DC
Glad I could help. Those two modules have saved me countless hours of work.
I’d be very surprised if it didn’t work with Open Office.
And there are a few caveats. WriteExcel tends to produce bad spreadsheets without warning if you try and write the same cell location more than once. So if your program logic runs the risk of doing that, you’re better off writing the data to an array first and then calling the various cell-writing functions.
Also, I don’t think it tracks identical formats. So if you add a format for bold text every time you want to make a bold formatted cell, you’re going to wind up with a bloated spreadsheet. I wound up writing my own format wrapper that tracks formats and returns an existing format object if a given format request is identical to one used earlier in the spreadsheet.
Forget all this PC stuff like C++ or Perl or Python – any of these would be a major change to change to the code, and a major change from the current sequential file processing design. And forget spending the money on SAS – Easytrieve is cheaper – just keep that, no point in buying a more expensive product.
If you want to replace EZT, just look at the latest features in DFSORT (or SyncSort). Your shop already has a sort package, so no additional expense there.
And probably nearly everything you do in EZT can be done by your Sort package, with fairly straightforward coding changes. (For example, the newest DFSORT can read exported Excel files directly, automatically removing commas, quotes, etc. and treating it as a fixed format file.)
See the Smart DFSORT Tricks manualfor examples. And if you have problems, ask at the Mainframe Sort forum, where you will get a quick response from the designers of the IBM DFSORT product.
SAS will do more than EZ Plus, but it will cost enormous amounts more than EZ Plus.
Are you running on a mainframe? Or is there some sort of EZ Plus extension that is running on a PC, now? If you are already running on a mainframe, I’d suggest that you just use REXX (that should already be there as part of the TSO/ISPF installation). I’ve gotten REXX to do as much as COBOL on some occasions, with parsing and other features. Table lookups are not quite as simple as the built-in code of EZ Plus, but I got around that my loading my tables as KSDS VSAM files and simply reading them with REXX. (And creating .csv records and exporting.csv files are a snap. I always used tab delimited fields, eliminating the need for fixed length fields or the hassle of throwing quotes around every flipping data element.)
I can see wanting to get away from Wang’s CA empire, but will the cost of re-writing all that code really make economic sense just to get a cheaper license?)
I almost never use the mainframe. I haven’t been primarily on a mainframe for years. EZT is running on my PC at the office.
Easytrieve, boy that takes me back. In the 80’s I worked for a competitor of Pansophic. EZ was a bit long in the tooth then. I wonder how much CA has dedicated to it since buying it in 1991? Not much, if true to form.
I may be way off here, so apologies if I am - but it sounds as if you have inherited a rather old system that has mutated greatly since its inception. Perhaps with changes made by non-IT staff to keep things working. It sounds as if the system started out on a System 3x box for the data and EZ as the report generator. Since then the data seems to have migrated to a PC (Excel?) but the reporting end is still the same - but requires additional processing (Access) to get it into a format that EZ can deal with.
If so, then looking at tools that can operate directly on Excel may be the best way to go. As noted, VBA is well integrated with Excel and can probably to all that you want to do - without worrying about exporting and reformatting the data. VBA is fairly easy to learn, lots of books and online tutorials.
Still though, VBA is getting rather old itself and will be dropped in the near future. MS has released VSTA, “Visual Studio Tools for Applications” as its replacement. VSTA is a part of Office 2007 applications.
These along with all the other suggestions for Perl, Python etc. will do the job for you - but I encourage you to step back away from the details and take a hard look at the design of the entire system rather than just replacing one part of the system.
Doing this, you may find it is not worth changing anything or that you can greatly simplify the entire system. Don’t forget that “If it ain’t broke, don’t fix it.”