How to Import Calc Spreadsheet to Base Database Table

706 views

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
  • 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.

arindam

Creator of libreofficehelp.com. The aim is to help as many people as possible with easy-to-understand tutorials. Connect with me via Telegram, Twitter, LinkedIn, or send us an email.

Subscribe
Notify of
guest

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

0 Comments
Inline Feedbacks
View all comments