Please tell me there's a quicker way- Spreadsheet to Shipping labels

[default_abuser]

So my work database spits out excel spreadsheets that have to become shipping labels. Here’s my turn-by-turn workflow.

1. I manipulate them a bit in Calc. Can’t Save As a .odb, so then I have to do step 2.
2. I open the .ods in Base for the only purpose of saving it as a .odb and “registering” as a database.
3. Open Writer, go to File->New->Labels.

Unless I do step 2, the file I want to use won’t be there in the “database” drop down menu in the Labels wizard.

I would really like to eliminate the step using Base entirely.

But I can’t seem to figure out how to Save As .odb from Calc (you can Save As DBASE .dbf but not .odb?). Likewise, I can’t seem to figure out how to use a .ods in a data merge in Writer (been told one can only use “registered” .odb files, but really hoping that’s false). Or something else. Do you have a time-saver, step-skipping suggestion for me?

ALSO, the column headers are ALWAYS the same on all my source files. So, something like “ItemCode” will always be column headed by ItemCode. But when I switch out one source file for another, the data merge seems to require me to completely rebuild my field layout even though the new file has the same column headers as the old file because the Labels layout window still refers to the old source file even though I have chosen a new registered database. What’s up with that?

Thanks.

[Ratslinger]

Hello,

Was once told you can do labels directly from Calc but have never seen any process for that.

You generate labels using data in or through a registered Base file. In your case this can be through by using the Calc file containing the data. Base is connected via Spreadsheet connection. It connects only one name (ods) at a time.

The connection only relies upon the connected file name. So you can replace the Calc file with another of the same name and then open the Base file and the data is present. Also, availability in Writer should not need to be re-established. This is also true of the column headers (as you state they are always the same).

With a current test, Created a Writer document for the labels. Generated labels from spreadsheet A (which runs through a Base file to the Writer doc via registered name). Closed Writer file (Calc & Base were already closed). Replaced Calc file (different file same name). Opened same Writer document for the labels and the was able to generate the labels from spreadsheet B.

Simple process based upon you having consistent spreadsheets - Sheet name and column name the same.

Edit:

Sorry for not giving a better explanation.

Are you saying that a .ods and a .odb
that share the same name are
permanently “linked” and so if I make
changes to the spreadsheet, the
changes are made automatically to the
.odb file as well?

No intention that names are the same for Base and Calc. Understand the .odb is a front end to a database and in the discussion here, Calc contains the data. Base is linked to the Calc file with a connection.

Creating a Base file with a Spreadsheet as source:

The Base file shows this connection:

Here the Calc file was named TestBase.ods. The Base file may be named whatever. In this example SpreadsheetBase.odb was used. Typically the registered name is the same as the Base file name but it can be whatever you want.

So now with this you can create you Writer label file. Here is the dialog:

Select new document and save the resulting document for selecting and printing labels.

So with all this set, to use a different Calc file, just replace the existing one. Replacing means it has the same .ods name, the data is on sheet(s) with the same name, and columns have the same names. Also, the Calc file must be in the same location as where the first connection was made. Once the Calc file is replaced, open your saved Writer label file and you can select and print from the newly connected Calc file - accessed through Base.

Thus the data is located in the Calc file connected to a Base file which, when registered, allows other modules to gain access to the data it is connected to.

Cannot give much reasoning to original design except that Base has been the central point for the mail merge (and label) and data access process back to OpenOffice and I believe even StarOffice. All the data passes through Base.

[default_abuser]

Hold up…the .odb file thing is very confusing to me as a long-time MS Office user (where you just use spreadsheets for data merges)–I don’t understand how it’s making anyone’s life easier or solving any problems, just seems so “extra.” Are you saying that a .ods and a .odb that share the same name are permanently “linked” and so if I make changes to the spreadsheet, the changes are made automatically to the .odb file as well? That seems so pointless. What is the point of this mirror file being visible to me the end user? If/since it is required to do a data merge, it could/should just be hidden as a system-type file, seems to me. What work does the .ods-.odb mirror do for others – or could it do something useful for me too if I just learned? Sometimes if I understand WHY something is, then I can remember the details of What and How. So far, the need for .odb files and “registering” them is very mysterious and frustrating.

[Ratslinger]

@default_abuser,

Please see edited answer.

Once initially set up, it is just a matter of replacing the Calc file and running your labels.

Should also add here, in this configuration Base is helpful in allowing some basic SQL for selection of data. Now this may not be required by you but many others have found this helpful for selection of different types within the data. A good reason not to hide it.

[default_abuser]

Thanks for your help. I understand your concept, though my head has a hard time wrapping itself around this concept of “links” and “registration”. I’ll simply have to take your word for it that this complexity is helpful to someone somewhere because I do not understand the point of it. MS Office and Thunderbird manage data merges with a lot more…grace.

[default_abuser]

I tried this out a little bit and it has locked up LO and maybe corrupted my file, as I tinkered and tinkered trying to get it to work even one time. I believe it does work. I do not believe my head is able to understand precisely what is going on step by step so I think i must be continuously messing up the recipe. This whole process is so complex and idiosyncratic. It would be extremely helpful to me if someone could explain WHY-WHY IS IT DESIGNED THIS WAY? and FOR WHOM, FOR WHOM IS THIS DESIGN A TIME SAVING EASY WAY TO DO IT? Because for me, the designers of LO seem unaware that I will never ever never have use for a data set a second time. If I print off 100 receipts, I will literallly never ever need to print exactly THOSE 100 receipts in a batch, ever again. There is no need to “register” this data set – I would rather it go away quietly and be saved somewhere for record-keeping purposes but that’s it.

[default_abuser]

yep. Now every-time I try to print my receipt file, LO crashes. it corrupted my file and each time I reopen it it is recovered, but then I try to print again and it crashes again. edit: oh well I just had to “exchange database” again and it stopped crashing and has now printed. i never had this much trouble learning to data merge on Thunderbird (which I would say is like best in class) or MS Office (which is fine).

[Ratslinger]

Hello,

Since LO is open source software, anyone in the world can contribute to it - you are welcome to submit any changes. As for who designed any one or more items, most of the design can go back decades. See → LibreOffice Timeline.

It is unfortunate you are having trouble understanding the relation within modules. With Mail Merge (labels, etc) Base is where data passes through. Base data can come from a variety of sources with Calc as one. For other modules in LibreOffice to access this data running through Base, the Base file must be registered. That’s it.

It doesn’t matter who designed it in this manner and exactly the reasoning as to why; this is how it currently works in LibreOffice. It could have been done decades ago. As stated, anyone can submit changes.

[default_abuser]

Thanks. I do understand this is all volunteer creation and that has its limits. I have no coding skills and while I’ve wanted to develop them, also don’t have the mental health/spoons to start learning such a thing while still working fulltime to put a roof over me.

Do you know of a formal or even informal/customary way for a user to setup a bounty for a fix? I would pay pretty good money for someone who would fix an item on my wish list. And it would get some thank-you funds into the hands of volunteer developers.

Also, while this excludes people who can’t afford to put up bounties, and therefore may skew results, I bet despite that the bounties would be a good way to parse priority fixes that are needed. If one bug in the bug bin has $1,000 bounty on it – well, that tells you it’s a bad bug that bugs people real bad. When people are willing to put money where their mouth is, it often is very meaningful, if you know what I mean.

[Ratslinger]

See link in this answer → Is there a platform for crowd-funding bug bounties?

[default_abuser]

oh wow, thanks. I’ll read up on it.