Lotus 123 rel 5 save as DBF hack ...

[sabre 2/3 usenaut]

Hi All,

Just got myself a Psion Siena (only) 512KB recently, and also bought
the PsiWin link. New toy, so spent most of last night transferring my
contacts address data from my old Casio SF4600 to my Win95 via Casio
Datalink. I then massaged the address database in Excel 5.0. [1]
Didn't have enough hours in the night so decided to bring the
spreadsheet to office to transfer it to my Siena.

ObHack:
Unfortunately, office only has Lotus 123 rel 5 for Windows. Thought, no
problem, 123 should be able to export the spreadsheet into DBF database
format as required by PsiWin, just as well as Excel 5 could do it.

How wrong I was. :-( Spent most of today's morning hacking around 123's
inability to do so. [2]

Using 123's File Save As DBF command with the whole sheet selected just
gave an error dialog box saying something like "Q+E, DBASEIV, Error,
An insert value not the same as column header". A hunt through the
error messages and help file didn't yield anything more helpful.

First, thought maybe not all the data are Label or Text. Changed all
cells to Label, also do copy Formula as Values, but neither helped. Then
did try File Save As DBF with a small selection of the spreadsheet, it
works. Great, progress, there must be something wrong with some cells
in the spreadsheet.

Now just a matter of finding out what is wrong with those cells.
Progressively trying Save As bigger selection of cells, narrowed down
the problem to a particular column; include it gets error, without it
okay. After many false trails, it was a matter of luck that I copied
another cell into one of the many blank cells in the problem column,
and found that that Save As DBF correctly.

So, the hack was just to put a few " " into the first cell (second
counting the column field name) in the problem column. ;-}

ObPostscript:
After all that, had to go for a lunch date, and had an unscheduled
meeting with bosses in the afternoon, and didn't have time to finish
downloading the address data into my Siena. Now after work, at home,
I no longer need the above hack, since I have Excel 5.0 ...

[... since, if you're still reading this you must be interested,
I have ...]

ObQuestion:
On hind sight, it's clear that 123 rel 5 is deciding whether a column
(field) is text or formula by just checking the first few cells of a
column (instead of checking the whole column). And if the cells are
blank (neither text nor formula), it is treated as formula.

So, does anyone knows which part of 123's docs that I've missed that
explains this? ;-}

regards,
.sabre

== footnote ==
[1] I did say I used to hack everything in 123 rel 2.0A right? In
fact I still use a lot of "/" commands (that my fingers had memorized)
in both 123 rel 5 and Excel 5. ;-}
[2] Almost gave up and searched for some fellow workers who have Excel 5.

.sabre
<tlx> Every silver lining has a cloud around it. </tlx>