In MS office Access for instance there is always a way to import Excel data just right in Access ribben labeled Import External file and by following the wizard view, you can easily link to Excel data.
But I don't know whether it is possible in Libreoffice Base to import Calc data. Is there anyway to achieve that? At work most of the time my colleagues sent me files in xlsx so when I want to use them in base I don't know what to do.
I like base because it can deal with large quantity of data unlike Calc or excel.
3 Answers
My problem with the answered above method is that it seems creates a read-only database from the spreadsheet file, so you can't actually edit anything. The same problem occurs with data in text files and system address books. This probably makes the imported data less useful than when it was on the spreadsheet, as then primary keys and stuff can't be added, and the tables can't be edited as the options are greyed out due to it being read-only.
I eventually found a method here that works nicely - here's a summary:
- Create a new database:
- Create a table in the database with the same fields/columns/whatever as the spreadsheet being imported
- Select the data in the spreadsheet to be placed in the database - if you include a first line with labels it can be automatically removed - and copy it. The go to the database, right-click on the entry for the table and select 'Paste':
- A options thing will then appear, and you can use this to import the data into the spreadsheet. You can also select which fields will be copied by pressing 'Next':
That should be another way you can import data into a database.
This is possible quite easily. Just set up a database connection to your spreadsheet file:
After clicking Next >>, you will be prompted for the name and path of the spreadsheet file:
There's a WAY easier way. Highlight the fields in calc and select them - do not highlight header fields, just highlight the data. Copy to the clipboard (edit - copy).
Make a backup of your Base file in case something goes wrong. Now open your Base file, but, this is important, do not open the table. Select Tables from the Database area on the left. Now, just click, once, on the table to which you want to import your data, only click one time to select it but don't open it. Right click. Select paste, and a wizard pops up that asks you how to import the data. Follow those instructions and the data will be imported into your table as specified. In the next screen, the wizard will show you the first row of what is being imported and will show you the fields from the table that each imported column will map to, and there you can make any changes so that the columns go into the proper database fields.
So for example, if the database fields are FirstName & Lastname, and the first row of your data is Smith, Tom, the wizard will tell you it's going to map the column containing Smith to FirstName and the column containing Tom to LastName, which is exactly backwards, so swap the field names and press OK. Your data will be imported. Double click on the table and there it will be.
