# Programming alternatives, Part 2 **URL:** \ **Category:** Factual Questions\ **Created:** [September 28, 2018, 11:45pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147 "2018-09-28T23:45:16Z")\ **Posts on this page:** 14\ **Page:** 6
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 3, 2018, 1:53pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/101 "2018-10-03T13:53:30Z")
> [@Sandwich](#): > > Wait, let’s back up for a moment here. Apologies if this is a really stupid comment, but some of the previous discussion was confusing about the scope of the problem. If we have simple string data in an Excel spreadsheet, that needs some very simple manipulation and reformatting, then we should first check that the whole thing can’t be done easily in Excel before we consider more involved solutions. You could at least produce the CSV files directly, no? 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. ---
**Author:** ![leahcim](https://avatars.discourse-cdn.com/v4/letter/l/b4bc9f/32.png) [@leahcim](https://boards.straightdope.com/u/leahcim)\ **Post date:** [October 3, 2018, 3:39pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/102 "2018-10-03T15:39:41Z")
> [@Johnny\_L.A](#): > > I import the .csv file into Access and then export it as a fixed-position text file. > > … Then the file is saved as .csv, imported into Access, exported as fixed-position text,… 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. ---
**Author:** ![Voyager](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/voyager/32/133_2.png) [@Voyager](https://boards.straightdope.com/u/Voyager)\ **Post date:** [October 4, 2018, 2:19am UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/103 "2018-10-04T02:19:41Z")
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. ---
**Author:** ![Melbourne](https://avatars.discourse-cdn.com/v4/letter/m/b5e925/32.png) [@Melbourne](https://boards.straightdope.com/u/Melbourne)\ **Post date:** [October 4, 2018, 9:16am UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/104 "2018-10-04T09:16:50Z")
> [@Johnny\_L.A](#): > > …Then the file is saved as .csv, imported into Access, exported as fixed-position text, and run through the reformat Easytrieve… 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. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 4, 2018, 3:41pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/105 "2018-10-04T15:41:20Z")
> [@Melbourne](#): > > 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_. ---
**Author:** ![Projammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/projammer/32/559_2.png) [@Projammer](https://boards.straightdope.com/u/Projammer)\ **Post date:** [October 4, 2018, 5:24pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/106 "2018-10-04T17:24:09Z")
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. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 4, 2018, 5:57pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/107 "2018-10-04T17:57:46Z")
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? 😕 ---
**Author:** ![Projammer](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/projammer/32/559_2.png) [@Projammer](https://boards.straightdope.com/u/Projammer)\ **Post date:** [October 4, 2018, 11:15pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/108 "2018-10-04T23:15:59Z")
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. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 5, 2018, 3:47am UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/109 "2018-10-05T03:47:41Z")
> [@Projammer](#): > > 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. :smack: Which is what I did! I was going to say that I copied it into a Word document (crtl+a, ctrl-c, ctrl+v), but the job intervened. On Safari I can save as web archive. That doesn’t seem to be available on Edge or Chrome. ---
**Author:** ![Folacin](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/folacin/32/3195_2.png) [@Folacin](https://boards.straightdope.com/u/Folacin)\ **Post date:** [October 5, 2018, 3:55am UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/110 "2018-10-05T03:55:06Z")
> [@Johnny\_L.A](#): > > ETA: About having separate sort, matching, and reformatting programs. I know I could put them all into a single program, but I like breaking them up so that I can have more control. In the event that I need to reprocess, I can skip steps. 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. ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 8, 2018, 1:22pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/111 "2018-10-08T13:22:18Z")
Amazon cancelled my order for _Perl Fod Dummies_ due to lack of availability. I’ll have to get it later. ---
**Author:** ![pulykamell](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/pulykamell/32/3166_2.png) [@pulykamell](https://boards.straightdope.com/u/pulykamell)\ **Post date:** [October 8, 2018, 1:56pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/112 "2018-10-08T13:56:49Z")
> [@Johnny\_L.A](#): > > Amazon cancelled my order for _Perl Fod Dummies_ due to lack of availability. I’ll have to get it later. 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](https://en.wikipedia.org/wiki/Perl_5_version_history), and it doesn’t seem like there’s anything that would make the book obsolete or anything.) ---
**Author:** ![Stranger\_On\_A\_Train](https://avatars.discourse-cdn.com/v4/letter/s/13edae/32.png) [@Stranger\_On\_A\_Train](https://boards.straightdope.com/u/Stranger_On_A_Train)\ **Post date:** [October 8, 2018, 2:01pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/113 "2018-10-08T14:01:30Z")
> [@Johnny\_L.A](#): > > Amazon cancelled my order for _Perl Fod Dummies_ due to lack of availability. I’ll have to get it later. Get [_Learning Perl_](https://www.amazon.com/Learning-Perl-Making-Things-Possible/dp/1491954329/) 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. Stranger ---
**Author:** ![Johnny\_L.A](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/johnny_l.a/32/1084_2.png) [@Johnny\_L.A](https://boards.straightdope.com/u/Johnny_L.A)\ **Post date:** [October 8, 2018, 2:06pm UTC](https://boards.straightdope.com/t/programming-alternatives-part-2/822147/114 "2018-10-08T14:06:41Z")
> [@pulykamell](#): > > If you want, PM me your (or an) address and I’ll mail you my copy > [@Stranger\_On\_A\_Train](#): > > Get [_Learning Perl_](https://www.amazon.com/Learning-Perl-Making-Things-Possible/dp/1491954329/) instead. **pulykamell** : Thank you for the offer. I’m not going to have time to start this month though **Stranger** : That’s the other book I looked at. 😉 Thanks, guys! [Previous page](https://boards.straightdope.com/t/programming-alternatives-part-2/822147.md?page=5)