Mail Merge: edit a field before applying it

[calabar]

I've to create a "Mail Merge" document.
Unfortunately the data source is not exactly what I need, so I've to work on single fields before add the result to the letter.

Example 1:
I've a single field containing firstname and surname. In the document, I want to write first the surname and then the firstname, so I've to create a routine that reverses the order.

Example 2:
One of the fields is a number, a value in Euro.
For my document, I've to split int value and decimals, and apply these two elements to two different fields.

Example 3:
One of the fields contain a long sentence.
I simply have to look how it starts (using a word or another) and then write a specific text on the related field.

I've no idea how to intercept the data, so that I can apply a routine/macro to modify it.
Thanks!

[acknak]

No need for macros at all: you can do all this (I believe) with a database query. The query can massage the data in the fields and present the modified data as a new data table; the fields from the query result table are then inserted into the merge document.

I don't know SQL well enough to give any specific hints, but there are lots of examples here on the forum, or you can just ask on the Base area of the forum and someone will give you some suggestions.

[calabar]

Thanks for the answer.

I didn't imagined I can use merge documents with a database query, it could be an interesting way to follow.
As I've not idea how to do that too, I hope to be able to find examples that will clarify my mind.

The important thing is that I can do this from the .odt file and not on the database, because the datasource changes every time while the odt file remains ever the same.

