Data Manipulation Off the HP 3000

October 2004

Data Manipulation Off the HP 3000

Excel’s Data Menu – Sort and Filter

Now I have the data, I can do pretty much whatever I want with it, within Excel’s capabilities. These are many and varied, but I’ll just mention a couple of other options on the Data menu that I’ve found very useful.

Firstly, there’s Excel’s own Sort. It will anyway sort more data, and faster, than WinWord, but can now be applied across the fields. For which, by the way, adding a top line to give each column a name will mean that the sort will offer you these names to pick from.

However, there seem to be only three keys on offer. What if you want to sort on more than that – five say? Well, use the old card-sorter’s trick – sort on the two most junior fields first. Then sort that result on the three major sort keys. Excel preserves the pre-existing sequence under these keys, so voila – sorted on all five.

The other useful, but often overlooked, feature on the Data menu is Filter. Choose this, for one or more columns, and you get a drop-down showing all the possible values. Choose one of these, and Excel filters the values to show you only the rows that match. There’s a Custom option, too, where you can set up a filtering rule if a simple value selection won’t do what you need. I don’t know how I ever used to get along without Filter.

Exporting from Excel

Besides saving as an .xls, you could save the file as a .csv, for those processes that wanted that format. If you save the file with Save As .txt, you can also get a tab delimited file. This too has its uses, as we will see.

But what if you want to put something more like a traditional fixed-fields MPE file back together? Well, let’s say we have five fields:

A

B

C

D

E

Widgets WX1234 103 Left-handed A424

and our file needs to be X20/X6/N6/X30/X4. But ‘Widgets’ is only six characters, and other names are perhaps other lengths still. But the formula:

=A1&REPT(“ “,20-LEN(A1))

will restore it to 20 characters long. Likewise:

=TEXT(C1,”000000”)

will not only put the leading zeroes onto 103, but will also render it as text, so that:

=A1&REPT(“ “,20-LEN(A1))&B1&TEXT(C1,”000000”)&

D1&REPT(“ “,30-LEN(D1))&E1

will give the needed string. (Assuming B1 and E1 are fixed; but if not, REPT is their friend too).

Putting the resulting column on a second worksheet in the workbook will allow just that sheet to be saved as a .txt file.

Formatting in WinWord, Part 2

Reading the tab-delimited .txt file from Excel back into WinWord, it now looks much as before, except there are now tabs between the fields instead of spaces. These tabs mean, as it happens, that choosing Table/Sort will now give you the option of sorting by fields, not just by paragraphs. But nothing you could not already have done more easily in Excel. The only reason to read the data back into WinWord for further formatting is that you now want to remove yet more ^p paragraph marks.

Why might you want to do that? Well, one reason I’ve done it is to load data into an application via its VPlus screen, without having to retype it all, where the application had no batch interface per se.

Let’s envisage an app which takes entries in blocks of ten per screen, and where some fields are filled right up, while others have extra spaces in them. The ^t after the full fields must be removed, while those after the fields with spaces will ensure the cursor moves as it should. And likewise, the ^p at the end of the lines must go, or the data lines will get extra blank lines between them – not what’s needed.

Here’s what three lines might look like, as per the fixed-file example above, viewed in WinWord with ‘Show ¶’ turned on:

HHH

Widgets ? WX1234 ? 103 ? Left-handed ? A424¶

Widgets ? WX1235 ? 29 ? Right-handed ? A428¶

Widgets ? WX1237 ? 1 ? T-Bend ? X525¶

To get this into VPlus, we need to remove the tab marks after the WXnnnn fields, and the paragraph marks also. The tab marks can be removed by holding down Alt and dragging down that column, and the paragraph marks in Replace.

Then the data looks like this:

Widgets ? WX1234103 ? Left-handed ? A424Widgets ? WX123529 ? Right-handed ? A428Widgets ? WX12371 ? T-Bend ? X525¶

It might look weird, but Copied and Pasted into a VPlus screen with lines of fields X20/X6/N6/X30/X4, it should fit and format perfectly. And of course, the application validation is still being applied – the technique is purely a typing time- and error- saver.

Volumes

I’ve gone up to 12-13 MB Excel files, with thousands of records, before hitting performance limits. And that was on a fairly modest PC, so today’s behemoths may go even further.

Clearly, with WinWord and Excel working in memory, these techniques are not going to work for migrating the contents of your Jumbo datasets. But those will probably merit dedicated programs anyway; the payoff of what I describe comes for those myriad small files that still need moving, and yet would take as much, if not more, programming effort – each – if handled like the big files.

Well, that’s about the limit of what I can offer here – I hope it’s given you a few ideas, at least, of what’s possible in this area. Now I have to go and actually do some of this stuff…

Roy Brown is CEO of development and consulting firm Kelmscott Ltd. (roy@kelmscott.co.uk) with more than 25 years’ experience on MPE and the HP 3000, and is currently working on HP 3000 migrations and MPE.