How to Import Calc Spreadsheet to Base Database Table

9630 views
8

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.

Sample Calc data to import into Base Database Table
Sample Calc data to import into Base Database Table

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.
Save the file as dbf
Save the file as dbf
  • 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.
Confirm save
Confirm save
  • 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).
dBase file encoding selection
dBase file encoding selection
  • 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.
Select dBase connection
Select dBase connection
  • 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.
Select the folder where you saved the dbf file
Select the folder where you saved the dbf file
  • 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.
Register the database
Register the database
  • 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.
After import from LibreOffice Calc to a Base Table
After import from LibreOffice Calc to a 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.
Base autodetect the datatype and size from dbf file
Base autodetect the datatype and size from dbf file
  • 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.

Join our Telegram channel and stay informed on the move.

Subscribe
Notify of
guest

This site uses Akismet to reduce spam. Learn how your comment data is processed.

8 Comments
Oldest
Newest Most Voted
Inline Feedbacks
View all comments
John_E

Hi,
Easy to follow and clearly documented and running on Mac OS.

However. when I get to “Set up a connection to BASE files” my saved file is on the list but greyed ” out so can not be opened.

Any ideas last to my error?

John

Sigrid E. Mortensen

I was able to get the file by pasting in the pathname to the file I wanted.

Sigrid E. Mortensen

I have the same problem. After clicking “Browse…” my file selection dialog has all the files, including the dbf file I saved from Calc, disabled.

Screenshot 2023-10-09 at 8.52.01 AM.png
Hihi

Hello,
Thanks for the tutorial. But what if I want to import to an existing table?