Advanced Database – Link external data to a database: spreadsheet
For this tutorial, first we are going to create a LibreOffice Calc spreadsheet. Open a new Calc file.
In sheet 1, enter the data shown to the left. Then right click on the tab for the sheet and select ‘Rename Sheet’
Type Customer Names and Ages, click OK.
Save the sheet as ‘Example 1’
Click the + button to add a new workbook.
Enter data for phone numbers as shown and rename the sheet to Phone Numbers.
Save the spreadsheet, then exit.
Open a new Libreoffice Base file.
Linking data (unlike importing data) allows users to work on the same data in different applications and changes are updated between the two.
In the Base wizard, select ‘Connect to an existing database’ and select ‘Spreadsheet’.
Click Next.
Browse to the Calc spreadsheet we just created and select it to set up a connection.
Click Next.
Select Yes, to register the database.
Check the box for ‘Open the database for editing’.
Click Finish and name the database and save it.
Notice that the database has automatically created two tables based on the two workbooks in the Calc spreadsheet.
Open one of them to see that the data has been automatically transferred.
Save the database, exit Base and re-open the Calc file.
Add a new row to the Customer Names and Ages workbook.
Save the spreadsheet, exit and re-open the Base file we just created.
Open the Customer Name and Age table.
Notice that the data we added in the Calc spreadsheet has been automatically added to the database.
As they are linked any changes made in the Calc file will update in the Base file and vice versa.
Note: you cannot create new tables in the database, but you can create them by adding workbooks in Calc.
Exit Base and open the Calc file again.
Add a workbook, add some data and rename it to Addresses.
Save and exit Calc.
Open the database in Base again.
Notice that the Addresses table has been automatically added to the database.
Also notice that the Create Table options are greyed out, meaning you cannot add tables through the Base application.
