[PLUG] Mail merge: finally working!

Colin Kuskie ckuskie at dalsemi.com
Thu Sep 26 16:36:12 UTC 2002


On Wed, Sep 25, 2002 at 07:03:44PM -0700, Rich Shepard wrote:
> On Wed, 25 Sep 2002, Colin Kuskie wrote:
> 
>   Someone on the mail list suggested changing the column to "text" from
> "number". Turns out that this cannot be done from the Format menu and
> there's nothing in the on-line help that explains what to do. I learned,
> finally, that one highlights the entire column, then clicks the right
> mouse/trackball button to reveal the "format cells" option. Unfortunately,
> this does not work. I ended up with the 9-digit codes as text and the
> 5-digit codes as numbers. Again, no 9-digit zip codes in the merge.

I'm using 1.0.1, and in the Format menu it's Cells.  In the right mouse
context menu, it's Format Cells.  Same thing.

>   Then I went through all 90 rows of the spreadsheet and changed each one to
> text, manually. As soon as I saved the spreadsheet, the 5-digit codes
> reverted to numbers. So, I gave up and modifed all 9-digit codes to 5-digit
> ones. Still empty zip code field in the merge on those records that had
> originally been 9-digit codes.

You're right.  It doesn't work and it's documented as not supposed to
be working in that way. (although I don't understand why it doesn't).
In general, if OO does something automatically, and then you tell it
not to do that automatic thing anymore, it won't undo what it did.  So
once you enter a number as a number, you're stuck with it.

In the OO help window, go to the index tab and enter:
numbers; as text
and look at the text that's displayed. It explains all that.

>   I've no idea what Calc is doing to the data. If I opened a new spreadsheet
> and made the appropriate column formatted at "text" before importing my .csv
> data, that column lost its formatting and I had mixed results again.

It's a bug/feature in the importing code.  However, playing around with
it I found out how you can override the bug.

1) Go back to your very original CSV file with the combination of 5 and
   9-digit ZIPs.

2) Open the CSV in any of the classic ways (on the command line,
or from a currently open document).

3) You get presented the Text Import dialog box.  At the bottom is the
Fields configuration.  It will allow you to preview your spreadsheet
based on delimiters, fixed width and so on.  More importantly, it will
let you select each column and override it's assumptions for the
format!

4) Select the column with the zip codes

5) Use the pull-down menu above (it defaults to "Standard", which is
probably where the bug lines) to change the format of the column to
Text.

>   Where's that six-pack of microbrew ...?

It's 5 o'clock in Germany, which is appropriate :)

I actually tried the solution above in 1.0.1 on a very simple CSV
file:

1,1,"1"
2,2,"2"
3,3,"3"
4,4,"4"
5,5,"5"
6,6,"6"
7,7,"7"
8,8,
9,9,
0,0,

and it worked okay.  I hope it works for you, too.

Colin




More information about the PLUG mailing list