I write programs using an ancient sequential program called Easytrieve. Basically, this is what they do:
Reformat the file;
Add fields;
Compare the current file with the previous file and use data from the previous file when there is a match on the key field;
Add numeric fields together to create a new field (or two replace two or more fields with one);
Add numeric fields together and compare them to the Total field. If they don’t match within set parameters, then the record is written to a different file;
Display a summary of the number of records that meet various parameters;
Write two output files, plus one or more files (non-matching totals, rejects, etc.);
Scan fields to remove and/or replace characters;
Sort files…
You get the idea.
We’ve just renewed our license for this program, and it occurs to me that we could save some money if we used something else. I took a very short course in SAS a few years ago, and it seems similar to Easytrieve. Is there a license fee for SAS?
What about C++? Is it free? I know nothing about OO languages. How difficult would it be to transition from a sequential language to OO, in order to do the things I listed?
What other alternatives are there?
FWIW, this is how we have to process data now: Open an Excel file. Remove commas, double-quotes, and non-display characters and save as comma-delimited. Import the file into Access. Export as fixed-format Text (i.e., define start positions for each field, and lengths). Run the .txt file through Easytrieve(s) for the desired outputs.
You don’t want to go anywhere near C++ It is the wrong answer to almost any question. (Take this from someone who codes in it daily.)
There are a whole heap of important meta-questions that you probably need to answer. Whilst it sounds as if you are stuck in the stone age, change for the sake of it in any critical application is to be avoided. If it isn’t broken, don’t fix it. In any change like this there is risk, and acessing this risk is the single most important part of any decision to change. (Vendors are well versed in locking customers in like this.) You would really want to cost out the savings in license fees versus the time needed to transition to a new platform, and factor in a substantial factor to cover the risk of something going wrong. You would need to consider running the two systems side by side for a reasonable amount of time, both to ensure that the new one is bug free, and to avoid the risk of catastrophe if bugs in the new system were found. All adds to the cost.
But there could easilly be upsides. In addition to saving money it may free you to do more interesting things - add more value to whatever the results are. Plus a more modern system may be more maintainable and take less time to modify and maintain.
Just for the hell of it I’ll mention the Python langauge as something you might find worthwhile. If I was hacking up something like you describe that is what I would use. But it may be that what you really need is a simple conventional database. You really need to do some sort of requirements analysis before going much further.
It also sounds as if you are not exactly well versed in current computing. Asking if C++ is free is a strange question. You didn’t specify a platform, although one assumes some version of Windows. Yes there is free C++ for Windows. Both GNU C++ and Windows Visual Studio Express for a start. Python is free too.
I could do all that with a shell script but Perl would probably be better. Sorry I don’t really have much help to offer except that scripting languages are easier to learn than real programming and might be worth looking into. I’m not aware of any language that you’d use for this that isn’t free.
Since this starts with an Excel file, you might consider VBA. VBA is a flavor of Visual Basic that is built into Microsoft Office applications and runs as a guest under the application (you cannot write a standalone program using VBA).
(I’m not sure that I follow why you have the step of importing to Access then exporting to another format. Do you use the Access format again, or just use Access to do some sort of format change? Seems like Excel could directly generate what you need.)
Most programming languages worth using have free versions or ports these days, except for some specialized ones - and those are usually part of a much larger toolkit (for instance, languages that are part of CAD suites).
If your input files are pretty much formatted text files, any number of languages that have good support for manipulating text could be candidates. Perl and Python have already been mentioned, and both are probably good candidates. For what you’re asking, C++ is almost certainly much too low-level and complex - the only reason to consider it at all is if you’ve got so much data that processing time becomes an issue, but with these kinds of formatting/reporting tasks, you’re almost always limited by disk speed anyway (meaning processing itself is already faster than reading and writing the data from the hard drive even with a relatively slow - and easier to use - language).
But it sounds like you’re planning to write the software yourself, and you don’t seem to be experienced at all in doing that. Depending on the complexity and cost of the current solution*, the importance of the task it’s doing and the amount of time you’re willing to spend learning how replicate it, I’d say chances are that you’d be better off either keeping what you’ve got (if it’s relatively cheap) or hiring someone else to do the work for you (if it’s that expensive and easy to specify). Very rough estimate assuming that it is indeed as simple as you make it look; if you’re not going to save a couple of thousand dollars over the lifetime of the new solution, it’s probably not worth starting.**
Oh, and if you have to ask about the pricing of SAS, chances are it’s more expensive than your current solution.
and no offense, but IME as a software developer/consultant users generally are extremely good at underestimating complexity.
** ETA: if it is going to save you a lot more money than that, it may be wise to pay someone to investigate your problem first.
FYI, SAS is quite pricey. It’s a great product, pretty easy to learn, and very powerful, but it’s not cheap. It’s not a one-time cost either. There’s an annual fee.
Our license allows one user, and the program must reside on a single computer (not the server). So I’m the only one who uses Easytrieve. I’d have no problem converting my programs to a different language – if I knew another language. The company would probably pay for classes for me to learn another language, but I’d pay for it myself if I had to just to make myself more marketable in case my situation changes. We’re a fairly small non-profit, and they paid for one person’s Access classes. She built the database, and then quit and got a new job. I’d take up the slack, but my Access skills are limited to importing and exporting files. I’d have to look at a couple of older databases to see how I did a simple formula. Anyway, TPTB are still a little angry that this woman used their money for her training and then quit.
Half of the data I work on needs to be output as fixed-format text. Formats are 350-byte records or 1485±byte records. The other half of the data is written as .xls and .csv format. Those could be done using VLOOKUP in Excel, only I was not successful when I tried it. We’re talking about a few hundred to 90,000 records (average about 3,000 records). When I’ve tried VLOOKUP I quit Excel after it had been running for 12 hours and hadn’t finished – on one of the shorter files. I don’t know if it’s because we’re using Excel 2003 and it’s slow, if my computer is slow, or if the coding was wrong. But I’m confident that my coding was correct. (I got help here.)
Python. Is that the one with the Ministry of Silly Coding?
My first motivation for looking into switching to a new language is to save the company the (rather expensive) license fee. My second is to have something that any of us can use instead of just me. (My boss and coworker are not technically-inclined, so it would have to be something like: 'Name the Excel file [filename]. Open [program]. Click on ‘Run program’. I’m far from being an Excel guru, but it was surprising what they didn’t know how to do.) Thirdly, I’d like to make myself more marketable. I like my job and have no intention of leaving. I like the small-company atmosphere and that they let me telecommute; not to mention the people. But in today’s environment it is not good to take things for granted. Last, I just like learning for the sake of learning.
Something that reads an Excel or text file directly and writes both would be great. Can you tell me more about VBA?
Access is used to make a fixed-format text file. Excel has a 240(?)-byte record length limit when writing a text file, or else the fields are not in fixed positions. In the former case, records are written to the limit and then the rest of the records are written at the end of the file. Like this:
Record 1… [reaches limit]
Record 2… [reaches limit]
Record 3… [reaches limit]
… rest of Record 1
… rest of Record 2
… rest of Record 3
Easytrieve reads one record at a time, so this won’t work. It also requires that data be in fixed positions. Writing a text file the other way (tab delimited) results in a text file that does not have fixed field positions. So the file is imported into Access, where it can be written as a fixed-block, fixed-position text file. If I could write such a file with Excel, then I’d bypass Access altogether.
No, I don’t want to write the software. I’d just like to learn an off-the-shelf language that does what I need it to do. I don’t know how much the license for Easytrieve costs, but I have it in my head that it’s a couple-grand a year. I know that our partners use DataStage, but I think it’s pretty expensive as well. I think it costs a couple of kilobucks to buy, and I don’t know about license fees for it. I think it might do what I want, but I’m not sure and would have to be trained on it in any case.
SAS seems to do everything Easytrieve does and then some. It would certainly make me more marketable, should the need arise, but I didn’t know about the license fees. Thanks.
If you have access to SAS it will do everything you need to do and can make use of all the inputs. In other words, you could probably do everything in one program.
Since you are using easytrieve, you are working in a mainframe environment, yes? Probably a medium to large corporation? If so, SAS may be available on your mainframe. Or, there may be an enterprise version on a server somewhere in your company.
I don’t know how large your company is, but check with the server group (?) to see if a SAS server is available. As already mentioned, check to see if SAS is on the mainframe (warning: mainframe SAS is a bit kludgier than windows or *nix based SAS).
To my knowledge, there’s no version of SAS for small businesses and no scaled-down desktop version.
If SAS is not available to you, the idea of using VBA (or perhaps windows scripting, assuming a windows environment) to pre-process the data might be the best idea.
The SAS class I took used the mainframe-based system. We’re an association of non-profit companies, and our office has ~20 people. Not very big at all. We have an AS 400, but most of our work is done on PCs. I don’t think they’ll spring for the cost of SAS, and there’s no reason to since it’s not significantly better for our purposes than Easytrieve.
VBA sounds like a good alternative. I’ll have to look into that (as well as Python and Perl) and see what resources are available to learn it (them).
I’ve just received word that the Easytrieve renewal is for the Maintenance agreement. We haven’t used it since my computer crashed a year or so ago and we had to reinstall. I’ll have to contact Computer Associates to find out if there actually is a license fee for Easytrieve.
But it would be better if we could use something that doesn’t require user fees, and that could be put on several computers so that other people could be trained to use them. My coworker constantly complains about the workload. (So why not buckle down and do it instead of spending half an hour complaining about it?) We’re not going to hire anyone else, so if we had something we could all use that will save work (and ensure better accuracy). What I’d like to do is write the routines and then tell my boss and coworker how to use them. (They sort of glaze over when I try to show them how the programs work.) We could do it with Easytrieve, if we were allowed to have it on more than one computer.
Well, you could have one install of SQL (free lite version) or MySQL (free) and build a custom browser-based app to run on your intranet to do this work. No seats or licenses needed to use the app once it’s built.
You’d probably want to hire a company to build the app to your liking instead of doing it yourself (you would be the one working directly with the company doing the building, so it’s right). You could learn SQL and the programming language used to make the app if you want to make tweaks after-the-fact.
Just depends on the cost of the building of the custom app vs. the cost of something off-the-shelf (with the seats you desire) vs. the time cost of you building it yourself.
In fact, this is exactly the sort of thing that Perl was designed to do, and do exceptionally well. You can also do this in Python (or Ruby, or any number of other interpreted “scripting” languages) but while I have a general preference for Python I think it would be easier to make a compact program in Perl to do this, and probably easier than writing SAS scripts (and vastly easier than trying to program in C or C++).
Perl (and Python) are freely available for download from CPAN or ActiveState, and there are plenty of free online resources as well as the excellent series of O’Reilly manuals for both languages.
I’ll look at those and see what I can see. This is supposed to be the ‘dead’ week of the month, but I’m still working. I’m getting close to capacity. Next week comes the avalanche. But I’ll check out suggestions in this thread as I can find the time.
I do stuff like what the OP wants to do all the time in Perl, sometimes processing thousands of files or files of millions of lines in a few minutes at most. I’m sure Python is just as good, but I’ve never seen the need for it.
I think the OP will be amazed at how easy his job is going to be using a scripting language, and about how much more flexibility he will have to eliminate makework steps and to format things or produce additional output that could be useful.
I think the OP should go to a bookstore and browse Python and Perl books, and choose the language he likes best.
Python is better because you save so much labor not having to type { } $ or ;
Perl is better because regular expressions are built in and as such are less cumbersome than in python.
But you must learn how to use regular expressions. Their main use is pattern matching similar to what you describe.
Perl is going to be more comfortable for someone who has done Unix shell scripting (as it is basically a superset of awk and sed commands with more extensive control structures). Python is clearer to those who have done more traditional program (though for some reason people get upset about the lack of curlique brackets). Python is definitely more functional as a prototyping language, especially for object-oriented development; for data parsing and manipulation either will do the job. Perl’s regexp capability is somewhat more capable than the Python re module, but unless you really get thick into the morass of complex regexps either should be usable.
I’m comfortable with traditional, mainframe-based sequential programs. I can’t believe I’ve been using Easytrieve for 13 or 14 years! :eek: ‘Get a record, do this, do that, get the next record…’ I’ve used SQL commands on occasion, but since those occasions were rare it was always a case of having instructions that said ‘Type this in exactly.’
I’ve received an email from CA:
But they don’t believe in lapsed maintenance. You don’t have to buy their maintenance agreement, but if anything goes wrong the contract is reinstated backdated to the lapse date.
I’d still like to have another alternative so I can spread the work around a little. My coworker thinks I’m silly to take my laptop on vacation with me. My boss thinks I should accept overtime, but we’ve agreed that comp time is enough. Still, there are times when I’d like to take some vacation time at the beginning of a month.