mail merge

[aupeter]

Hi folks,

I am very new to OO and need help with my mail merge. I've given it a go, but no luck.

I want to 1. copy a table from a web page, 2. insert it into Calc, 3. have a preformatted Writer document update. Steps 1. and 2. are easy enought but number 3 is where I'm having no joy.

example of one record let us call it John's scores as they appear in the Calc table;
ColA ColB
99 great job well done
66 could have done better
54 just plain lazy

I want it to look lexaclty ike this in a document

score=99; comment="great job well done";
score=66 comment="could have done better";
score=54 commnet="just plain lazy";

I am sure this is a simple process for those with expertise. If you could share your expretise I would be grateful.

REgards,

AU

[acknak]

Did you try the merge wizard?

Have you registered your spreadsheet as a data source?

[aupeter]

Yes i have tried your suggestion previously and no luck. I've followed the wizard and Help documents (very confusing) and still find a blank page. I either need someone to do it for me or I need very specific instructions.

If anyone wants to do this for me I am willing to pay for it - hope this dosn't contravene the rules of the forum - my apologies if it does.


REgards,

AU

[acknak]

Ok, give this a try.

I've made a sample set of documents from your example here.
There is:
• A sample merge document
• A sample data file (a spreadsheet)
• A sample database document

1) Download the attached zip archive and unpack the files (you should be able to double-click it, then extract the files).
The three files are:
merge_document.odt
merge_data.ods
ds_sample.odb

2) Open the merge_document file.
3) Open the data explorer panel (Edit > View > Data Sources: F4). It should look like this:

Now, we have the sample database document but it is not yet registered as a data source. We will register it.
4) Right-click in the left hand part of the data explorer panel; choose "Registered databases..."
5) Click "New"; browse to the ds_sample database document, then OK. It should look like this:

the registered data source

Now, we can pull data out of the data source, into the merge document.
6) Use the left hand part of the data explorer panel to select the table "Sheet1". The data from the spreadsheet should appear in the right-hand panel.
7) Select the rows you want to transfer (click upper left for all rows)

Ready to merge

8) Now click the "Data to fields" icon just above the data grid. The data from the selected rows will appear in the document.

Data merged successfully

There's a sample to walk through. Maybe that will give you a feel for the overall steps.

After you get this set up, it goes much easier: the data source is already registered, the document will remember the data source you used before, and so on.

PS: Not really my business, but I cringe seeing that "just plain lazy" comment. I do hope that isn't meant for a student. Anyway, as I said--none of my business.

[aupeter]

thank you so much for the assistance. i will download now and follow your instructions.

With respect to "just plain lazy" this was very much a tongue in cheeck response.

I'll let you know you I go with the instructions.

REgards,

AU

[aupeter]

I followed your instructions to the letter but got this error message

Any suggestions?

Regards,
AU

OOerror.zip

error

(90.9 KiB) Downloaded 289 times

[acknak]

Ok, so the database document is still trying to access the data in the location where it was on my system! I tested that here and it seemed to work. Grr.

In Writer, open the odb file and change it to use your local data:
1) File > Open... Browse to your local copy of ds_sample.odb; OK
2) Edit > Database > Properties > Path to the spreadsheet document
Browse to your local copy of merge_data.ods; OK
(Test connection might be good at this point also.)
3) File > Save
4) File > Close

That should get you back on track. If it doesn't, just ask. I'd like to see if this helps at all. If it doesn't, then I'll know to try a different tack. If you don't have time to fiddle with it, that's fine too.

[aupeter]

thankyou acknac.

your fix worked a treat and I can use the setup.

Could you do one more thing, please. If I want to add more colums (say "effort"), how to do this? If you could walk me through how to add any new fields this would be great.

Thanking you for your generosity,


AU

[aupeter]

Acknak,

one other thing. The example is for one person. How do I add other students so that the one document contains all students and thier results? I would prefer to have the other students data in seperate sheets on the Calc.

Again a walk through would be great.

Thank-you,

AU

PS if you would like a donation to your favorite charity I would be more than happy to do so.

[acknak]

Great! Glad you got it to work. I'll have to look into getting around that problem.

Here's a walk-through; it's really not quite as bad as it looks.

Adding a new data column:

Edit the data source
1) File > Open -- Open the data document: merge_data.ods
2) Add your column, with a header like the others. The formatting doesn't matter, but it's best to keep the label simple: "Effort" (no spaces or punctuation). You can add it as column C, or B, somewhere else.
3) Enter some data in the new column
4) File > Save -- Overwrite the old file; make a backup copy beforehand if you need to. The data source registration refers to the specific file by name, so if you save to a different file, the old data will be found instead of the new.

