You are not logged in.
This advice is hard won, and while I am presently the only member likely to need it, there may be others in the membership or readership who would like to have a quick reference.
The setup for this is the need to print business cards of OpenFOAM element data in order to attempt to understand how the data structures are laid out in an OpenFOAM mesh.
LibreOffice is the open source equivalent of Microsoft Word. I am reasonably sure that the procedure will be similar in Microsoft Word.
LibreOffice Mail Merge: How to Print Business Cards Correctly (1 Per Label Slot)
If you're trying to print multiple business cards on a single sheet (like Avery 5871), and LibreOffice is printing one per page instead—this guide will help. The secret is in inserting real mail merge fields and using the Next record feature.
[hr]
✅ Problem:
Dragging fields from the database browser (using the mail merge toolbar or F4) often inserts:
<YourDatabase.YourTable.0.Name>This locks the label to record #0, and disables merge tools like Next record.
[hr]
✅ Correct Method to Insert Merge Fields
Place your cursor in the first label box of the Avery template
Go to:
Insert > Field > More Fields...(or press Ctrl + F2)
In the Database tab:
Category: Mail merge fields
Select your database and table
Insert each of:
<Name> <Title> <Organization>Then place your cursor after the last field and insert:
Category: Database
Field type: Next record
Click Insert
Click Close
[hr]
✅ Synchronize Labels
If you checked “Synchronize contents” when you set up the label page:
Click the Synchronize Labels button (it appears in the toolbar)
All 10 labels will now contain the correct merge field sequence
[hr]
✅ Merge and Print
Go to
File > PrintClick Yes to the "This document contains address database fields..." prompt
Choose All records to fill all business card slots
Output to printer or PDF
[hr]
⚠️ What to Avoid
Do not drag fields from the Mail Merge data browser (via F4 or toolbar).
These often insert a fixed record like:<MyDatabase.Table.0.Name>This disables Next record and causes all labels to repeat the same data
[hr]
? Result
Your merged document should now show 10 unique business cards per page—correctly aligned with your Avery 5871 or other label sheet.
Let me know if you'd like help adjusting margins or saving this as a reusable template!
(th)
Offline
Like button can go here
This post is reserved for an index to posts that may be contributed by NewMars members over time.
A post containing the Microsoft Word version of this procedure would be most welcome!
For anyone interested in attempting this, the label type is Avery 5871 (10 business cards per sheet)
Index:
(th)
Offline
Like button can go here
In my earlier post I was using a simple text file with only alphanumeric characters, so the default connect import worked. I have ** just ** learned that the "connect" option means a link to a file, and NOT import of the data directly. The consequence was that when I tried to import a table with numbers in some columns, LibreOffice Base stubbornly insisted that one of the columns was a set of numbers, and nothing I could do could convince it otherwise. The solution turned out to be to import the data into the spreadsheet program "Calc" and then to copy-paste the data into the database Table import window. That solution worked perfectly. Here is the transcript with Gemini:
Conversation with Gemini
Hi Gemini! Thanks for your help with many previous questions. I am working with LibreOffice, and trying to set up a simple database for a simple mail merge. The input file is text, with five columns, three of which are numbers. The import procedure is stubbornly creating a format for the first numeric column of number, while all the other folders are formatted correctly as text. I tried importing as text, but that didn't make a difference. Can you think of anything that might be causing LibreOffice base to refuse to change the formatting to text? I can reset the format to text in the opened table, but the change does not take effect.
That's a frustrating issue! When LibreOffice Base stubbornly holds onto a data type, especially during text import, there are a few usual suspects and methods to try. Here's a breakdown of what might be happening and how to address it:
Common Causes and Solutions:
The Import Wizard's Column Type Detection:
During Import: This is the most crucial step. When you import the text file (File > New > Database > Connect to an existing database > Text or by creating a new table in an existing Base file and choosing to import data), you should encounter a Text Import wizard.
Crucial Step: In this wizard, there's usually a preview of your data with columns. You MUST explicitly click on the header of the problematic column in the preview and then set its type to "Text" from the dropdown menu provided for column types. Don't just rely on a general "import all as text" if that option exists and isn't working. Do it column by column if necessary.
"Detect Special Numbers": Look for an option in the import wizard like "Detect special numbers" or "Quoted data." If "Detect special numbers" is checked, try unchecking it. This can sometimes override your explicit text setting if it thinks it sees a number.Modifying Table Design After Import (The "Doesn't Take Effect" Problem):
Open in Design View: If you've already imported the data and are trying to change the column type, make sure you are doing this in the table's "Design View" (right-click the table name and select "Edit" or "Design View").
Save the Changes: After changing the "Field Type" to Text [VARCHAR] (or Text [LONGVARCHAR] if needed), you must save the table design.
Data Truncation/Conversion Warning: Sometimes, if Base thinks converting to text might cause issues with existing "numeric" data (even though you want it as text), it might resist or warn you. However, number-to-text is usually a safe conversion.
Primary Keys/Indexes: If that column is inadvertently part of a primary key or an index that was created assuming a numeric type, Base might prevent the change. Ensure it's not set as a primary key if it shouldn't be, or that any associated indexes are also modified or dropped and recreated.Hidden Characters or Inconsistent Data in the Source File:
Leading/Trailing Spaces: Sometimes, numbers mixed with spaces can confuse importers. Ensure your numeric strings are clean.
Inconsistent Quotes: If your text file is a CSV, ensure that text fields, especially those you want to be treated as text even if they look like numbers, are consistently enclosed in quotes (e.g., "12345", "Main Street", "007"). If the problematic column sometimes has quotes and sometimes doesn't, the importer might get confused.
Header Row: Check the header name for that column. Is it something generic like "ID" or "Number" that might predispose Base to think it's numeric? Try making it very explicitly text-like, e.g., "ItemID_Text".The "Phantom Format" in Calc as an Intermediary:
If you're opening the text file in LibreOffice Calc first and then copying/pasting or linking to Base, Calc's own auto-formatting can be an issue.
In Calc: When opening the text file in Calc, the same import wizard appears. Again, be meticulous: select the column(s) and explicitly choose "Text" as the column type before clicking "OK."
Format Cells: Once in Calc, select the entire column, right-click, choose "Format Cells," and ensure the category is "Text."LibreOffice User Profile Corruption:
This is a less common but possible cause for various strange behaviors. You can try resetting your LibreOffice user profile. Instructions are available on the LibreOffice support website (it usually involves renaming a folder). Be aware this resets all your LibreOffice customizations.Bugs in Specific LibreOffice Versions:
Ensure your LibreOffice is updated to the latest stable version. There might have been a bug in an older version related to text import.Troubleshooting Steps to Pinpoint the Issue:
Simplify the Input: Create a tiny test text file with just that problematic column and one other correctly behaving column. Does the issue persist? This helps rule out interactions with other data.
New Database: Try importing the problematic text file (or the simplified version) into a brand new, empty LibreOffice Base file. Does the problem still occur? This can indicate if the issue is with your existing Base file's settings or a more general LibreOffice problem.
Examine the .odb File Internals (Advanced): A LibreOffice Base file (.odb) that uses the default HSQLDB engine is essentially a ZIP archive. You can rename it to .zip and extract its contents. Look for a database folder, and within that, a script file. This file contains the SQL CREATE TABLE statements. You can inspect it to see what data type Base actually recorded for your column. This can tell you if the GUI is misleading you or if the change truly isn't being saved. (Make a backup before trying this!).If you've tried setting the column type to "Text" in the import wizard meticulously and it still defaults to "Number" for that one column, here are the key things I'd double-check:
The "Detect special numbers" option in the import wizard. Uncheck this.
Try manually quoting all entries in that problematic column in your source text file. For example, change 12345 to "12345". This often strongly signals to the importer that it's text.Could you describe the exact steps you take during the import process, especially the options you see and select in the import wizard? Knowing the type of text file (CSV, TSV, fixed-width) might also be helpful.
I think we must be close to a solution! I have the LibreOffice base page in front of me. It has two sections on the window (under the icons) The top section says Tasks on left and Description on right. The bottom half of the display has the title "Tables" and under that is the name of the table I imported using the text import. I was asked no questions during the import. Now, when I click on the table, I see the data but the X column is formatted as a number and I canm'
It sounds like you're viewing the table's data, not its design or structure. You can't change column formats (like number to text) from the data view mode directly in a way that permanently alters the table's underlying structure for that field.
Here's what you need to do to edit the table structure:
Close the Data View: If you have the table open showing its rows and columns of data, close that window. You should be back to the main LibreOffice Base window you described (with "Tasks" and "Tables" sections).
Edit the Table Structure:
In the "Tables" section of the main Base window, find the name of your imported table.
Right-click on the table name.
From the context menu that appears, select "Edit" or "Design View" (the exact wording might vary slightly depending on your LibreOffice version).This will open the table in a different view where you see:
Field Name
Field Type
Description
And further down, "Field Properties" for the selected field.Change the Field Type:
In this Design View, find the row corresponding to your problematic "X column".
Click on the current Field Type for that column (which is likely showing something like Number [NUMERIC], Decimal [DECIMAL], or Integer [INTEGER]).
A dropdown arrow should appear. Click it and select a Text type. The most common and versatile text type is Text [VARCHAR]. If you have very long text strings, you might consider Memo [LONGVARCHAR], but for most mail merge data, Text [VARCHAR] is fine.Save the Changes:
After changing the field type, you must save the table design. Click the Save icon (usually a floppy disk icon) on the toolbar, or go to File > Save.
Close the Design View window.Check the Data:
Now, try opening your table again by double-clicking it in the "Tables" section.
The "X column" should now be treated as text. Existing numbers might still look like numbers, but their underlying data type will be text. New data entered will definitely be treated as text.Why No Questions During Import?
You mentioned "I was asked no questions during the import." This is unusual if you used the standard method for importing external text data into a new native Base table. This makes me suspect one of these might have happened:
Pasting Data: Did you perhaps copy the data from a spreadsheet (like LibreOffice Calc) or a text editor and paste it directly into the "Tables" area or into an already open (but empty) table? If so, Base might try to auto-detect types without the full wizard.
Connecting to a Text File as a Database (Less Likely if you see it under "Tables" as a regular table): If you used File > New > Database > Connect to an existing database > Text, the wizard for defining column types appears when you set up that connection, not each time you "open" what looks like a table from that connection.
Drag and Drop: In some cases, dragging a CSV file onto the Base window might trigger an import with default settings, potentially skipping the wizard.For Future Imports (The Recommended Way to Get the Wizard):
If modifying the existing table doesn't work as expected, or for next time, here's a more robust way to import text that should give you the wizard:
In your LibreOffice Base file, go to the "Tables" section.
Look for an option like "Create Table in Design View..." or "Use Wizard to Create Table...". However, for importing text, it's often easier to directly use the text import functionality if you're creating the table from the text file.
A more direct way to import text files and get the import wizard:
Ensure your text file (e.g., .csv, .txt) is saved and closed.
In Base, you might not have a direct "Import Text File" button readily visible. The process is often tied to connecting to it or copying data.
The Calc Intermediary Method (often most reliable for the wizard):
Open your text file with LibreOffice Calc first.
Calc will definitely show you a "Text Import" wizard.
In this wizard, very carefully select each column, especially your problematic numeric column, and change its "Column type" to "Text" from the dropdown.
Click "OK". The data opens in Calc, correctly formatted as text.
Select all the data in Calc (Ctrl+A).
Copy it (Ctrl+C).
Go back to your LibreOffice Base window, in the "Tables" section.
Right-click in the white space where table names appear and choose "Paste".
A "Copy table" or "Paste Table" wizard will appear.
Give your new table a name.
Ensure "Definition and data" or "Append data" (if pasting into an existing, correctly structured table) is selected.
Make sure "Use first line as column names" is checked if your text file has headers.
Click "Next" or "Create."
This wizard might also offer a chance to review data types. Double-check them here again if presented.By editing the table in Design View, you should be able to correct the format of your existing table. If not, using Calc as an intermediary for the import is a very solid way to ensure column types are set correctly before the data even touches Base's native table structures.
Thank you for staying with me! I am in the design page, and I've been here before (I now realize) I am unable to change anything. I'll paste what I have in front of me. I am unable to change anything in any of the fields. I am also unable to copy anything from the display. I'll type it:
Field Name | Field Type
Row | Text [VARCHAR]
Okay, this is a very important clue! If you are in the Design View (where you see "Field Name" and "Field Type" columns) and you genuinely cannot click into any cell in the "Field Type" column and change its value (e.g., click on Text [VARCHAR] and see a dropdown arrow or be able to type), then there are a few strong possibilities:
It's Not a Native Base Table (Most Likely for Text Imports with No Questions):
You mentioned you weren't asked any questions during the import. This often happens if, instead of importing the data into a new, native Base table, you've created a link to the external text file.
When Base links to a text file (often done via File > New > Database > Connect to an existing database > Text), the "table" you see in Base is more like a window to that external file. The structure (including column types) is defined when you set up that initial connection.
The Design View for such linked tables is often read-only because the schema is controlled by the connection settings and the external file itself. You can't change the structure of the "text file's columns" from within Base's table design view in the same way you can for a native table.The Database File (.odb) is Read-Only:
Navigate to where your .odb (LibreOffice Base database) file is saved on your computer using your file explorer.
Right-click on the .odb file and choose "Properties."
Look for a "Read-only" checkbox (on Windows, this is under the "General" tab; similar options exist on macOS and Linux). If it's checked, uncheck it, click "Apply," then "OK." Then, try editing the table design in Base again.LibreOffice is in a Strange State / Lock:
Close LibreOffice Base completely (and all other LibreOffice applications like Writer or Calc).
Re-open your Base file and try editing the table design again.Given your description, the most probable scenario is #1: you are working with a linked text table, not a native Base table.
What To Do If It's a Linked Text Table (and Design View is Read-Only):
You cannot directly change the column types in the read-only Design View. You have two main options:
Option A: Re-create the Link/Connection Correctly (If you want to keep it as a linked text file):
If this table is indeed a link to your text file, you would need to delete this current "table" entry from your Base file. (Right-click on its name in the "Tables" list and choose "Delete").
Then, you'd re-establish the connection:
Go to File > New > Database.
Select "Connect to an existing database". From the dropdown, choose "Text". Click Next.
You'll be prompted for the path to your text file.
Crucially, the next steps in that wizard are where you define the separator (comma, tab, etc.) AND THE COLUMN TYPES. You must carefully select your problematic column in the preview and set its type to "Text" during this setup wizard.
Finish the wizard to register the text database. The "table" that appears will then use the types you defined.Option B: Create a New, Native Base Table with the Correct Types (Often More Robust and Recommended for Mail Merge):
This is the method I emphasized previously and is generally more reliable for ensuring data types are exactly what you want within Base itself.
Open Your Text File in LibreOffice Calc:
Start LibreOffice Calc.
Go to File > Open and select your text file (e.g., yourfile.csv or yourfile.txt).
The "Text Import" wizard will appear in Calc.
Carefully review the separator options (comma, tab, etc.).
In the preview area, click on the header of the column that is giving you trouble.
In the "Column type" dropdown above the preview for that selected column, choose "Text". Do this for any column you want to ensure is treated as text.
Click "OK". Your data will open in the Calc spreadsheet, with the specified columns formatted as text.Copy the Data from Calc:
In Calc, select all the cells that contain your data (including the header row). An easy way is to click the empty square above row 1 and to the left of column A, or press Ctrl+A if the sheet only contains this data.
Copy the selected data (Edit > Copy or Ctrl+C).Paste into LibreOffice Base to Create a New Table:
Go back to your main LibreOffice Base window (where you see the "Tables" list).
In the "Tables" section, right-click in the empty area below any existing table names.
Select "Paste".
The "Copy Table" wizard will appear.
Table name: Give your new table a distinct name (e.g., "MailMergeData_Native").
Make sure the option "Definition and data" is selected. This tells Base to create a new table structure based on the data you're pasting AND to copy the data itself.
Check the box "Create primary key" if you want one (Base can add an ID column), or uncheck it if your data doesn't need/have one or you'll define it later. For a simple mail merge, you might not strictly need it initially.
Click "Next >".
You'll now see a list of the columns being copied. This is a critical step:
For each column, you can verify and change the "Field Type". Find your problematic column, and ensure its Field Type is set to Text [VARCHAR]. Also, adjust types for any other columns as needed.
You can also change Field Names here if desired.
Click "Create".Now, you will have a new native Base table. If you right-click on this new table and choose "Edit" or "Design View," you should be able to modify its field types because Base owns and controls its structure.
Try Option B (the Calc intermediary method) first, as it gives you the most control and results in a robust native table which is generally better for mail merge purposes within LibreOffice.
***
Thank you! I am indeed using the connect option, because that was what I was taught to do for a simple text file for mail merge. This is the first time I've tried an input file with numbers. I deleted the failing database. I went to Options Database and confirmed the failed database is not registered. I changed the input file back to .csv. Then I re-opened new >> database and chose to create a new database and I gave it a name NewDatabase. I am now looking at the Tasks/Descriptions page, and there is nothing in Tables. Can I try to import the data now?Yes, absolutely! You are in the perfect position to import your data now into this new, empty database.
Since you encountered issues with the "Connect" method not allowing type changes easily, let's use the method that gives you the most control over how your columns are interpreted, especially for those tricky numeric ones you want as text. This involves using LibreOffice Calc as an intermediary.
Here's how to proceed:
Minimize or Set Aside Base for a Moment: Keep your "NewDatabase" LibreOffice Base window open, but switch to LibreOffice Calc.
Open Your .csv File with LibreOffice Calc:
Start LibreOffice Calc (the spreadsheet program).
Go to File > Open....
Navigate to and select your .csv file.
The "Text Import" wizard will appear. This is a crucial step.In the "Text Import" Wizard (in Calc):
Separator Options: Ensure the correct separator is chosen (likely "Comma" for a .csv file). You should see your data arrange into columns correctly in the preview pane at the bottom.
Other Options: Settings like "Quoted field as text" are usually good to keep checked.
Define Column Types (Most Important Step):
In the preview area at the bottom of the wizard, you'll see your columns.
Click on the header of the first column that contains numbers you want to be treated as text. It will become highlighted.
Just above this preview, there's a "Column type" dropdown menu. It probably says "Standard" or might have guessed "Numeric." Click on this dropdown and explicitly select "Text".
Repeat this for any other column that contains numbers (or any data) that you want to be definitively treated as text in your database.
For columns you genuinely want as numbers (if any, though you said all three numeric columns were problematic initially, now focusing on the first), you could select an appropriate numeric type, but for your stated goal of making them text, choose "Text."
Click "OK". Your data will now open in the Calc spreadsheet, and the columns you designated as "Text" will be formatted that way.Copy the Data from Calc:
In the Calc spreadsheet, select all the data that you want to import, including your header row. (Click and drag, or click the first cell, scroll to the last cell, hold Shift, and click the last cell. Or, if it's the only data on the sheet, you can click the empty square above row 1 and to the left of column A to select all cells).
Copy the selected data: Edit > Copy (or Ctrl+C).Paste the Data into Your LibreOffice Base "NewDatabase":
Go back to your "NewDatabase" LibreOffice Base window. You should still be looking at the main screen with "Tasks," "Description," and the empty "Tables" area.
In the "Tables" area (the bottom white section), right-click.
From the context menu that appears, select "Paste".The "Copy Table" Wizard (in Base):
A wizard will pop up.
Step 1: Define table name and options:
Table name: Give your table a name (e.g., "MailMergeSource" or something descriptive).
Make sure "Definition and data" is selected.
If your first row of copied data contains your column headers (which it should if you copied them from Calc), make sure "Use first line as column names" is checked.
Click "Next >>".
Step 2: Assign types (Column types):
This screen is your second chance to ensure the column types are correct. Base will make guesses based on the data it sees.
Go through each Column name. For each one, check the Field type.
For your problematic column(s) that you want as text, ensure the Field type is set to Text [VARCHAR]. If Base has guessed something else (like Number [NUMERIC]), click on it and change it from the dropdown.
You can also adjust the Length for Text [VARCHAR] fields if necessary, but the default is usually fine.
Click "Create".Your table should now be created in the "Tables" section of your "NewDatabase." If you right-click on this new table and choose "Edit" or "Design View," you should find that:
You can now edit the field types if you ever needed to (because it's a native Base table).
The column you wanted as text should already be set to Text [VARCHAR] because you defined it during the Calc import and confirmed it during the Base paste wizard.This method is generally the most robust for getting data from text files into Base with precise control over data types. Let me know how it goes!
***
Thanks ** very ** much for teaching me this workaround for the failed import. The Calc program asked me to confirm it's choices, but it's choices were correct. I performed the right click in the Tables space and the table was created. When I opened the table all the data was there, and it was correctly formatted. I doubt you have a score board for your assistance to customers, but if you do you can chalk up another "win" !!!!
***
That is fantastic news! I'm so glad to hear that the Calc intermediary method worked perfectly for you and that your data is now in Base, correctly formatted.You're absolutely right – using Calc in between gives you that crucial level of control during the import process, especially for ensuring column types are exactly what you need them to be before Base even tries to guess. It's a very reliable workaround when direct imports or connections don't behave as expected.
And while I don't have an official scoreboard, helping you get this sorted out definitely feels like a "win"! Thank you for your patience and for working through the steps.
If you have any more questions as you continue working with LibreOffice Base or your mail merge, please don't hesitate to ask. Happy databasing!
(th)
Offline
Like button can go here
And here is ChatGPT4o's version of the same advice:
How to Import CSV Data into LibreOffice Base with Precise Column Formatting
If you've encountered issues with LibreOffice Base misinterpreting column data types—especially when using the "Connect to Text" method—here's a reliable workaround using LibreOffice Calc to ensure your data is imported correctly.
Step-by-Step Guide:
Open Your CSV File in LibreOffice Calc
Launch LibreOffice Calc.
Go to File > Open and select your CSV file.
The Text Import dialog will appear.
Configure Import Settings
In the Separator Options, ensure the correct delimiter (e.g., Comma) is selected.
In the preview pane, click on each column header that you want to import as text.
For each selected column, set the Column type to Text from the dropdown menu.
Click OK to open the file with the specified settings.
Copy Data from Calc
Select all the data you want to import, including headers.
Copy the selection (Edit > Copy or Ctrl+C).
Paste Data into LibreOffice Base
Open your LibreOffice Base database.
In the Tables section, right-click and choose Paste.
The Copy Table wizard will appear.
Configure Table Settings in Base
Table Name: Enter a name for your new table.
Definition and Data: Ensure this option is selected.
Use first line as column names: Check this if your data includes headers.
Click Next.
Set Field Types
For each column, verify that the Field type matches your intended data type.
For columns you want as text, ensure the type is set to Text [VARCHAR].
Adjust field lengths if necessary.
Click Create to finish the import.
Benefits of This Method:
Full control over data types during import.
Prevents issues with misinterpreted data formats.
Ensures data integrity for complex datasets.
By using LibreOffice Calc as an intermediary, you gain precise control over how your data is interpreted and stored in LibreOffice Base, avoiding common pitfalls associated with direct CSV imports.
Feel free to share this guide with your community. If you need further assistance or have additional questions, don't hesitate to ask!
As a follow up ... ChatGPT4o listed some activities that might occur, but in my case did not. When I pasted the Calc data into the table import window, Base accepted the gift without complaint and the data was correct.
(th)
Offline
Like button can go here