Anyway examples I made are not exhaustive, so I think I will surely need to apply some Macro (for example I've to convert digits in words).

[Villeroy]

No, you do not need any macro at all. It is possible to use all kinds of data sources, tables, queries and field names without changing your letter template. However without a trace of technical info it is impossible to give any advice. At least we should know where all the data come from (which database server, user privileges, SDBC, JDBC or ODBC driver etc.).

[calabar]

Thanks for the answer.

Data come from an xls file. I've seen that writer convert it in an .odb file.

The problem is not to get the data from the source, but to transform the retrieved data before inserting that in the document.

[Villeroy]

No, Writer does not convert anything. The data come from your spreadsheet file which is connected to a so called "database document" which itself does not contain any data at all. It presents your spreadsheet data as if they were a database.
So all you need is a spreadsheet formula to split the names into fornames and surnames, another formula to split the numbers (INT and MOD do this) and a formula that looks up the right thing from the third word of a field.
Then you need a database query which picks the wanted columns under the correct alias names.
It may take many hours to get this done by means of macros but it is a matter of one minute to modify your spreadsheet accordingly (or any other sheet for that purpose).

[calabar]

Thanks.
The problem is that I'm trying to avoid to modify spreadsheet, because it is autogenerated from a software on which I've not any control and it changes every time. I should modify it every time a new spreadsheet is provided (quite often).
My aim is to delegate this work to the .odt file, so that, once prepared this file, it can use the generated xls files without other intervention.

[Villeroy]

When a software dumps data into xls the one and only reason is that this file format allows you to do anything you want with the exported data (assuming that "everybody" has a copy of Excel). Otherwise it would print PDF reports or something. The software will never read any xls data back into its database so you are free to do what you want.
All this is too obscure. We know nothing about the exported data nor the software producing the xls dump. Is it some kind of database?
Macros are an unproductive waste of time.

[calabar]

The software producing the xls file is some kind of management software, I don't know the name (I don't work on it, I simply receive the xls file), probably some ad hoc software.
The software contain transactions data (with client's data), one operation for each row. I've to get these informations and print for each a sort of "sheet" that include some of these data, but I need to transform them before (data "as are" are not useful for me in most cases).
Do you need other informations?

I can agree about macros, but:
- I already have a working Macro that transform numbers in letters, so I thought to use it rather then write another kind of routine that do that.
- The odt file have to take the xls file as is and then make necessary transformations. I've to prepare something that other people, that have no ability to modify the xls file, can use. Ask me every time is not an option.
- The solution don't need to be efficient. I don't have to deal with hundred of thousand records, but with tens to hundreds records at a time.

If I can do all that easily without macros, for me is a good solution.
Otherwise, I need macro.

Thanks.

[calabar]

For any suggestion, I'm still here!

[RoryOF]

How big (typically) are the speadsheets you receive? Can you post a sample one (sensitive data removed or changed,, of course)? Are they always in a constant format - i.e., the data to be analysed/changed in the same positions?

[Villeroy]

Well, intstead of exporting xls files from that mysterious business software you could query anything you want from its internal database and then dump it into Writer or Calc in various ways. Instead you try to build a database report (serial letter) from something which had been exported as a database report (xls file) already.

I've to create a "Mail Merge" document.
Unfortunately the data source is not exactly what I need, so I've to work on single fields before add the result to the letter.

Example 1:
I've a single field containing firstname and surname. In the document, I want to write first the surname and then the firstname, so I've to create a routine that reverses the order.

Firstnames and surnames are stored separately in the original database. They had been merged for your xls report. Splitting them again into separate fields is tricky because a surname and a firstname may consist of more than two words. Which words belong to the first and which words belong to the surname? Using the original database you have them separated. When working with the xls file I would use the full name as is.

Example 2:
One of the fields is a number, a value in Euro.
For my document, I've to split int value and decimals, and apply these two elements to two different fields.

You could query the 2 fields from the original database. In the xls is it is:
=INT($X2)
and
=MOD($X2 ; 1)

Example 3:
One of the fields contain a long sentence.
I simply have to look how it starts (using a word or another) and then write a specific text on the related field.

I've no idea how to intercept the data, so that I can apply a routine/macro to modify it.
Thanks!

This sounds too much like fuzzy logic. But querying some record set depending on the occurrance of keywords in another record set is a typical database task. In a spreadsheet, mapping multiple keywords to multiple records it is very tricky. This is exactly what spreadsheets are NOT made for while most of today's spreadsheet users try to these things so desparately learning everything about extinct programming languages (Basic), array formulas and crude lookup functions with and without regurlar expressions.

P.S. The mystic "business software" should be able to generate serial letters one way or the other.

[calabar]

How big (typically) are the speadsheets you receive? Can you post a sample one (sensitive data removed or changed,, of course)? Are they always in a constant format - i.e., the data to be analysed/changed in the same positions?

Typically among 100 and 150 lines (no many variations). The format is constant.

The document is in Italian language, so I didn't post it before.
I attached a sample, it should be similar enough to the original document.

Well, intstead of exporting xls files from that mysterious business software you could query anything you want from its internal database and then dump it into Writer or Calc in various ways. [...]

My English is not so good, so maybe I failed to communicate that I've not any access to the database that generate the lxf file. I simply receive the file.
The xls file seems to be meant to be printed and used for manual filling. What I want to do is to avoid manual filling and automatize a bit the procedure.

[Villeroy]

SampleDocument.ods

The name splitting fails when the name consists of more than 2 words.

[calabar]

I replaced the attached document, it seems I didn't clear adequately the data.
Please delete your file too.

@Villeroy
Examples I did before was only... examples!
Don't worry about the implementation, I'd simply like to understand how to intercept data, later I will think how to manage that.

PS: I've not understood what I've to do with the ods file you attached above.

[Villeroy]

You want a serial letter from spreadsheet.
----------THIS NEEDS TO BE CONFIGURED ONLY ONCE---------
File>New>Database...
[X] Connect to existing databsae
Type: Spreadsheet
Point to your spreadsheet
[X] register database
Save the database file.
If you like, you can create a query that includes only the fields you are interested in.
In Writer hit F4, double-click your registered database, then queries and select your query with relevant data.
Drag the column headers into your serial letter and save it as template for the next serial letters to come.
-----------------END OF CONFIGURATION----------------------------------------------------------------------------------------------

Next time when a new xls arrives you copy the calculated fiedls (spreadsheet formulas) from the old sheet into the new one and overwrite the old file with the new file.

[calabar]

Ok, so you are saying to:
- Take the original xls spreadsheet
- Add new columns to the spreadsheet
- Use formulas to fill the new columns with modified data.
- Use the spreadsheet as datasource for the odf file that will perform the mail merge activity.

Next time when a new xls arrives you copy the calculated fiedls (spreadsheet formulas) from the old sheet into the new one and overwrite the old file with the new file.

That's a little complication I would like to avoid (because introduces some complications that I'm not sure peolple that will use the document will be able to manage), but it is not so terrible as not to give it a try.
Anyway, instead than copy formulas from the previous xls and then replace the file, maybe simply replacing the data fieds in the old file with the new data could be an easier solution. Agree?

