[PLUG] Mail merge: finally working!

Rich Shepard rshepard at appl-ecosys.com
Thu Sep 26 02:03:44 UTC 2002


On Wed, 25 Sep 2002, Colin Kuskie wrote:

> So based on your summary below, the problem is that OO will not let you
> do mail merges if the zip code is considered a number by OO Calc?

Colin,

  The problem originated with a data file external to OO; from my jpilot
addressbook. Some of the zip codes were 5-digits, some were 9-digits. OO's
Calc saw the 5-digit codes as numbers and tried subtracting the final 4
digits of the 9-digit codes, so the resulting merge had no zip code field.

  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.

  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.

  During all this, I followed someone's suggestion to use the .dbf files
exported from Calc. That did't work. When I exposed the fields in the
database so I could place them on the form letter, I was shown fields
numbered N1 through N16; my data have 7 fields. I also tried to use a plain
.txt file in various permutations.

  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.

  This is why I got so frustrated in trying to accomplish this task. The
software appeared to be fighting me every step of the way. I reformatted my
data multiple times, recreated the fields on the form letter multiple times
and it was all trial-and-error with no guidelines. The one HOWTO did not
help for it assumed all data were consistent. Well, I _thought_ mine were
consistent as text, even the zip code field was surrounded by double quote
marks, but Calc just ignored that and made them all numbers.
 
> Did you try to fool the autorecognition software by appending
> spaces to the zip codes inside the quotes while it was a CSV file?
> 
> "97123   "  may be recognized as a string, and won't hurt your mail
> merge.

  No. In every other spreadsheet I've used putting a string in double quotes
is sufficient for it to be recognized as text.
 
> Doing this may also let you suck it directly into OO Calc, without the
> intervening step of XessSE.

  I tried just about every possible permutation of four different data
formats: txt, csv, sxc and dbf. It's not been pretty.

> If you want to open the .xls without the blank writer document, you
> can invoke soffice on the command line:

  What I didn't try, because it finally worked, was opening the .xls file in
Calc, then closing the document window before trying to save the spreadsheet
in the native, OO format. That, too, should not have been as difficult as it
was.

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

Thanks,

Rich





More information about the PLUG mailing list