How to Import Calc Spreadsheet to Base Database Table

This tutorial will teach you how to import a Calc spreadsheet (*.ods) file to a LibreOffice Base database table.

In the earlier article, I explained how to export a LibreOffice Base database table to a CSV file, which can easily be imported into the LibreOffice Calc spreadsheet. In this article, you can learn how you can easily import spreadsheet data directly to a base database.

While creating a database or tables, it isn’t easy to enter data manually. The most frequent use case is your data resides inside a spreadsheet, and you want to import them to a table for further storage and processing.

To demonstrate, I have the following data in an ods file. How can I upload it to a table? It’s worth mentioning that a simple copy/paste won’t work from Calc to LibreOffice Base table data entry screen.

Import LibreOffice Calc data to Base

• First, you need to save the spreadsheet to a dBase file having .dbf extension. From the Calc file menu, select File > Save As. In the save dialog, choose the file extension as .dbf and select the option to verify the filters, as shown in the below image.

• macOS users: If you are using these steps in macOS and see the file selection is greyed out, then open the Finder address bar using ALT+COMMAND+P or VIEW > SHOW PATH BAR from the finder menu. And type the entire qualified path to the file, including the file name. Then press OK/SELECT.
• LibreOffice Calc will ask you whether you want to continue to save as .dbf file. Select Use dBase format.

• Since you have selected the filter option, you need to select which encoding you want in your file. Ideally it should be the default Unicode (UTF-8) format. If you want something else, choose from the below list.

• If you are unsure, then choose Unicode (UTF-8).

• After you save the file, close LibreOffice Calc. And open LibreOffice Base.

• To create a database, choose the option Connect to an existing database in the select data wizard.

• From the dropdown, select dBase. Hit Next.

• In the next window, you need to select the folder where you saved the dbf file in the above step. Use the browse button and point to the folder.

• In the final screen, select the option to register the database and open the database for editing. And click Finish. It will prompt you to save the database. Use any name you want and save the database as Base format i.e. odb.

• As soon as you save it, the Base workspace will open. And you should see one table is created with the name of the dbf file name.

• Double-click on the table; you should see all the data from LibreOffice Calc imported to this Base table.

These should be the basic steps to import data from a spreadsheet to LibreOffice Base. You need to repeat the steps if you have multiple files.

Here are some important usage notes.

Usage Notes

• In the above example, you can see several data types in the Calc spreadsheet. Such as dates, numbers and text.
• While converting the dbf file, Base converts date and text data to VARCHAR and all numbers to NUMERIC as per SQL data types. It won’t convert date format from spreadsheet to table column data type.
• The size of the data fields is picked up from the dbf file, which is determined by LibreOffice Calc while saving the file as dbf.

• So, make sure you verify the integrity of the data after importing.
• You can go to the design view and change the column data type if needed.
• If your source Calc workbook has multiple worksheets, you need to repeat it for each worksheet.

Closing Notes

I hope this guide helps you save time and create tables directly from the LibreOffice Calc spreadsheet data. It definitely saves time.

Feel free to drop a comment below if you have any questions.