PS: don't forget to remove the attachment above, please.

[Villeroy]

Ok, so you are saying to:
- Take the original xls spreadsheet
- Add new columns to the spreadsheet
- Use formulas to fill the new columns with modified data.
- Use the spreadsheet as datasource for the odf file that will perform the mail merge activity.

Next time when a new xls arrives you copy the calculated fiedls (spreadsheet formulas) from the old sheet into the new one and overwrite the old file with the new file.

That's a little complication I would like to avoid (because introduces some complications that I'm not sure peolple that will use the document will be able to manage), but it is not so terrible as not to give it a try.
Anyway, instead than copy formulas from the previous xls and then replace the file, maybe simply replacing the data fieds in the old file with the new data could be an easier solution. Agree?.

I agree.

You could also link a Base document to the xls file, link an import range of an .ods file to the Base document, link another Base document to the .ods file and the serial letter to the second Base document.
With this setup you replace the xls file with a new one, open the ods file, refresh the import range (which automatically adjusts the formula range), save the .ods and run the mail merge.
I have something similar with an import range and calculated fields on an ods sheet. There are also 2 buttons. One button is bound to the following macro, the other button opens a Writer document with maíl merge fields.

Code: Select all

Sub refreshAll()
on error goto exitErr:
for each dbr in thiscomponent.databaseranges
	dbr.refresh()
next
ThisComponent.calculate()
ThisComponent.store()
exitErr:
End Sub

PS: don't forget to remove the attachment above, please.

Why? Mario Rossi with telephone 333/123456 is a real person?

[calabar]

You could also [...]

Ugh, this sounds a bit complicated, I've to read it more carefully!
I'll try later.

Why? Mario Rossi with telephone 333/123456 is a real person?

Eheh no, Mario Rossi is not a real person, but elsewhere in the document there are some real data.

[Villeroy]

It's always the same:

---------THIS NEEDS TO BE CONFIGURED ONLY ONCE---------
File>New>Database...
[X] Connect to existing databsae
Type: Spreadsheet
Point to your spreadsheet (the .xls)
[X] register database
Save the database file.
If you like, you can create a query that includes only the fields you are interested in.
In your ods window hit F4.
Drag the icon of your table or query from the left pane into your document.
You get a linked import range (see Data>Define... Import1)
Attach the formulas you need next to the import range.
-----------------END OF CONFIGURATION----------------------------------------------------------------------------------------------

---------THIS NEEDS TO BE CONFIGURED ONLY ONCE---------
File>New>Database...
[X] Connect to existing databsae
Type: Spreadsheet
Point to your spreadsheet (the .ods)
[X] register database under some other name than the first
Save the database file.
If you like, you can create a query that includes only the fields you are interested in. Include the calculated fields.
In Writer hit F4, double-click your registered database, then queries and select your query with relevant data.
Drag the column headers into your serial letter and save it as template for the next serial letters to come.
-----------------END OF CONFIGURATION----------------------------------------------------------------------------------------------

1) Replace the old xls with a new xls.
2) Open the ods file, click any single cell in the import range.
3) Call Data>Refresh.
4) Save the .ods.
Print your serial letter. It should contain the data of the new .xls file plus the calculated fields from the .ods file.
IF NOT: hit F4, right-click the .ods data source and choose "Disconnect". Then try again.

[calabar]

Thanks, I'll make a try as soon as possible (not immediately because of few time).

I've seen you didn't removed the uploaded file above yet, so I renew the request.

