Here’s the process: Easytrieve needs fixed-position text files. We receive most of our data as Excel spreadsheets (and .csv files, tab-delimited text, and even fixed-position text in the correct format). The Excel spreadsheet is opened, and I do some editing to make sure everything will fit in the eventual output file. I save the spreadsheet as .csv. I import the .csv file into Access and then export it as a fixed-position text file. This file is run through Easytrieve. The output files (Matched, Nonmatched in Previous, Nonmatched in Current) are combined and imported into Excel. The Nonmatched in Previous records are cleaned and the file is sorted. Then the file is saved as .csv, imported into Access, exported as fixed-position text, and run through the reformat Easytrieve. The resulting files are sent to their destinations.
A decade ago I wanted to see if I could do the processing with Excel so that my coworkers could process it. (I’m the only one who knows how to write Easytrieves, and my boss and coworker didn’t want to learn how to run it – and we only have a license for it to be on one computer anyway.) I thought I could use vlookup to to the matching to the previous file, but it didn’t work. I let it run overnight once, and it still hadn’t finished in the morning. Someone who knew how to do it (outside of the company, maybe from here) showed me exactly how to write the vlookup, so I know it was correct. And then there’s the hassle of reformatting. One format has over 100 fields, and I’m not confident Excel can handle that.
If you’re looking for a programming “project”, it seems like a tool that converts CSV to fixed-position text would come in very handy for you – Access is very heavyweight to use for that task, and adds an additional dependency to your project. Both the input and output formats are basically text files, which are easy to interact with through perl or python or whatever.
Yes, writing a Perl script to convert .csv to fixed position text is very easy and would be a good - and fast - exercise in Perl. And it also simplifies your process.
Then you could see how you like Perl.
Back in the day (Access 2.x) , that import and export was actually done by a page of VBA (Access Basic) code in one of the system modules. Which you could of easily copied and pasted into Excel. As part of the many improvements to Access that you have noted above, that process is now hidden, mysterious and fragile.
And annoying. I have one file that has address fields that are… problematic. Rather than make sure at the beginning everything will fit in the output format, I’ve defined the field width in Easytrieve as 100. But then they have something like this: ‘E-mail Invoices to: wanker@wankoriffic[dot]com Fax Statements to: (800) 555-1212 Attn: Accounts Payable Dept PO Box 12345’. If Access truncates it, it’s a minor hassle (if it’s not a preciously provided account) to open the raw file and look up the address. With 2010, it doesn’t truncate it and any record that has a length of more than 100 is shifted to the right. That’s a bigger hassle. It would be much better if Access believed that I know what I’m doing!
In any case… First half of the month, and I have a stack of files. Easytrieve is working since they reinstalled the license key (fingers crossed it continues to do so), so I’m going to be very busy. And I’m on vacation for two weeks starting on the 22nd, so I need to work some extra hours to get everything done before then. But I did order Perl For Dummies.
Glad that it’s working for you now. I’ll go ahead and state the obvious to get it on record though. Copy the installer files and ca.ofl to a network drive. Not so obvious but equally important. Download a copy of the html from the installation link and save it as well. It might not be there the next time around. Also, have the IT person who installed it create a document detailing any issues they had to solve while doing the installation while it’s still fresh on their mind.
And good luck and have fun if you decide to use this as a reason to learn one of the languages recommended above.
I’m sure they have the installer and ca.olf files on their server. Good idea about saving the instructions page. I was going to save a copy myself, but… How do you save a page using Edge?
The easiest way would be to use the mouse to select the instructions on the page and then right-click/copy the text. Then open a blank document in Word and paste the copied text. It should retain the formatting and all you have to do is save your new file.
Lots of people have given you reasons to use either Perl or Python (and now I see AWK, which is what I personally use for quick string work (but I really should bone up on Python)).
At any rate - I quoted the above because breaking your process up into discrete steps is almost always a good idea, for the reasons you stated and maintainability.
Write each piece as a standalone Perl script/Python program, and if you want you can write another script/program to call your pieces in the order you need to run them for your normal case, and still have access to the parts for reprocessing.
If you want, PM me your (or an) address and I’ll mail you my copy. It’s from 2003; I don’t think the language changed that much since then–it’s all still Perl 5. This book is geared to Perl 5.8. (I quickly looked at the version history, and it doesn’t seem like there’s anything that would make the book obsolete or anything.)
Get Learning Perl instead. Or just use the multitude of online tutorials and resources which should be sufficient to get you started and see how you like it.
