[Solved] Labels from an existing calc spread sheet

Writing a book, Automating Document Production - Discuss your special needs here

[Solved] Labels from an existing calc spread sheet

Postby ptownpapa » Mon Oct 09, 2017 3:13 am

In Excel I could create a spreadsheet and use Word to extract the info and print it as labels...
Open Office seems to demand a Data base??
Please advise..
Last edited by RoryOF on Thu Dec 07, 2017 11:24 pm, edited 2 times in total.
Reason: Added green tick [RoryOF, Moderator]
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 7
Joined: Mon Oct 09, 2017 2:58 am

Re: Labels from an existing calc spread sheet

Postby RusselB » Mon Oct 09, 2017 6:30 am

Technically this is a database process, and even in Excel/Word the functionality is based on how a database works.
You can import a spreadsheet into Writer and then use the Mail Merge options (which I'm guessing is what you are actually after).
If my guess is wrong, please advise us as to what you are trying to do.
OpenOffice 4.1.4 and LibreOffice 5.2.7.2 on Windows 7 Pro & Ultimate
If you believe your problem has been resolved, please go to your first post in this topic, click the Edit button and add [Solved] to the beginning of the Subject line.
RusselB
Volunteer
 
Posts: 4160
Joined: Fri Jan 03, 2014 7:31 am
Location: Sarnia, ON

Re: Labels from an existing calc spread sheet

Postby Villeroy » Mon Oct 09, 2017 11:30 am

menu:File>New>Labels...
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Labels from an existing calc spread sheet

Postby Villeroy » Mon Oct 09, 2017 1:30 pm

If the label wizard does not work for you or if you want to use a template downloaded from the label vendor, then it is easy enough to do it manually.
It involves 3 files:
1. a tabular source which is a spreadsheet in many cases but could be a lot more than that.
2. a Base document connected to the source
3. a label document (Writer) with the right layout. The wizard produces tiles of frames. Downloaded templates consist of one table with the right spacing.

Having these files, you can design one label with text, pictures and place holders, append a [Next Record] field and copy this label across the other labels. Save the text document with place holders as a template so you don't have to do anything when you need this layout again.

Here is a description, discussion and an example based on a data source and Base document we all have on our systems since it is installed with the office suite: https://forum.openoffice.org/en/forum/v ... 45#p423345
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Labels from an existing calc spread sheet

Postby ptownpapa » Thu Dec 07, 2017 10:07 pm

Thank you to all those who tried to help..... I read the manual (several times - Reflect and interpret) and found I had to register a Database, create a Table, Copy the existing spreadsheet into the Table, and then interpret the printing instructions. At the end I could do what took 10 minutes in MS Word with no manual. I still like Open Office - no MS BS and there is actually a helpful forum for the unwashed. Again - Thank you all
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 7
Joined: Mon Oct 09, 2017 2:58 am

Re: Labels from an existing calc spread sheet

Postby Villeroy » Thu Dec 07, 2017 10:36 pm

No, you don't have to create a new database and copy data to it.
You can connect a spreadsheet to a database document.

File>New>Database...
[X] Connect to existing database
Type: "Spreadsheet"
[X] Register the database
Save the database.

Now you have the used areas of all sheets represented as tables of a pseudo-database. Nothing has been copied nor imported nor converted. Your data are still in the spreadsheet and Base represents those data as if your spreadsheet were a database. This pseudo-database is read-only and any changes to the spreadsheet require a restart of the office suite before the database connection reflects the changes.
Since spreadsheets are free hand tools, not even close to databases, you may want to define cell ranges as database ranges in Calc via menu:Data>Define... These lists appear as separate database tables when the sheet contains more than just the list data. Then you can hide away all unwanted pseudo-tables in Base via menu:Tools>Table Filter... Unelected tables will be hidden.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Labels from an existing calc spread sheet

Postby ptownpapa » Thu Dec 07, 2017 11:02 pm

I missed that in the manual.... Thank you.
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 7
Joined: Mon Oct 09, 2017 2:58 am

Re: Labels from an existing calc spread sheet

Postby ptownpapa » Thu Dec 07, 2017 11:07 pm

I have reread all of the answers... none offered the quick fix you just handed me - now I know a bit more then before.
John
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 7
Joined: Mon Oct 09, 2017 2:58 am

Re: [Solved] Labels from an existing calc spread sheet

Postby John_Ha » Fri Dec 08, 2017 11:41 am

You need three files for labels:

1 A spreadsheet .ods file with the data
2 A database .odb file which has nothing in it but which is registered and can be considered to be the "interface between the document and the spreadsheet"
3 A document .odt file for the labels.

If you do not have the .odb file in Step 2, or if you have not registered it, it will not work.

I wrote this aide-memoire as a post on another forum many, many years ago. It was based on OOo v2, used a .xls file, is out of date and it isn't fully correct ... but it serves me well each Christmas.
Attachments
aide memoire for labels.pdf
(64.34 KiB) Downloaded 6 times
AOO 4.1.4, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the up to date Writer guide for information. Click the Help button on a pop-up window for extensive help on that function.
John_Ha
Volunteer
 
Posts: 4644
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Labels from an existing calc spread sheet

Postby Villeroy » Fri Dec 08, 2017 2:41 pm

John_Ha wrote:I wrote this aide-memoire as a post on another forum many, many years ago. It was based on OOo v2, used a .xls file, is out of date and it isn't fully correct ... but it serves me well each Christmas.

In other words: You need one text document for every layout of labels (3x7, 2x8, different sizes etc) and the source sheets need to be reloaded after editing. I use to recommend reloading the whole office suite just to be sure.
BUT you only need one registered data source (*.odb) and one spreadsheet document keeping the list. Every placeholder in your text documents refers to DatabaseName.TableName.ColumnName. When any of the 3 names changes in the data source, some placeholder field can not be filled with data anymore.

When you want to exchange the whole list, say from Xmas cards to swinger club invitations, then you have a lot of flexible options for all types of databases, however when using a spreadsheet it is very easy to rename the old TableName and replace it with another sheet renamed to the same TableName having new data under the same ColumnNames.
Alternatively you may copy the Xmas list to any other sheet and paste the new list into the existing TableName sheet. In any case you have to watch out that the ColumnNames match with the new data so you never need to fiddle with the placeholder fields in your text documents. This is easy to accomplish by means of copy/paste or drag&drop.

------------------------------------------------------------------------

More theoretical options to exchange the source lists:
Of course you may also rename the whole spreadsheet document (*.ods) with an equally named spreadsheet document having the new data under the same ColumnNames on the same TableName.
And you can open the Base document and connect it to another spreadsheet document with the same ColumnNames on the same TableName (via menu:Edit>Database>Connection...)
And finally you may change the database registration, so the same DatabaseName refers to some other database of any type with the same ColumnNames on the same TableName.

------------------------------------------------------------------------

Working with real databases (not connected to spreadsheets or text files) it is difficult to manipulate ColumnNames and TableNames, however it is very easy to add a query to the database document having the right query name and column names so you never need to replace any of the place holders in your various label documents.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: [Solved] Labels from an existing calc spread sheet

Postby ptownpapa » Fri Dec 08, 2017 3:35 pm

Thank you again Elf Villeroy.... yes, at the holiday season labels are very important.
I will read the whole thing and try to copy it for reference.

Wishing you a happy Christmas,
Hanukkah,
Kwanzaa,
Dawali and New Year
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 7
Joined: Mon Oct 09, 2017 2:58 am

Re: [Solved] Labels from an existing calc spread sheet

Postby John_Ha » Fri Dec 08, 2017 3:38 pm

Thanks - that clarifies it. I am, to quote Pooh?, "a bear of little brain".

Villeroy wrote:When you want to exchange the whole list, say from Xmas cards to swinger club invitations

I don't have a list of names for the swinger club invitations - do you have one I could use :D :D :D
AOO 4.1.4, Windows 7 Home 64 bit

See the Writer Manual, the Writer FAQ, the Writer Tutorials and the up to date Writer guide for information. Click the Help button on a pop-up window for extensive help on that function.
John_Ha
Volunteer
 
Posts: 4644
Joined: Fri Sep 18, 2009 5:51 pm
Location: UK

Re: [Solved] Labels from an existing calc spread sheet

Postby ptownpapa » Fri Dec 08, 2017 3:42 pm

HA! - just saw the PDF "Aide Memoire" you elves think of everything.
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 7
Joined: Mon Oct 09, 2017 2:58 am

Re: [Solved] Labels from an existing calc spread sheet

Postby ptownpapa » Fri Dec 08, 2017 3:49 pm

No, my wife says I do that too - a lesson in genetics answering the question " what kind of bird is that"?
I do appreciate having my vision stretched - you discover all kinds of uses for for all kinds of things.

Thanks again....
openoffice 4.1.3 on Wndows 10
ptownpapa
 
Posts: 7
Joined: Mon Oct 09, 2017 2:58 am

Re: [Solved] Labels from an existing calc spread sheet

Postby Villeroy » Fri Dec 08, 2017 5:15 pm

John_Ha wrote:
Villeroy wrote:When you want to exchange the whole list, say from Xmas cards to swinger club invitations

I don't have a list of names for the swinger club invitations - do you have one I could use :D :D :D

No. My lists are the same for both purposes. Incest is a game the whole familiy can play.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24656
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Advanced Uses

Who is online

Users browsing this forum: No registered users and 4 guests