Add the new field (column) to the merge document
5) File > Open -- Open the merge document: merge_document.odt
6) View > Data Sources (F4) -- The new column should appear in the grid now.
7) Add a field for your new column to the merge document: Click on the "Effort" column header in the data grid, and drag the header into the merge document. Release the mouse button anywhere in the text document: a new field "<Effort>" should appear.
8) Adjust the layout of the document as needed. You can copy and paste the fields just like any other text. Just make sure you have a "Next Record" field for each row of data, and that it comes after any data fields for the row.

Test it out using "Data to fields"

Adding a new student data set:

Surprise: it's the same basic procedure: edit the data document, then edit the merge document.

If you want to make each student on a separate sheet, just clone (copy) the existing sheet, then edit the data. Cloning a Calc sheet is very easy:
Click on the sheet-name tab at the bottom edge of the grid area, press and hold the "Ctrl" key while dragging the sheet tab across the other tabs. You'll see a small indicator to show the new position. When you release the mouse button, a clone copy of the sheet will appear.

i strongly suggest that you rename the sheet to use the student's name--again, without spaces or punctuation: "Doe_Susan", e.g..

In the merge document, with the data source open (F4), you'll see in the left-side panel that each sheet in the data document appears as one table under the data source. Clicking on a different table in the left-hand panel will make it appear in the data grid.

As long as the column names (field names) are the same, you can switch between different data tables. The only catch is that when you do that, you have to use Edit > Exchange Database to tell Writer that it should use a different table as the data source. Just select the new table on the right side of the dialog and click "Define".

Just watch out...
There is a long-standing bug when using a spreadsheet as a data source: sometimes you change the data in the spreadsheet, then switch to the merge document but the changes do not appear in the data source panel. It doesn't happen all the time, but it does happen. If you find that, you can try a disconnect / re-connect to the database, but if that doesn't help, you may have to completely exit OOo and re-start. The bug is being addressed, but is not yet fixed.

[aupeter]

acknak,

you are a marvel (and a gentleman - if my reading of your prosodic style is accurate).

I'll trial you new instructions and let you know.\

Regards,

AU

[aupeter]

Hi acknak,

I was going fine with everything until I tried to do the actual thing I wanted and then I only got the first record in each sectIon.

I wonder if you could have a look to see where I went wrong.

Thanks,

AU

ds_sample.zip

(19.53 KiB) Downloaded 270 times

[acknak]

Hmm, interesting. Ok, I see what the problem is, but I don't entirely understand the details.

Do this:
1) File > Open merge document.odt
2) View > Field Names = YES
Writer will display the names of each field instead of their data value.

Now, look at the "Next Record" field at the end of the third line. Do you see any problem?

Right, it still refers to the old data table: "Next record:ds_sample.Sheet1", so it has no effect on the table you're actually using.

If you double-click on that field, you should see this:

Changing "Next record" table

See table "Sheet1" is selected in the dialog? Just click on the correct table "eurusd" and OK.

You can repeat that for each "Next record" field, or you can copy & paste the first one to replace the others.

After that, it works for me.

But... there is another problem, and not one that I can solve.

If you do the above, you will find that no data is filled in for the last section of your merge document. I think you can see why: the "Next record"s have skipped past the data that's to be filled in there.

To solve that, you will have to re-arrange your data document to move the data you need at that point so that it is available.

Even so, I'm not sure it will quite work out, but that gets off into designing a data base, which is rather beyond the scope of this topic At any rate, you have to understand the purpose of the exercise, which I don't, so I can't really advise you how to structure it.

One general principle: all the data items (i.e. fields, columns) that belong together must all appear in the same row of the data table (you have them spread over several rows, I think). If you have a data "item" that is really a variable-length list, then you either have to make enough columns to deal with the largest number of list items (and deal with some that are empty), or break the list off into another table--and now you're deep into the database jungle.

I would suggest that you play with it a little, and see if you can get it to work the way you need. If not, then perhaps you can ask in the Base forum and they can give you some ideas on how best to structure the data.

[aupeter]

Thanks, Acknak,

I'm not sure I immediately understand what you just said, but I'll have a serious look over the weekend.

It should be identical to the student example as one student with a vaiety of scores can be substituted with one anything with a variety of datapoints.

I'' let you know.

Regards,

AU