[calabar]

I'm still busy, so I'll delay tests again.

In the meantime, I'd like to don't completely leave the initial request of the topic, even if for my problem I will follow another way.
How to manipulate data in the .odt file after I import it for mail merging? Is it possible in Writer?

[Villeroy]

I'm still busy, so I'll delay tests again.

In the meantime, I'd like to don't completely leave the initial request of the topic, even if for my problem I will follow another way.
How to manipulate data in the .odt file after I import it for mail merging? Is it possible in Writer?

No, it is not possible as long as you store your data in spreadsheets. You've got to open the spreadsheet, edit it, save to disk, shut down any database connection to that spreadsheet and then run the mail merge with updated data.

[calabar]

No, it is not possible as long as you store your data in spreadsheets. You've got to open the spreadsheet, edit it, save to disk, shut down any database connection to that spreadsheet and then run the mail merge with updated data.

That's a pity. It is very limitative that it is not possible to intercept data before inserting that.
I've seen that it is possible to apply a format filter to the data, but in this case I need something more complex.

I imagined something like:
- Writer insert a placeholder for the data, for example <myDB.table.Field>
- I could intercept it and write something like <myFunction(myDB.table.Field)>, where the return value of my function was the modified data I need.

[Villeroy]

You can do so in the database which happens to be a spreadsheet in this particular case. People love spreadsheets because they make it so easy to use functions?

Oh, you may find some useful Writer field. Conditional fields are being used quite often in the context of serial letters. I prefer to create calculated values in the database.

[calabar]

You can do so in the database which happens to be a spreadsheet in this particular case. People love spreadsheets because they make it so easy to use functions?

In this specific case I will probably choose the suggestions you gave me some posts above. But I wanted to find an answer to my original question anyway, because in other cases it could be useful (or simply, because I'd like to learn how to do that!).
Besides, datasheet functions not ever are enough to manipulate the data.

In any case, the answer is ever the same: the advantage would be to avoid to modify the datasource, because the datasource changes every time, and this mean that an editing work is necessary every time. A modification of the datasource is a good solution only when the "couple" document-datasource is stable.

Oh, you may find some useful Writer field. Conditional fields are being used quite often in the context of serial letters. I prefer to create calculated values in the database.

I made some try with variables, but I can't obtain something working with mail merge.
Could you provide an example?

[calabar]

I found the time to make some test.
I created a support (intermediate) xls file that contains the ready data for the final document (at the moment data are simply written there, while in the future these data will be retrieved from the original data file and modified using the functions integrated in Calc and some MACRO).

At the moment, I've two problems:
- I applied a macro in the "support" xls file that convert numbers to words. Unfortunately it seems that the elaborated data is not imported in the final document, I get a "#VALUE!" text instead than the real value.
- I'm testing with only three iterations, but the print process is very very slow, and Writer hangs. I've tried with a PDF printer and it seems that the generated file is about 4 GigaBytes big!
The whole system is slowed, but it seems related to the use of the disk, because CPU is low and memory use is not high.

Any suggestion?

[RoryOF]

A 4 Gigabyte file looks suspiciously like something serious is wrong. Some Windows file systems do not like files of that size (4 GB system file limit?)

[calabar]

The file was a little smaller than 4 GB, and the file system is NTFS (no 4 GB limit).

Anyway I removed the background from the document and the problems seems to be solved: the the file is printed in very few time.
It seems that Writer has some serious problem to manage page background. With the background the document was very slow and heavy to use (simply adding/removing text, or pan through the document).

Unfortunately it seems that the page is rotated from horizontal to vertical, so the print is not correct, but maybe it is a problem related to the PDF printer (pdfcreator) and not to writer (but I will have to solve it anyway!).
EDIT: I solved this starting the mail marge procedure from standard "print" instead than from the wizard. Maybe a bug of Writer?

Any idea about the other issue?

[RoryOF]

No, I've no thoughts on your problems, which are beyond my knowledge. My previous post was an immediate reaction to the size of the file, nothing more.